"joshsackett" <jo*********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Given the following table information:
HOSTNAME DATETIME
WEBNYC001 2005-06-15 10:30AM
WEBNYC001 2005-06-15 10:31AM
WEBNYC001 2005-06-15 10:31AM
WEBNYC001 2005-06-15 10:34AM
WEBNYC001 2005-06-15 10:35AM
WEBNYC001 2005-06-15 10:35AM
WEBNYC002 2005-06-15 10:30AM
WEBNYC002 2005-06-15 10:30AM
WEBNYC002 2005-06-15 10:33AM
WEBNYC002 2005-06-15 10:35AM
WEBNYC002 2005-06-15 10:35AM
WEBNYC002 2005-06-15 10:35AM
How can I easily return the following results:
HOSTNAME DATETIME COUNT
WEBNYC001 2005-06-15 10:30AM 1
WEBNYC001 2005-06-15 10:31AM 2
WEBNYC001 2005-06-15 10:32AM 0
WEBNYC001 2005-06-15 10:33AM 0
WEBNYC001 2005-06-15 10:34AM 1
WEBNYC001 2005-06-15 10:35AM 2
WEBNYC002 2005-06-15 10:30AM 2
WEBNYC002 2005-06-15 10:31AM 0
WEBNYC002 2005-06-15 10:32AM 0
WEBNYC002 2005-06-15 10:33AM 1
WEBNYC002 2005-06-15 10:34AM 0
WEBNYC002 2005-06-15 10:35AM 3
Thanks!
Here's one possible solution. In general, queries involving ranges of times,
dates or numbers are often easier if you have an auxiliary table to join on.
If you don't want to implement such a table, you could create a table-valued
function which returns all required values between two given datetimes
instead - that would avoid having a potentially very large table in the
database.
Simon
create table dbo.Data (
host char(9) not null,
dt datetime not null
/* need a primary key here */
)
go
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:30AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:31AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:31AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:34AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC001', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:30AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:30AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:33AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:35AM'
insert into dbo.Data select 'WEBNYC002', '2005-06-15 10:35AM'
go
create table dbo.Times (
tm datetime not null primary key
)
go
insert into dbo.Times select '2005-06-15 10:30AM'
insert into dbo.Times select '2005-06-15 10:31AM'
insert into dbo.Times select '2005-06-15 10:32AM'
insert into dbo.Times select '2005-06-15 10:33AM'
insert into dbo.Times select '2005-06-15 10:34AM'
insert into dbo.Times select '2005-06-15 10:35AM'
go
select
h.host,
t.tm,
coalesce(a.cnt, 0)
from
dbo.Times t
cross join (select distinct host from dbo.Data) h
left outer join
(
select
host,
dt,
count(*) as 'cnt'
from
dbo.Data
group by
host,
dt
) a
on t.tm = a.dt
and h.host = a.host
order by
h.host,
t.tm
go
drop table dbo.Data
drop table dbo.Times
go
