[SOLUTION] sql count for a week

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

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)

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 72

Scott PletcherSenior DBACommented: 2015-02-25

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.

LVL 19

erikTsomikSystem Architect, CF programmer Author Commented: 2015-02-25

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

where ...
and SU.sessionStart >= '20150513' and
SU.sessionStart < DATEADD(DAY, 8, '20150513')
and ...

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

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')

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)

Any suggestions

LVL 51

PortletPaulEE Topic AdvisorCommented: 2015-02-25

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

sql count for a week

Who is Participating?