GROUP BY a time range - 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

SQL Server 2005 Forums

Transact-SQL (2005)

GROUP BY a time range

Author

Topic

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-08-05 : 08:07:28

Hi,
Please help me to construct a SQL statement, where I wish to count the records in Table A, group by the time range as defined in Table B.
The results should be like :

No,StartTime,End Time,Available
1,1899-12-30 10:00:00.000,1899-12-30 10:29:59.000,3 (6 minus 3)
2,1899-12-30 10:30:00.000,1899-12-30 10:59:59.000,5 (6 minus 1)

Thanks.

Table A (Transaction table)
TransactionDate
1899-12-30 10:00:00.000, (falls into the 10:00 - 10:29:59 group)
1899-12-30 10:20:00.000, (falls into the 10:00 - 10:29:59 group)
1899-12-30 10:10:00.000, (falls into the 10:00 - 10:29:59 group)
1899-12-30 10:40:00.000, (falls into the 10:30 - 10:59:59 group)

Table B (Range Definition table)
No,StartTime,End Time,MaxAvailable
1,1899-12-30 10:00:00.000,1899-12-30 10:29:59.000,6
2,1899-12-30 10:30:00.000,1899-12-30 10:59:59.000,6
3,1899-12-30 11:00:00.000,1899-12-30 11:29:59.000,6
4,1899-12-30 11:30:00.000,1899-12-30 11:59:59.000,6
5,1899-12-30 12:00:00.000,1899-12-30 12:29:59.000,6
6,1899-12-30 12:30:00.000,1899-12-30 12:59:59.000,6
7,1899-12-30 13:00:00.000,1899-12-30 13:29:59.000,6
8,1899-12-30 13:30:00.000,1899-12-30 13:59:59.000,6
9,1899-12-30 14:00:00.000,1899-12-30 14:29:59.000,6
10,1899-12-30 14:30:00.000,1899-12-30 14:59:59.000,6
11,1899-12-30 15:00:00.000,1899-12-30 15:29:59.000,6

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-05 : 08:18:08

Assuming your dates are DATETIME datatype:

select b.No, b.StartTime, b.EndTime, b.MaxAvailable - count(*) as Available
from TableA a
inner join TableB b on b.StartTime <= a.TransactionDate and b.EndTime > a.TransactionDate
group by b.No, b.StartTime, b.EndTime, b.MaxAvailable

Be One with the Optimizer
TG

liangtp@hotmail.com
Starting Member

27 Posts

Posted - 2008-08-05 : 08:56:31

Thanks TG.
It worked.
Your're the man!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources