Solved

SQL query get records in between time slots

Posted on 2015-02-09
10
125 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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