We help IT Professionals succeed at work.

sql count for a week

I have a query where I am counting number of session per weekdays and this part works.

What the problem is when the date is passed I want to check the week and count the number of session in that week, instead I get the number of sessions for that day

Select WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),105),CountID = Count(S.sessionKey)

			FROM SessionUnit SU
			INNER JOIN Session S on S.sessionKey = SU.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 (228,260))
			WHERE  su.sessionStart >= DateAdd(day,45,dateadd(day, datediff(day, 0, getdate()), 0))
			AND su.sessionStart <  DateAdd(day,91,dateadd(day, datediff(day, 0, getdate()), 0))
			AND datediff(day, 0, su.sessionStart) % 7 not in (5, 6)
			AND p.productKey = 2  and SU.instructorKey = 13879
			and convert(date,SU.sessionStart) = '2015-05-13'
			GROUP BY
			 dateadd(week, datediff(week, 0, SU.sessionStart), 0)
			 --HAVING COUNT(S.sessionKey) >= 9

Open in new window

Comment
Watch Question

Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
You have a WHERE condition:
            and convert(date,SU.sessionStart) = '2015-05-13'
that limits sessionStart to just that one day.

You need to change the WHERE conditions for sessionStart to include the entire week you want.
erikTsomikSystem Architect, CF programmer

Author

Commented:
I know When the date get passed I wan to  use that day to figured out which week is belong to and count a number of sessions for that week
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
where ...
and SU.sessionStart >= '20150513' and
    SU.sessionStart < DATEADD(DAY, 8, '20150513')
and ...
erikTsomikSystem Architect, CF programmer

Author

Commented:
hi ScottPletcher I try using your logic but for some reason I am getting 2 records. Also If I change the date to be 5-12 we still in the same week the count is 6

WeekStart      CountID
11-05-2015      5
11-05-2015      9
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
I thought 05-13 was the week start date.  If it's week end date, you need to do this:

SU.sessionStart >= DATEADD(DAY, -6, '20150513') and
SU.sessionStart < DATEADD(DAY, 1, '20150513')
erikTsomikSystem Architect, CF programmer

Author

Commented:
05-13 is the random date in my select statement I am working out what the start date is  
Select WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),105)
erikTsomikSystem Architect, CF programmer

Author

Commented:
Any suggestions
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
suggestion:

      AND SU.sessionStart >= DATEADD(WEEK, DATEDIFF(WEEK, 0, '20150513'), 0)
      AND SU.sessionStart <  DATEADD(WEEK, DATEDIFF(WEEK, 0, '20150513')+1, 0)

This is the same algorithm you are using to do the grouping, so for any specific date this should give you the whole week it relates to.

HOWEVER:
Please look at the where clause as a whole as there are several parts of it that relate to dates. These could easily lead nothing being selected.

I have rearranged it slightly for emphasis
WHERE P.productKey = 2
      AND SU.instructorKey = 13879

      AND SU.sessionStart >= DATEADD(DAY, 45, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
      AND SU.sessionStart < DATEADD(DAY, 91, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

      AND DATEDIFF(DAY, 0, SU.sessionStart) % 7 NOT IN (5, 6)

      AND SU.sessionStart >= DATEADD(WEEK, DATEDIFF(WEEK, 0, '20150513'), 0)
      AND SU.sessionStart <  DATEADD(WEEK, DATEDIFF(WEEK, 0, '20150513')+1, 0)

Open in new window