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... soif 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