SQL Server 2012 :: Combine Functionality Of IN And LIKE In WHERE Clause

May 8, 2015

I would like to be able to combine the functionality of IN and LIKE in a WHERE clause. Although the simple AdventureWorks2012 example below illustrates the concept with 3 search criteria, the real-world example I need to apply the concept to has a couple dozen. This returns 50 rows, but requires multiple OR ... LIKE functions:

SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name LIKE '% sports %'
OR c.Name LIKE '% exercise %'
OR c.Name LIKE '%toy%'

What I would like to do is something like this, which doesn't work:

SELECT DISTINCT c.Name
FROM Sales.Store c
WHERE c.Name IN(LIKE '% sports %', LIKE '% exercise %', LIKE '%toy%')

I could load up a cursor and loop through it, but the syntax is more cumbersome than the multiple LIKE statements, not to mention most SQL programmers are horrified at the mention of the abominable word 'cursor' for performance reasons.

View 7 Replies


ADVERTISEMENT

SQL Server 2012 :: Combine One Value To Equal Two Values

Feb 13, 2015

I have a report that I am running in visual studio 2010, that gets its data from a few different stored procedures in a SQL 2012 Database. The variables are Date and Office Code. We currently have 6 different office codes. One of the Stored Procedures gets call information for each office. With the report we can select any single office or any combination of offices to compile data.

The problem I have is two of these share phone information, so when you select either one or both of them the same data gets returned. So for example we have office codes of AAAAA, BBBBB, CCCCC, DDDDD, EEEEE, and FFFFF. Now AAAAA, and BBBBB share phone information so if you select office code AAAAA, the phone info that is returned is for AAAAA, and BBBBB, and visa versa. So I am not sure how to accomplish that either in the report or in the stored procedure.

View 1 Replies View Related

SQL Server 2012 :: How To Combine Data From Different Tables Using A Query

Dec 17, 2013

I have a set of tables that look like what I have shown below. How I can achieve the desired output ?

CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);

INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);

[Code] ....

I have a query currently that looks like this . @Month and @Year are supplied as parameters

SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]

[Code] ...

Right now I see an output like this : for a particular value of @Month and @Year

SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :

[Customer],SalesofProductA, SalesofProductB, SalesofProductC

How it can be done ?

View 2 Replies View Related

Transact SQL :: Finding Syntax To Combine IN And CASE In A WHERE Clause?

Oct 29, 2015

I cannot seem to find the syntax to combine IN + CASE in a WHERE clause

WHERE
ses.BK_MS_SESSION <= '2015-03'
AND vis.CAT_DRAW_STATUS =
(CASE ses.BK_MS_SESSION
WHEN '2015-03' THEN vis.CAT_DRAW_STATUS
ELSE
CASE stat.BK_MS_VISIT_STATUS
WHEN 'T' THEN 'X'
ELSE vis.CAT_DRAW_STATUS
END
END
) IN ('D','R')

View 7 Replies View Related

SQL Server 2012 :: Using Parameterized Query With Like In Where Clause

Feb 4, 2014

From MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this:

where [Field1] like @p1 and [Field1] < @p2.
Field1 is the only primary key field and clustered index. The query also has a TOP 50 clause.
@p1 is always a "Starts-With"-value (something like N'abc%').

The query plan uses a clustered index seek but the number of reads look more like a clustered index scan.

Depending on the table size I see 1M or more reads for these querries.

If I rebuild the query in SSMS, but replace the paramerters with actual values I only see a few reads.

I was able to reproduce the issue with a temp table. See code below.

Is there a way to make SQL Server use another strategy when using the parameterized query?

SQL Server Version is 11.0.3401.
if object_id('tempdb..#tbl') is not null
drop table #tbl;
create table #tbl
(
[No] nvarchar(20)
,[Description1] nvarchar(250)

[Code] ....

View 9 Replies View Related

SQL Server 2012 :: Where Clause On Multiple Columns?

May 16, 2014

Right now I have to do something like this and it is time consuming every time I have to query a specific table...

SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here

Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).

