Link to home
Start Free TrialLog in
Avatar of erikTsomik
erikTsomikFlag for United States of America

asked on

sql count per weekend

I need to be able count a number of classes per weekend provided a selected by user date.

DECLARE @sessionDate DATE
	                                SET @sessionDate = '2016-01-10 06:01:00'

	        						
		                                    SELECT  WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),106) ,CountID = Count(distinct 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 (183,34)
		                                        OR S.locationKey in (183,34))
		                                    WHERE SU.instructorKey =2644
		                                        AND  convert(DATE,su.sessionStart) >= DateAdd("d",45,getDate()) AND convert(DATE,su.sessionStart) < DateAdd("d",91,getDate())
		                                         and datepart(dw, Su.sessionStart) in (1, 7)
		                                       
		                                        and P.productKey in (2,4)
		                                        AND DatePart(hour, su.sessionStart) between 8 and 11
		                                        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)

Open in new window

Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Typically I would do something like

SUM(CASE WHEN DATEPART(WEEKDAY,  SU.SessionStart) IN (1, 7) THEN 1 ELSE 0 END) AS WeekendCount
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial