erikTsomik
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SUM(CASE WHEN DATEPART(WEEKDAY, SU.SessionStart) IN (1, 7) THEN 1 ELSE 0 END) AS WeekendCount