Solved

sql query for weeks

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
store vs query adhoc - no show rows 4 37
SQL Query 20 20
SQL - Using an 'array' kind of variable 5 14
Query Task 8 16
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…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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