View 4 Replies View Related

SQL Server 2012 :: Curious Use Of Percentage On Where Clause

May 5, 2015

I found a code snippet that use the curious following sintax on the creation of View:

CREATE VIEW [dbo].[vw_EvenValues]
AS
SELECT [TestColumn]
FROM [dbo].[TestTable]
WHERE [TestColumn] % 2 = 1

[code]....

IF [TestColumn] on the Select is varchar, then error occurs and say:"Conversion failed when converting for the varchar value 'A001' to data type int"

View 7 Replies View Related

SQL Server 2012 :: Running Totals With OVER Clause?

May 28, 2015

A while back, a "quirky update" method was proposed for lightning fast running totals based on the three-part MSSQL UPDATE's SET statement and tally tables. However, some claimed this was not 100% absolutely guaranteed behavior.

How does the new OVER clause compare in terms of performance ?

DECLARE @Tbl TABLE
(
pk int not null primary key identity,
N int
)
INSERT INTO @Tbl (N) SELECT TOP 1000 1 FROM syscolumns a CROSS JOIN syscolumns b
SELECT pk, SUM(pk) OVER (ORDER BY pk )
FROM @Tbl

View 9 Replies View Related

SQL Server 2012 :: Where Clause In Dynamic Query?

Jul 2, 2015

I am creating a dynamic query where i am appending a where clause something like -

IF (@CurCaptureDate IS NOT NULL)
SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + @CurCaptureDate

PS - CD.CaptureDate is datetime and @CurCaptureDate is also datetime

but when executing , it gives this error - Msg 241, Level 16, State 1, Line 169 Conversion failed when converting date and/or time from character string.

i am not able to use convert here with these quotes.

i tried this - SET @where_clause = @where_clause + CHAR(10) + 'AND CD.CaptureDate = ' + CONVERT(VARCHAR(25),@CurCaptureDate )

but it makes it to -

AND CD.CaptureDate = Jul 19 2014 12:00AM. I would need the date in quotes.

View 4 Replies View Related

SQL Server 2012 :: Using WHERE Clause For Report Generation

Sep 11, 2015

Because of the way in which a specific piece of code is written, I'm bound into using a WHERE clause for a report generation.Each Inspection generates a unique Inspection Number. Any re-inspection created from that inspection is assigned that Inspection Number and appended with ".A", ".B", ".C" and so on.

The problem is this: Each row's Primary Key is the "InspectionId" in "dbo.v_InspectionDetailsReports". I need to return not only the data related to that particular InspectionId, but also the data related to any previous related inspection. For example, if I have a main number of CCS-2012 and three re-inspections, CCS-2012.A, CCS-2012.B and CCS-2012.C, and I report on CCS-2012.B, I need all the data for CCS-2012, CCS-2012.A and CCS-2012.B but NOT CCS-2012.C.

I would prefer to not have to do everything in a WHERE statement, but my hands are a bit tied.

