Solved

SQL query get records in between time slots

Posted on 2015-02-09
10
135 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 49

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 49

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 49

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 49

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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

615 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