Blocks of Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Blocks of Time

Author

Topic

carmenv323
Starting Member

3 Posts

Posted - 2014-10-30 : 14:21:38

I have the following SQL that I want to count the number of sessions for each provider. a session is a block of time, for example, any time between 9-12 is a morning sessions, 12-5 is an afternoon session etc. I need to count the number of sessions in a month not the appointments within the session, make sense?

select sr.resourceid, st.TimeStart, datename(m,sr.scheduledate) as sesmonth, datename(yy,sr.scheduledate) as year,
(CASE
WHEN DATEPART(hour, st.TimeStart) BETWEEN 0 AND 12 THEN 'MORNING SESSION'
WHEN DATEPART(hour, st.TimeStart) BETWEEN 12 AND 17 THEN 'AFTERNOON SESSION'
WHEN DATEPART(hour, st.TimeStart) BETWEEN 17 AND 24 THEN 'EVENING SESSION'
END )AS SESSIONs

from ScheduleResource sr
inner join scheduletimes st on sr.scheduleid = st.scheduleid
where sr.resourceid = '15' and datename(m,sr.scheduledate) ='April'
group by resourceid, st.TimeStart, datename(m,scheduledate),datename(yy,scheduledate)

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-30 : 14:46:24

Do you mean something like this:

select sr.resourceid
      ,st.TimeStart
      ,datename(m,sr.scheduledate) as sesmonth
      ,datename(yy,sr.scheduledate) as year
      ,SUM(CASE WHEN DATEPART(hour, st.TimeStart) BETWEEN 0 AND 12 THEN 1 ELSE 0 END) AS 'MORNING SESSION'
      ,SUM(CASE WHEN DATEPART(hour, st.TimeStart) BETWEEN 12 AND 17 THEN 1 ELSE 0 END) AS 'AFTERNOON SESSION'
      ,SUM(CASE WHEN DATEPART(hour, st.TimeStart) BETWEEN 17 AND 24 THEN 1 ELSE 0 END) AS 'EVENING SESSION'
  from ScheduleResource sr 
       inner join scheduletimes st
               on sr.scheduleid = st.scheduleid
 where sr.resourceid = '15'
   and datename(m,sr.scheduledate) ='April'
 group by resourceid
         ,st.TimeStart
         ,datename(m,scheduledate)
         ,datename(yy,scheduledate)

carmenv323
Starting Member

3 Posts

Posted - 2014-11-14 : 10:33:15

Thanks, this worked, but when I added the number of visits to it it got kind of complicated.

I've been at this for days and so stuck! They want to know how many AM, PM and Evening sessions in April and how many total visits for the sessions

i.e.
Month AM Visits PM Visits Eve visits
April 19 38 21 116 4 23

I've tried to do it Crystal and even moved the data to excel...Thank you in advance for your help!!

select resourceid, ApptStart,datename(m,ApptStart) as daymonth,datename(dd,ApptStart) as day,
datename(yy,ApptStart) as year, count(AppointmentsId) As Visits,
CASE WHEN DATEPART(hour, ApptStart) BETWEEN 0 AND 12 THEN 'AM' END AS 'MORNING_SESSION',
CASE WHEN DATEPART(hour, ApptStart) BETWEEN 13 AND 17 THEN 'PM' END AS 'AFTERNOON_SESSION',
CASE WHEN DATEPART(hour, ApptStart) BETWEEN 18 AND 24 THEN 'EVE' END AS 'EVENING_SESSION'

from appointments
where Resourceid = '50' and
datename(m,ApptStart) = 'April' and datename(yy,ApptStart) = '2014'
and status in ('Arrived','Completed')
group by resourceid, ApptStart,datename(m,ApptStart),datename(dd,ApptStart),datename(yy,ApptStart)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources