Having a problem with the syntax, need to return a result based on Number and date range, if its 6,2 then this date range else the other date range.
DECLARE @Test TABLE
(
ID NVARCHAR(10)
,Number INT
,[Date] DATETIME
)
INSERT INTO @Test VALUES ('TG32',6,'20160715')
INSERT INTO @Test VALUES ('TG47',6,'20160803')
INSERT INTO @Test VALUES ('AG9',6,'20160805')
INSERT INTO @Test VALUES ('BF27',2,'20160804')
INSERT INTO @Test VALUES ('QD65',2,'20160802')
INSERT INTO @Test VALUES ('F98',0,'20160806')
INSERT INTO @Test VALUES ('GC5',0,'20160731')
INSERT INTO @Test VALUES ('HT76',0,'20160802')
INSERT INTO @Test VALUES ('KL81',0,'20160805')
INSERT INTO @Test VALUES ('WR52',0,'20160802');
SELECT TOP 10 * FROM @Test
WHERE [Date] = CASE WHEN Number IN ('6','2') THEN ([Date] BETWEEN CAST(GETDATE() - 6 AS date) AND GETDATE())
ELSE ([Date] BETWEEN CAST(GETDATE() - 1 AS date) AND GETDATE()) END
You can't use CASE
in this way, in sql case is not like if then else
it is more similar to function (test?if_yes;if_not)
WHERE [Date] BETWEEN CAST(GETDATE() - CASE WHEN Number IN ('6','2') THEN 6 ELSE 0 END AS date) AND GETDATE()