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

Posted on 2010-01-07
Medium Priority
Last Modified: 2012-05-08
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:


8-9am, 1
9-10am, 2
10-11am, 2
11am-12pm, 2
12pm-1pm, 1
1pm-2pm, 1
2pm-3pm, 1
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.
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

Open in new window


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
    insert into @x (x) values (@counter)
    set @counter = @counter + 1

--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
where dateadd(hh,x,begindate) < enddate
group by dateadd(hh,x,begindate), dateadd(hh,x+1,begindate)

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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…

862 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