Solved

sql query for weeks

Posted on 2015-02-02
5
110 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

744 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

16 Experts available now in Live!

Get 1:1 Help Now