Solved

SQL query get records in between time slots

Posted on 2015-02-09
10
128 Views
Last Modified: 2015-02-16
I am trying to create a query which will return records that are max 2 days per week from 2:00pm to 5:59 pm on weekdays from September to May. But ca nit figure out how to do time portion and a month portion

And what I came up with is the following:
SELECT WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),105),CountID = Count(S.sessionKey)
FROM session S
INNER JOIN sessionUnit SU on SU.sessionKey= S.sessionKey
INNER JOIN product P on p.productKey = S.productKey
WHERE SU.instructorKey = 13879
AND  convert(DATE,su.sessionStart) >= DateAdd("d",45,getDate()) AND convert(DATE,su.sessionStart) < DateAdd("d",91,getDate())
AND datediff(day, 0, su.sessionStart) % 7 not in (5, 6)
and P.productKey=2
and (DATEPART(minute,SU.sessionStart) BETWEEN 840 AND 1440 OR DATEPART(minute, SU.sessionStart) <= 180)

GROUP BY dateadd(week, datediff(week, 0, SU.sessionStart), 0)
HAVING COUNT(S.sessionKey) >= 2

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
  • 4
  • 2
10 Comments
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 150 total points
ID: 40599359
SELECT WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),105),CountID = Count(S.sessionKey)
FROM session S
INNER JOIN sessionUnit SU on SU.sessionKey= S.sessionKey
INNER JOIN product P on p.productKey = S.productKey
WHERE SU.instructorKey = 13879
AND  su.sessionStart >= DateAdd(Year,-1,dateadd(day, datediff(day, 0, getdate()), 0))
AND DatePart(month, su.sessionStart) in (9, 10, 11, 12, 1, 2, 3, 4, 5)
AND DatePart(hour, su.sessionStart) between 14 and 17

AND datediff(day, 0, su.sessionStart) % 7 not in (5, 6)
and P.productKey=2

GROUP BY dateadd(week, datediff(week, 0, SU.sessionStart), 0)
HAVING COUNT(S.sessionKey) >= 2
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40599387
what is this line for
AND  su.sessionStart >= DateAdd(Year,-1,dateadd(day, datediff(day, 0, getdate()), 0))

I wan to do the check every 45 to 90 days from today I think My line is correct:
AND  convert(DATE,su.sessionStart) >= DateAdd("d",45,getDate()) AND convert(DATE,su.sessionStart) < DateAdd("d",91,getDate())
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40599533
You're right then as far as the dates go, if you want to go forward 45 to 91 days. I misinterpreted the "from September to May" in the original q.

However, as far as coding, you should never use a function on a table column when it can be avoided.

AND  su.sessionStart >= DateAdd(Day, 45, DateAdd(Day, Datediff(Day, 0, getdate()), 0))
AND  su.sessionStart < DateAdd(Day, 92, DateAdd(Day, Datediff(Day, 0, getdate()), 0))
0
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40599813
You need to very careful when using GETDATE(), it includes the time of day!

for example, if getdate() was used up at 23:17 in the evening, then this: DateAdd("d",45,getDate())

would result in a datetime 45 days in the future AT 23:17

that is why Scott has used:  

DateAdd(Day, Datediff(Day, 0, getdate())

which "truncates" getdate() to a time of 00:00:00 and THEN add 45 or 92 days

an alternative would be:

AND  su.sessionStart >= DateAdd(Day, 45, cast( getdate() as date), 0))
AND  su.sessionStart < DateAdd(Day, 92, cast( getdate() as date), 0))
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40604522
HOW would I also pass a day to find out if that day belongs to the week that max out 2 days window
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40604586
afraid I don't understand the comment just above this one

perhaps you could explain by example?
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40604600
the query below give me start week of week wich has more than 2 sessions completed. Is there a was I can pass A date to the query to figure out if the provided date is withing that week OR need to work that out once I get a data-set back

SELECT WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),105),CountID = Count(S.sessionKey)
FROM session S
INNER JOIN sessionUnit SU on SU.sessionKey= S.sessionKey
INNER JOIN product P on p.productKey = S.productKey
WHERE SU.instructorKey = 13879
AND  su.sessionStart >= DateAdd(Year,-1,dateadd(day, datediff(day, 0, getdate()), 0)) 
AND DatePart(month, su.sessionStart) in (9, 10, 11, 12, 1, 2, 3, 4, 5)
AND DatePart(hour, su.sessionStart) between 14 and 17
AND datediff(day, 0, su.sessionStart) % 7 not in (5, 6)
and P.productKey=2

