Solved

SQL HELP

Posted on 2016-09-08
2
81 Views
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 
FROM ( 
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 
)T 
WHERE @sessionDate BETWEEN T.WeekStart AND T.WeekEnd 
GRoup By T.WeekStart,T.WeekEnd,T.CountID

Open in new window

0
Comment
Question by:erikTsomik
2 Comments
 
LVL 11

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.
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 41790864
Why don't you use DATEPART(WEEK, SU.sessionStart) to calcuclate the week number?

E.g.

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 ,
            T.CountID;

Open in new window

0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

947 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now