Solved

SQL query get records in between time slots

Posted on 2015-02-09
10
120 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:ScottPletcher
ScottPletcher 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:ScottPletcher
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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now