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

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
 
PortletPaulfreelancerCommented:
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

0
 
Scott PletcherSenior DBACommented:
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.
0
 
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
 
Scott PletcherSenior DBACommented:
where ...
and SU.sessionStart >= '20150513' and
    SU.sessionStart < DATEADD(DAY, 8, '20150513')
and ...
0
 
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
0
 
Scott PletcherSenior DBACommented:
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')
0
 
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)
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
Any suggestions
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Advertise Here