| Simon Hayes 2005-06-15, 8:24 pm |
|
"joshsackett" <joshsackett@gmail.com> wrote in message
news:1118852141.669360.188390@g44g2000cwa.googlegroups.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
|