Solved

sql query for weeks

Posted on 2015-02-02
5
112 Views
Last Modified: 2015-02-03
I need to create a query which will return dates from the 45 to 90 days from now. The number of session per week should 9 or more and number of sessions per week is only calculated per working days (Satarday / sundays excluded).

I start doing the query but seems to be stock in here ...

SELECT DATEADD(day, DATEDIFF(day, 0, SU.sessionStart), 0) AS reservedOn,
 COUNT(SU.sessionKey) AS Count,
 DATEDIFF(day,getDAte(),SU.sessionStart),l2.locationKey 
 
 FROM SessionUnit SU 
 Inner join Session S on S.sessionKey = SU.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 (225,228,260) OR S.locationKey in (225,228,260)) 
 WHERE convert(DATE,su.sessionStart) >= DateAdd("d",45,getDate()) 
 AND convert(DATE,su.sessionStart) <= DateAdd("d",90,getDate()) 
and ((DATEPART(dw, su.sessionStart) + @@DATEFIRST) % 7) NOT IN (0, 1)
 AND p.productKey = 2 
 GROUP BY DATEADD(day, DATEDIFF(day, 0, sessionStart), 0),DATEDIFF(day,getDAte(),SU.sessionStart),l2.locationKey 

 ORDER BY reservedOn 

Open in new window

0
Comment
Question by:erikTsomik
  • 4
5 Comments
 
LVL 19

Author Comment

by:erikTsomik
ID: 40584829
Any suggestions
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40584870
What is the specific issue you're having?

Some minor tweaks would help the SQL perform, but other than that, what about it is not working?


SELECT
    DATEADD(day, DATEDIFF(day, 0, SU.sessionStart), 0) AS reservedOn,
    COUNT(SU.sessionKey) AS Count,
    DATEDIFF(day,getDAte(),SU.sessionStart),l2.locationKey
FROM SessionUnit SU
Inner join Session S on S.sessionKey = SU.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 (225,228,260) OR S.locationKey in (225,228,260))
WHERE
    su.sessionStart >= DateAdd(day,45,dateadd(day, datediff(day, 0, getdate()), 0))
AND su.sessionStart <  DateAdd(day,91,dateadd(day, datediff(day, 0, getdate()), 0))
AND datediff(day, 0, su.sessionStart) % 7 not in (5, 6) --always works regardless of date settings
AND p.productKey = 2
GROUP BY DATEADD(day, DATEDIFF(day, 0, sessionStart), 0),DATEDIFF(day,getDAte(),SU.sessionStart),l2.locationKey

ORDER BY reservedOn
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40584917
The question is I need the number of session for the week to be >=9. What I am trying to is to say it was done 3 sessions on 4/20,3 sessions on 4/21, 3 sessions 4/22 I want to grayed out the whole week on jquery ui datepicker. SO I need to know every single day but the count for the week must be 9 or more.
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40584919
O r event better example the client did 9 sessions on 4/15 I want to great out the whole week from 4/13 to 4/17 because he max out number 9 sessions per week
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40585087
I came up with query But now how would I get the end date .If I get an end date than I can do the range

Select WeekStart =
  dateadd(week, datediff(week, 0, SU.sessionStart), 0)
  ,CountID = Count(S.sessionKey)
 
FROM SessionUnit SU
Inner join Session S on S.sessionKey = SU.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 (225,228,260) OR S.locationKey in (225,228,260))
WHERE  su.sessionStart >= DateAdd(day,45,dateadd(day, datediff(day, 0, getdate()), 0))
AND su.sessionStart <  DateAdd(day,91,dateadd(day, datediff(day, 0, getdate()), 0))
AND datediff(day, 0, su.sessionStart) % 7 not in (5, 6) --always works regardless of date settings
AND p.productKey = 2  and SU.instructorKey = 42
GROUP BY
 dateadd(week, datediff(week, 0, SU.sessionStart), 0)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
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.
Viewers will learn how the fundamental information of how to create a table.

867 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

18 Experts available now in Live!

Get 1:1 Help Now