Create a time series

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!"joshsackett" <joshsackett@.gmail.com> wrote in message
news:1118852141.669360.188390@.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|||That kicked ass.

Exactly what I needed... thanks!sql

CR Chart add 2 or more series ...

I am trying to graph fuel miles per gallon by vehicle by date.

My data looks like this:
Vehicle MilesPerGallon Date
1 25.6 9-1-04
1 27 9-7-04
2 22 9-2-04
2 25 9-8-04

I would like a line by vehicle, so like vehicle 1 would be a blue line and the y axis would have Miles Per Gallon, the x axis would have date and it would have a dot at 9-1-04 and 25.6, 9-7-04 and 27. Then vehicle 2 would be a red line with dots at 9-2-04 and 22, 9-8-04 and 25.

I am using CR with .NET (basically CR v10 ... I think) and don't understand how to accomplish this. Do you have any advice?

Thanks ... ChadDid you ever find the solution to your problem?? I am having the same problem and would appreciate any help you could give me.

Thanks|||Nope, I gave up and only showed Miles Per Gallon for one vehicle at at time.

Sorry ...