The "SELECT * FROM dbo.v_InspectionDetailsReports WHERE . . ." is already hardcoded (don't ask).
SELECT *
FROM dbo.v_InspectionDetailsReports
WHERE ( RefOnly = 0
OR RefOnly IS NULL

[code]...

View 5 Replies View Related

SQL Server 2012 :: OVER Clause With Insert Very Slow

Sep 29, 2015

I am using an aggregate with the OVER clause.Running the script is fast less than 1 second but when I say insert into a temp table the execution plan is very different at it take 8 seconds.I have attached the execution plans. Also the Statistics IO, Time messages. I am using SQL Server 2014 with backward compatibility to 2008 R2.

if (select OBJECT_ID('tempdb..#MM')) is not null drop table #MM
CREATE TABLE #MM ([MyTableID] [int], [ParticipantID] [int], [ConferenceID] [nvarchar](50), [Points] [money], [DateCreated] [datetime], [StartPoints] [money], [EndPoints] [money], [LowPoints] [money], [HighPoints] [money])
insert into #MM ([MyTableID], [ParticipantID], [ConferenceID], [Points], [DateCreated], [StartPoints], [EndPoints], [LowPoints], [HighPoints])
selectmm.MyTableID, mm.ParticipantID, mm.ConferenceID, mm.Points, mm.DateCreated,

[code]....

View 2 Replies View Related

SQL Server 2012 :: OUTPUT Clause Returning Wrong Row?

Nov 6, 2014

I'm looking at various methods for deleting duplicate rows. Among the alternatives, one works just fine but gives me results that make me go?.

Consider this script:

declare @t table (a int, b int, c int, d int, e int)
insert into @t (a, b, c, d, e) values
(1, 2, 3, 4, 5),
(3, 4, 2, 3, 4),
(1, 2, 3, 4, 5)

select a,b,c,d,e, rn = row_number() over (
partition by a,b,c,d,e

[Code] ....

The code works -- that is, the duplicate row is deleted. However the output clause returns:

abcdern
123451

So....why? Why does the output clause show that the row with rn=1 was deleted, when the where clause stipulates rn > 1?

View 9 Replies View Related

SQL Server 2012 :: How To Append Go Clause To 10k Lines Of Code

Dec 28, 2014

I have 10k indexes I need to rebuild and each time the script reaches an error it stops all further activity. How can I append 'GO' to the end of each line so it will continue on error messages?

Once I have the syntax I can do a find and replace function in Notepad++

USE [AdventureWorks2014] + char(13) + char(10) + GO
ALTER INDEX [IX_Person] ON [Person].[Person] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO
ALTER INDEX [IX_Emp] ON [HumanResources].[Employee] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF) + char(13) + char(10) + GO
************** Truncate ***********

View 3 Replies View Related

SQL Server 2012 :: Filtering Query Using CASE Statement Within WHERE Clause

Aug 21, 2014

How I am using a CASE statement within a WHERE clause to filter data:

CREATE PROCEDURE dbo.GetSomeStuff
@filter1 varchar(100) = '',
@filter2 varchar(100) = ''
AS
BEGIN
SELECT

[Code] .

What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.

Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?

View 5 Replies View Related

SQL Server 2012 :: How To Modify ORDER BY Clause At Runtime Using Parameter

Jun 4, 2015

the code below works (this is only a quick dumbed down version of the actual code, it might not work 100% for all cases). Is it at all possible to exploit the functions that were added to SSQL since v. 2005 to simplify this code ?

In SSRS, a parameter allows the user to create a list of invoices (from CRM) to be ordered in any of the following ways the user prefers:

'Document Date (most recent date first)'
'Document Number (highest number first)'
'Document Date (most recent first) and Number'
'Document Number (lowest number first)'

The invoices have a (supposedly) sequential identity-generated number. However Accounting may want to set a different date than the creation date on some invoices. So there is no way the invoice numbers will be in the same sequence as the invoice dates.

So I just created the "sorting fields" - they appear as junk in the output dataset (just do not drop them in the SSRS tablix - they have to be part of the SELECT statement to be usable in the ORDER BY clause.

The code is:

DECLARE @ls_OrderBy varchar(80)
--'Document Number (highest number first)'
--'Customer and Document Date (most recent date first)'
--'Customer and Document Number (highest number first)'
--'Document Date (most recent first) and Number'

[code]....

View 9 Replies View Related

SQL Server 2012 :: How To Use Unique Identifiers In Where Clause Not In Select Statement

Jun 8, 2015

I have a two tables each having a uniqueidentifier column person_id

I am trying to a select statement where I want a list of the person_id's in one table that are not in another table.

-- insert into wch_needed those who need checked

insert into #wch_needed (person_id, rendered_by )
select distinct e.person_id, e.rendered_by
from #wch_who o, encounter e
where o.person_id not in (select distinct person_id from #wch_have )
and o.person_id = e.person_id

the where conditional

where o.person_id not in (select distinct person_id from #wch_have )

does not work.

How can I do this?

View 4 Replies View Related

SQL Server 2012 :: Error Message - Aggregate Function And Group By Clause

Feb 19, 2014

I'm trying to write a query to select various columns from 3 tables. In the where clause I use a set of conditions, but most important condition is that I only want to see all results from the different columns where the ph.ProdHeaderDossierCode contains at least 25 lines of processed hours. I tried this with group by and having, but I constant get error messages on all other columns that I want to see: "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause". How can I make this so I can see all information I need?

Here is my code so far:

selectph.CalculatedTotalTime,
ph.ProdHeaderDossierCode,
ph.MachGrpCode,
ph.EmpId,
pd.PartCode
fromdbo.T_ProcessedHour ph,

[Code] ....

View 8 Replies View Related

SQL Server 2012 :: Producing Running Total Column In Select Clause

Jul 27, 2014

I want to create the following scenario. I have a table that stores employees working on projects and their project hours by week, but now I also need a running total per week for each of those projects. For example take a look below:

EmployeeID, Project, Sunday, Monday, Tuesday,....Saturday, ProjectHours, TotalProjectHoursPerWeek(this is the column I am trying to derive), FiscalWeek

101, ProjectABC, 5,5,5,...5, 20, 40,25
102, ProjectXYZ 4,4,4,....4, 20 ,40,25
103,ProjectQWE, 2,2,2,...2, 8, 32,26
104, ProjectPOP, 6,6,6,...6, 24, 32,26

What I have tried so far:

Correlated Subquery:
SELECT EmployeeID,Project, Sunday, Monday,....Saturday, ProjectHours, SELECT(SUM(ProjectHours) FROM dbo.TableABC ap GROUP BY FiscalWeek),
FROM
dbo.TableABC a

I got this to work one time before, but now I am getting the following error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

View 2 Replies View Related

SQL Server 2012 :: Behavior Of Brackets In Select Clause When Declaring Variables?

Oct 11, 2014

I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'

Below is Query 1:

Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar= pub_name
FROM publishers
WHERE pub_id= '999'
Select @testvar

Out put of this query is 'Noname'

BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement

Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar=(Select pub_name
FROM publishers
WHERE pub_id= '999')
Select @testvar

View 4 Replies View Related

SQL Server 2012 :: Create Variable In Select Query And Use It In Where Clause To Pass The Parameter

Sep 9, 2014

I am writing a stored procedure and have a query where I create a variable from other table

Declare @Sem varchar (12) Null
@Decision varchar(1) Null
Select emplid,name, Semester
Decision1=(select * from tbldecision where reader=1)
Decision2=(select * from tbldecision where reader=2)
Where Semester=@Sem
And Decision1=@Decision

But I am getting error for Decision1 , Decision2. How can I do that.

View 6 Replies View Related

SQL 2012 :: Can Combine Performance Monitor Counters With Extended Events

Oct 14, 2014

In the past, I've combined server side traces with Perfmon successfully, which is pretty useful, I know that. I would like to do the same with Extended Events, so I can correlate and analyze the server side as well.

View 4 Replies View Related

FYI: BUG: &#34;IF UPDATE&#34; Functionality Broken In SQL Server 6.5 SP5, 5a

May 18, 1999

see article: Article ID: Q216700 .
Within a trigger on SQL Server 6.5 Service Pack 5 or 5a, the IF UPDATE clause will evaluate to true for all columns when an INSERT is performed, even if there is no value specified and no default value exists for the column

http://support.microsoft.com/support/kb/articles/q216/7/00.asp

View 2 Replies View Related

Questions On Use Of SQL Server 2005 Functionality In Gridview Paging

Jun 25, 2007

I have a webpage that displays 4000 or more records in a GridView control powered by a SqlDataSource.  It's very slow.  I'm reading the following article on custom paging: http://aspnet.4guysfromrolla.com/articles/031506-1.aspx.  This article uses an ObjectDataSource, and some functionality new to Sql Server 2005 to implement custom paging.There is a stored procedure called GetEmployeesSubestByDepartmentIDSorted that looks like this:ALTER PROCEDURE dbo.GetEmployeesSubsetByDepartmentIDSorted(    @DepartmentID        int,    @sortExpression        nvarchar(50),    @startRowIndex        int,    @maximumRows        int)AS    IF @DepartmentID IS NULL        -- If @DepartmentID is null, then we want to get all employees        EXEC dbo.GetEmployeesSubsetSorted @sortExpression, @startRowIndex, @maximumRows    ELSE      BEGIN        -- Otherwise we want to get just those employees in the specified department        IF LEN(@sortExpression) = 0            SET @sortExpression = 'EmployeeID'        -- Since @startRowIndex is zero-based in the data Web control, but one-based w/ROW_NUMBER(), increment        SET @startRowIndex = @startRowIndex + 1        -- Issue query        DECLARE @sql nvarchar(4000)        SET @sql = 'SELECT EmployeeID, LastName, FirstName, DepartmentID, Salary,                     HireDate, DepartmentName        FROM            (SELECT EmployeeID, LastName, FirstName, e.DepartmentID, Salary,                     HireDate, d.Name as DepartmentName,                     ROW_NUMBER() OVER(ORDER BY ' + @sortExpression + ') as RowNum             FROM Employees e                INNER JOIN Departments d ON                    e.DepartmentID = d.DepartmentID             WHERE e.DepartmentID = ' + CONVERT(nvarchar(10), @DepartmentID) + '            ) as EmpInfo        WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @startRowIndex) +                         ' AND (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '                         + CONVERT(nvarchar(10), @maximumRows) + ') - 1'                -- Execute the SQL query        EXEC sp_executesql @sql      ENDThe part that's bold is the part I don't understand.  Can someone shed some light on this for me?  What is this doing and why?Diane 

View 4 Replies View Related

SQL Server 2008 :: Transaction Replication Functionality Within Mixed SPs

Jul 1, 2015

Currently, we are on SQL2008R2 EE SP1 CU13 across the board.

We are planning on upgrading the primary SQL server to SP3.

Question: Will transactional replication continue to work properly even if the Publisher is upgraded to SP3 but the subscribers remain at SP1 CU13 ?

View 2 Replies View Related

SQL Server 2008 :: Table(s) Design For Messaging Functionality?

Jul 21, 2015

I'm in the process of building messaging functionality in to my application where by users can contact one another, look at it as a dating site, you click on someones profile, view their profile and then send that user a message.

I started to build the table which looked like this:

Id (PK) (Increments by 1)

ToUserId (FK) -- User who they're getting in contact with

FromUserId (FK) -- User who sent the message

Content (nvarchar(3000)) -- Message being send

Status (int) -- read / new / deleted / sent

EmailDate (datetime)

EmailDeleted (datetime)

But the problem with this setup is both user's maybe sending / replying to each other so I would have multiple entries / statuses in one table which may become a nightmare to manage / control.

View 9 Replies View Related

SQL 2012 :: FROM Clause Have Same Exposed Names

Jul 2, 2014

We're getting

Msg 1013, Level 16, State 1, Line 1

The objects "MYTEST2.TEST" and "mytest.TEST" in the FROM clause have the same exposed names. Use correlation names to distinguish them.use of fully qualified names is allowed without having to alias them.One workaround is changing compatibility mode to 80.Is there another (startup flag?).Reason for no alias: MS Reportbuilder doesn't provide them when building queries

CREATE SCHEMA MYTEST;
go
CREATE SCHEMA MYTEST2;
go
CREATE TABLE MYTEST.TEST

[code]....

View 2 Replies View Related

SQL 2012 :: Output Clause With Update

Sep 26, 2014

I need to update the account_status and show the audit in the ssrs report.

I wrote the following query in the ssrs report but when I click on refresh fields it is throwing error incorrect syntax near output

Declare @username nvarchar(50)
Declare @UserStatus Table
(id bigint,
Username nvarchar(50),
Oldaccount_status varchar(255),
Newaccount_status varchar(255))

[Code] .....

View 2 Replies View Related

SQL 2012 :: Coalesce WHERE Clause With XML Data

Oct 9, 2014

Looking to coalesce a WHERE clause with XML data. I cannot supply test data.

If 472 is null, check the NEXT XML field at grab the 150, if both 472 AND 150 do NOT exist let it be NULL.

set @Date = (select top 1 XL.value('(DTM/DTM.02/DTM.02.1)[1]', 'varchar(100)') from #src
where coalesce(XL.value('(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '472', '(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '150')

View 1 Replies View Related

SQL 2012 :: SSIS And Variables With Where Clause

Jan 15, 2015

I have an SSIS package which uses variables and foreach loop containers so connect to multiple instances to retrieve config data. I am adding an extra step to include the port of each instance.

I used a select from a central table to get the connection strings, which is put into [User::Server2]

1) execute SQL task to collect the port

(DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =

'SoftwareMicrosoftMicrosoft SQL ServerMSSQLServerSuperSocketNetLibTcpIpAll',
@value_name = 'TcpPort',
@value = @portNumber OUTPUT
)

Which is then put into a variable @Portnumber

2) I then need to insert this into a certain server with a where clause including another variable

so something like
"INSERT INTO DBO.InstanceConfig VALUES ('"+@[User::Portnumber]+"') where Serverinstance = ('"+@[User::Server2]+"')

but it doesnt work like that.. is there an easier way

View 1 Replies View Related

SQL 2012 :: Update A Table Using Where Clause?

Oct 29, 2015

I have a temporary table #Temp with, among others, a column CountryCode and a column Lastname.I would like to change the ü that appears in some names to u (u umlaut to a plain u), but only for those that have the nationality 'Ned'

My code so far:

Update #Temp
set LastName = replace(Lastname, 'ü', 'ue') WHERE CountryCode = 'Ned'

This code deletes all entries in the column Lastname

I think it must be easy, but I keep staring at the code.

What to do?

View 5 Replies View Related

SQL 2012 :: Estimate Row Count - WHERE Clause Of Statement

Oct 16, 2014

How SQL calculates the estimated row count when < or > are used in the WHERE clause of a statement. For example:-

WHERE Created_Datetime_utc > CONVERT(DATETIME,'2014-10-14 10:00:00',102)

I know how the estimated number of rows are calculated when an = is used but I've been googling and cannot find anything about < and >.

View 2 Replies View Related

SQL 2012 :: Pass Where Clause To A Store Procedure

Nov 2, 2015

From my one app, a dynamic where clause will generate like below.

//where ordercity='london' and orderby='smith'
//where orderamount > 100 and shipcity='new york'

From server, I created a store procedure as below.

SELECT * FROM [Order] WHERE @whereSql

How to complete the store procedure so that I don't need to pass parameters one by one...

View 1 Replies View Related

Analysis :: SSAS 2012 - MDX To Find Last 7 Days Data In Where Clause?

Jul 6, 2015

I have a MDX query , where I have a date Range in where clause.

I want to replace it with Cuurent Date and Last 7 days date.

I tried multiple ways using NOW function , but could not get it correct .

modifying the Query so that I can fetch DATA for last 7 days 

SELECT NON EMPTY { [Measures].[X] } ON COLUMNS, NON EMPTY { ([PRODUCT].[PRODUCT].[PRODUCT].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM ( SELECT ( { [COLOR].[COLORName].&[BLACK], [COLOR].[COLORName].&[BLUE] } ) ON COLUMNS
FROM ( SELECT ( [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[3].&[7].&[20150706] : [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[2].&[6].&[20150629] ) ON COLUMNS
FROM [MYCUBE]))

I want to replace Date Hard code value , I have used Calendar Hierarchy of date dimension. to find Last 7 days Data.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved