I am counting the number sessions per week and there is one conditions when I believe I am going wrong.   The rules are the restrictions applies 45 days out till 90 days out. so 8/23/2016 should not be included into the calculation I believe

DECLARE @sessionDate DATE 
SET @sessionDate = '2016-10-23'
SELECT count(*) as cnt 
SELECT WeekStart = DATEADD(dd, -(DATEPART(dw, convert(date,SU.sessionStart,120))-1), 
convert(date,SU.sessionStart,120)) , 
WeekEnd = DATEADD(dd, 7-(DATEPART(dw, convert(date,SU.sessionStart,120))), convert(date,SU.sessionStart,120)) , 
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 (34,150) OR S.locationKey in (34,150)) 
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 
GROUP BY	Su.sessionStart 
WHERE @sessionDate BETWEEN T.WeekStart AND T.WeekEnd 
GRoup By T.WeekStart,T.WeekEnd,T.CountID

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?
ste5anConnect With a Mentor Senior DeveloperCommented:
Why don't you use DATEPART(WEEK, SU.sessionStart) to calcuclate the week number?


WITH    T AS ( SELECT   WeekStart = DATEADD(dd, -( DATEPART(dw, CONVERT(DATE, SU.sessionStart, 120)) - 1 ), CONVERT(DATE, SU.sessionStart, 120)) ,
                        WeekEnd = DATEADD(dd, 7 - ( DATEPART(dw, CONVERT(DATE, SU.sessionStart, 120)) ), CONVERT(DATE, SU.sessionStart, 120)) ,
                        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 ( 34, 150 )
                                                           OR S.locationKey IN ( 34, 150 )
               WHERE    SU.instructorKey = 2644
                        AND P.productKey IN ( 2, 4 )
                        AND CONVERT(DATE, SU.sessionStart) > DATEADD(DAY, 45, GETDATE())
                        AND CONVERT(DATE, SU.sessionStart) < DATEADD(DAY, 91, GETDATE())
                        AND DATEPART(HOUR, SU.sessionStart) BETWEEN 8 AND 11
                        AND DATEPART(WEEK, SU.sessionStart) = DATEPART(WEEK, @sessionDate)
               GROUP BY SU.sessionStart
    SELECT  COUNT(*) AS cnt
    FROM    T
    GROUP BY T.WeekStart ,
            T.WeekEnd ,

Open in new window

Máté FarkasDatabase Developer and AdministratorCommented:
Sorry, this way the investigation is not possible.
You are using user defined functions and we don't know the nature of your data.
Please break down your query to smaller parts and inspect the expected result then add more conditions and joins and always test the intermediate results.
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.