SQL query get records in between time slots

what is this line for
AND  su.sessionStart >= DateAdd(Year,-1,dateadd(day, datediff(day, 0, getdate()), 0))

I wan to do the check every 45 to 90 days from today I think My line is correct:
AND  convert(DATE,su.sessionStart) >= DateAdd("d",45,getDate()) AND convert(DATE,su.sessionStart) < DateAdd("d",91,getDate())

You're right then as far as the dates go, if you want to go forward 45 to 91 days. I misinterpreted the "from September to May" in the original q.

However, as far as coding, you should never use a function on a table column when it can be avoided.

AND  su.sessionStart >= DateAdd(Day, 45, DateAdd(Day, Datediff(Day, 0, getdate()), 0))
AND  su.sessionStart < DateAdd(Day, 92, DateAdd(Day, Datediff(Day, 0, getdate()), 0))

You need to very careful when using GETDATE(), it includes the time of day!

for example, if getdate() was used up at 23:17 in the evening, then this: DateAdd("d",45,getDate())

would result in a datetime 45 days in the future AT 23:17

that is why Scott has used:  

DateAdd(Day, Datediff(Day, 0, getdate())

which "truncates" getdate() to a time of 00:00:00 and THEN add 45 or 92 days

an alternative would be:

AND  su.sessionStart >= DateAdd(Day, 45, cast( getdate() as date), 0))
AND  su.sessionStart < DateAdd(Day, 92, cast( getdate() as date), 0))

HOW would I also pass a day to find out if that day belongs to the week that max out 2 days window

afraid I don't understand the comment just above this one

perhaps you could explain by example?

the query below give me start week of week wich has more than 2 sessions completed. Is there a was I can pass A date to the query to figure out if the provided date is withing that week OR need to work that out once I get a data-set back

SELECT WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),105),CountID = Count(S.sessionKey)
FROM session S
INNER JOIN sessionUnit SU on SU.sessionKey= S.sessionKey
INNER JOIN product P on p.productKey = S.productKey
WHERE SU.instructorKey = 13879
AND  su.sessionStart >= DateAdd(Year,-1,dateadd(day, datediff(day, 0, getdate()), 0)) 
AND DatePart(month, su.sessionStart) in (9, 10, 11, 12, 1, 2, 3, 4, 5)
AND DatePart(hour, su.sessionStart) between 14 and 17
AND datediff(day, 0, su.sessionStart) % 7 not in (5, 6)
and P.productKey=2

GROUP BY dateadd(week, datediff(week, 0, SU.sessionStart), 0)
HAVING COUNT(S.sessionKey) >= 2

Select allOpen in new window

sort of depends on what you want to do...

as a where clause condition?

...
and @param_date between  DATEADD(WEEK, DATEDIFF(WEEK, 0, SU.sessionStart), 0)
          and DATEADD(WEEK, DATEDIFF(WEEK, SU.sessionStart)+1, 0)


as a item of information?

SELECT ...
case when @param_date between  DATEADD(WEEK, DATEDIFF(WEEK, 0, SU.sessionStart), 0)
          and DATEADD(WEEK, DATEDIFF(WEEK, SU.sessionStart)+1, 0) then 1 else 0 end as info_item

How would I change the logic to count number of days not number of sessions.
For example, on 1/1/2015 the client did 2 session that should be count as 1 and on 1/2/2015 the client did 1 session that another 1. I my code I only count number sessions for week when I should be counting number days per week

DECLARE @sessionDate DATE 
SET @sessionDate = '2015-04-27'
SELECT WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),105),CountID = Count(S.sessionKey) 
FROM session S 
INNER JOIN sessionUnit SU on SU.sessionKey= S.sessionKey 
INNER JOIN product P on p.productKey = S.productKey 
CROSS APPLY ( SELECT item AS SPLITLOCATIONKEY from DelimitedSplit8K(S.locationKeyList, ',') )as ca 
INNER JOIN location AS L2 ON CA.SplitLocationKey = L2.LocationKey AND (L2.locationKey in (260) OR S.locationKey in (260)) 

WHERE SU.instructorKey = 13879 
AND convert(DATE,su.sessionStart) >= DateAdd("d",45,getDate()) AND convert(DATE,su.sessionStart) < DateAdd("d",91,getDate()) 
AND datediff(day, 0, su.sessionStart) % 7 not in (5, 6) 
AND DatePart(month, su.sessionStart) in (9, 10, 11, 12, 1, 2, 3, 4, 5) 
and P.productKey=2 
AND DatePart(hour, su.sessionStart) between 14 and 17 
and @sessionDate between DATEADD(WEEK, DATEDIFF(WEEK, 0, SU.sessionStart), 0) and DATEADD(WEEK, DATEDIFF(WEEK, 0,SU.sessionStart)+1, 0) 
GROUP BY dateadd(week, datediff(week, 0, SU.sessionStart), 0) 
--HAVING COUNT(S.sessionKey) >= 2 

Select allOpen in new window