Solved

sql query for weeks

Posted on 2015-02-02
5
121 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
[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
  • 4
5 Comments
 
LVL 19

Author Comment

by:erikTsomik
ID: 40584829
Any suggestions
0
 
LVL 69

Accepted Solution

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

636 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