[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL HELP

Posted on 2016-09-08
2
Medium Priority
?
106 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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

656 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