Counting Entries by 24 Hour Period

microsoft.public.sqlserver.programming

19 Oct 2006 8:06 PM

randy1200

I have a table that includes a DateTime column. I need to create a select
statement that displays the number of entries for each period:

Period                      Count
01 Oct 06 + 4             7
02 Oct 06 + 4             10
03 Oct 06 + 4             10

If the 24 hour period began at midnight, this would be easy. My database is
stored in UTC times, and my periods begin at 4:00 am, and end at 3:59 the
following morning.

Below is some code I've tried to implement and test, in case that's useful.
Thanks,

--
Randy

declare @UtcStartTime DateTime
set @UtcStartTime = '10/1/2006 4:00'
declare @UtcEndTime DateTime
set @UtcEndTime = '10/5/2006 3:59:59'
select CONVERT(varchar(30), TheDate, 6) as 'Date',
           count(TheDate) as 'Count'
from test
where TheDate is not null
        and TheDate between @UtcStartTime and @UtcEndTime
group by CONVERT(varchar(30), TheDate, 6), year(TheDate),month(TheDate),
day(TheDate)
    order by year(TheDate), month(TheDate), day(TheDate)

--create table with constraint
create table dbo.test
    (id int not null
    Constraint PK_id Primary Key Nonclustered (id),
    TheDate DateTime null)

--add values
insert into test values(1,'10/1/2006 1:00')
insert into test values(2,'10/1/2006 2:00')
insert into test values(3,'10/1/2006 3:00')
insert into test values(4,'10/1/2006 4:00')
insert into test values(5,'10/1/2006 5:00')
insert into test values(6,'10/1/2006 6:00')
insert into test values(7,'10/1/2006 7:00')
insert into test values(8,'10/1/2006 8:00')
insert into test values(9,'10/1/2006 9:00')
insert into test values(10,'10/1/2006 10:00')

insert into test values(11,'10/2/2006 1:00')
insert into test values(12,'10/2/2006 2:00')
insert into test values(13,'10/2/2006 3:00')
insert into test values(14,'10/2/2006 4:00')
insert into test values(15,'10/2/2006 5:00')
insert into test values(16,'10/2/2006 6:00')
insert into test values(17,'10/2/2006 7:00')
insert into test values(18,'10/2/2006 8:00')
insert into test values(19,'10/2/2006 9:00')
insert into test values(20,'10/2/2006 10:00')

insert into test values(21,'10/3/2006 1:00')
insert into test values(22,'10/3/2006 2:00')
insert into test values(23,'10/3/2006 3:00')
insert into test values(24,'10/3/2006 4:00')
insert into test values(25,'10/3/2006 5:00')
insert into test values(26,'10/3/2006 6:00')
insert into test values(27,'10/3/2006 7:00')
insert into test values(28,'10/3/2006 8:00')
insert into test values(29,'10/3/2006 9:00')
insert into test values(30,'10/3/2006 10:00')

insert into test values(31,'10/4/2006 1:00')
insert into test values(32,'10/4/2006 2:00')
insert into test values(33,'10/4/2006 3:00')
insert into test values(34,'10/4/2006 4:00')
insert into test values(35,'10/4/2006 5:00')
insert into test values(36,'10/4/2006 6:00')
insert into test values(37,'10/4/2006 7:00')
insert into test values(38,'10/4/2006 8:00')
insert into test values(39,'10/4/2006 9:00')
insert into test values(30,'10/4/2006 10:00')

--review and drop
select * from test
drop table test