Posted on 2016-09-08
Last Modified: 2016-09-09
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

Question by:erikTsomik
LVL 12

Expert Comment

by:Máté Farkas
ID: 41790863
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.
LVL 33

Accepted Solution

ste5an earned 500 total points
ID: 41790864
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


Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question