help in select case issue

sql express 2012

hello: i am trying to case in the where part and having a syntax errors - 

this is what i am trying to do:

select all the days in week number x including last year if necessary... so
if the year start not at the beginning of the week then 
look in last year as well ( for the same week number of this year and last week nu of last year)

declare 
@yyyy int = 2014,	-- THE YEAR
@mm int = 1,		-- THE MONTH
@week1No int = 1,	-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37	-- THE last WEEK NUMBER IN last YEAR

select count(tblDay.start)		-- tblDay.start IS smallDatetime
from tblDay
where 
	CASE
		WHEN @mm > 1 THEN
			(MONTH(start) = @mm-1) 
			AND (YEAR(start) = @yyyy) 
			and weekNo = @week1No		
		WHEN @mm = 1 THEN
			   -- last week of the year
			((MONTH(start) = @mm) AND (YEAR(start) = @yyyy) and weekNo = @week1No)
			OR -- lest year same week
			((MONTH(start) = 12) AND (YEAR(start) = @yyyy-1)and weekNo = @week2No)
		WHEN @mm > 1 THEN
			(MONTH(start) = @mm-1) 
					AND (YEAR(start) = @yyyy) 
					and weekNo = @week1No
	END

May 24th, 2015 5:04am

See another posting; use a calendar table with a week-date column.
Free Windows Admin Tool Kit Click here and download it now
May 24th, 2015 10:39am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics