?
Solved

SQL HELP

Posted on 2016-09-08
2
Medium Priority
?
104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 13

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 35

Accepted Solution

by:
ste5an earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

752 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