• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

SQL query get records in between time slots

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
erikTsomik
Asked:
erikTsomik
  • 4
  • 4
  • 2
2 Solutions
 
Scott PletcherSenior DBACommented:
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
 
erikTsomikSystem Architect, CF programmer Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PortletPaulfreelancerCommented:
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
 
erikTsomikSystem Architect, CF programmer Author Commented:
HOW would I also pass a day to find out if that day belongs to the week that max out 2 days window
0
 
PortletPaulfreelancerCommented:
afraid I don't understand the comment just above this one

perhaps you could explain by example?
0
 
erikTsomikSystem Architect, CF programmer Author Commented:
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
 
PortletPaulfreelancerCommented:
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
 
erikTsomikSystem Architect, CF programmer Author Commented:
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now