Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql query for weeks

Posted on 2015-02-02
5
Medium Priority
?
123 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 70

Accepted Solution

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

972 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