Solved

How to create T-SQL by hour between a TIME_IN and TIME_OUT columns

Posted on 2010-01-07
Medium Priority
397 Views
I'm using Microsoft SQL Server 2005 and 2008.

SAY The table only has a STUDENT NAME, TIME_IN, TIME_OUT columns.

If Paul takes a class from 8am-3pm and John takes a class from 9am-noon, I want to create an HOURLY report that looks like:

HOUR, NUMBER OF STUDENTS

8-9am, 1
9-10am, 2
10-11am, 2
11am-12pm, 2
12pm-1pm, 1
1pm-2pm, 1
2pm-3pm, 1
0
Question by:mscptran
• 2

LVL 10

Expert Comment

ID: 26205864
have a look at the sample code

You may use CTE as I've done it now or you may have a table with all hours.
For simplicity I'm not checking all the hours in this snippet but you will see the mechanics.
0

LVL 10

Expert Comment

ID: 26205871
Something happened with the code. here it goes again
``````create table Students (studentId int identity, studentName varchar(32), studentIn tinyint, studentOut tinyint)

insert into Students values ('Paul', 8, 15)
insert into Students values ('John', 9, 12)

with AllHours as
(
select 6 as H
union select 7
union select 8
union select 9
union select 10
union select 11
union select 12
union select 13
union select 14
union select 15
)
select AH.H, COUNT(*) from AllHours AH
left outer join Students S on AH.H between S.studentIn and S.studentOut-1
group by AH.H
``````
0

LVL 4

Accepted Solution

jaan33 earned 2000 total points
ID: 26205941
--populate x table: Assumes date range cannot be more than 24 hrs
--this is usually a permanent table
declare @x table
(x          int)

declare @counter int
set @counter = 0

while @counter < 25
begin
insert into @x (x) values (@counter)
set @counter = @counter + 1
end

--populate/get student records
declare @base table
(studentname   varchar(50)
,begindate     datetime
,enddate       datetime)

insert into @base (studentname, begindate, enddate)
values ('Paul', '01/07/2010 08:00', '01/07/2010 15:00')
,('John', '01/07/2010 09:00', '01/07/2010 12:00')

--parse into hourly records
select dateadd(hh,x,begindate) as starttime, dateadd(hh,x+1,begindate) as endtime, count(studentname) as [number of students]
from @base b
join @x on 1 = 1
sol.jpg
0

Featured Post

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month17 days, left to enroll