GROUP BY dateadd(week, datediff(week, 0, SU.sessionStart), 0)
HAVING COUNT(S.sessionKey) >= 2

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40604720
sort of depends on what you want to do...

as a where clause condition?

...
and @param_date between  DATEADD(WEEK, DATEDIFF(WEEK, 0, SU.sessionStart), 0)
          and DATEADD(WEEK, DATEDIFF(WEEK, SU.sessionStart)+1, 0)


as a item of information?

SELECT ...
case when @param_date between  DATEADD(WEEK, DATEDIFF(WEEK, 0, SU.sessionStart), 0)
          and DATEADD(WEEK, DATEDIFF(WEEK, SU.sessionStart)+1, 0) then 1 else 0 end as info_item
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40606153
How would I change the logic to count number of days not number of sessions.
For example, on 1/1/2015 the client did 2 session that should be count as 1 and on 1/2/2015 the client did 1 session that another 1. I my code I only count number sessions for week when I should be counting number days per week

DECLARE @sessionDate DATE 
SET @sessionDate = '2015-04-27'
SELECT WeekStart = convert(varchar(10),dateadd(week, datediff(week, 0, SU.sessionStart), 0),105),CountID = Count(S.sessionKey) 
FROM session S 
INNER JOIN sessionUnit SU on SU.sessionKey= S.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 (260) OR S.locationKey in (260)) 

WHERE SU.instructorKey = 13879 
AND convert(DATE,su.sessionStart) >= DateAdd("d",45,getDate()) AND convert(DATE,su.sessionStart) < DateAdd("d",91,getDate()) 
AND datediff(day, 0, su.sessionStart) % 7 not in (5, 6) 
AND DatePart(month, su.sessionStart) in (9, 10, 11, 12, 1, 2, 3, 4, 5) 
and P.productKey=2 
AND DatePart(hour, su.sessionStart) between 14 and 17 
and @sessionDate between DATEADD(WEEK, DATEDIFF(WEEK, 0, SU.sessionStart), 0) and DATEADD(WEEK, DATEDIFF(WEEK, 0,SU.sessionStart)+1, 0) 
GROUP BY dateadd(week, datediff(week, 0, SU.sessionStart), 0) 
--HAVING COUNT(S.sessionKey) >= 2 

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 350 total points
ID: 40606605
try COUNT( DISTINCT DATEPART(weekday, SU.sessionStart) ) you can do this as well if you need both
DECLARE @sessionDate date
SET @sessionDate = '2015-04-27'
SELECT
      WEEKSTART = CONVERT(varchar(10), DATEADD(WEEK, DATEDIFF(WEEK, 0, SU.sessionStart), 0), 105)
    , COUNTID = COUNT(S.sessionKey)
    , COUNTDYS = COUNT( DISTINCT DATEPART(weekday, SU.sessionStart) )
FROM session S
      INNER JOIN sessionUnit SU ON SU.sessionKey = S.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 (260)
      OR S.locationKey IN (260))

WHERE SU.instructorKey = 13879
      AND CONVERT(date, SU.sessionStart) >= DATEADD("d", 45, GETDATE())
      AND CONVERT(date, SU.sessionStart) < DATEADD("d", 91, GETDATE())
      AND DATEDIFF(DAY, 0, SU.sessionStart) % 7 NOT IN (5, 6)
      AND DATEPART(MONTH, SU.sessionStart) IN (9, 10, 11, 12, 1, 2, 3, 4, 5)
      AND P.productKey = 2
      AND DATEPART(HOUR, SU.sessionStart) BETWEEN 14 AND 17
      AND @sessionDate BETWEEN DATEADD(WEEK, DATEDIFF(WEEK, 0, SU.sessionStart), 0) AND DATEADD(WEEK, DATEDIFF(WEEK, 0, SU.sessionStart) + 1, 0)
GROUP BY
      DATEADD(WEEK, DATEDIFF(WEEK, 0, SU.sessionStart), 0)
--HAVING COUNT(S.sessionKey) >= 2 

Open in new window


{+edit}
or you could use

count( distinct datepart(dayofyear, [your_date_field]  ) )
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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…
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.
Viewers will learn how the fundamental information of how to create a table.

751 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