Datetimes, groupings and count query

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 2000 Forums

SQL Server Development (2000)

Datetimes, groupings and count query

Author

Topic

ciggs
Starting Member

8 Posts

Posted - 2003-03-24 : 09:51:35

Hi all,
I've got a table with the basic setup below

LabelID varchar(50)
date_scan datetime

Data example
LabelID date_scan
Y45768 23/07/2003 12:34:40
Y45769 23/07/2003 12:45:32
Y45770 24/07/2003 10:09:30
Y45771 24/07/2003 10:34:21

What I want to do is set up a query with a parameter of 2 dates - I want the data to display the range of hours between those 2 dates counting how many labels have been scanned in those hours between those dates - if nothing has been scanned I want it to return a zero.
This means if the data above is used a the dates input are 22/07/2003 and 24/07/2003 the query will return 24 zeros for the hours of the 22nd, 23 zeros and a count of 2 for the 23rd and 23 zeros and a count of 2 for the 24th.
Anyone got any ideas for the easiest implementation - do I need to use a temporary table or a cursor. How do I get it to populate back hours for which there is no data. Any help appreciated.
Cheers,
ciggs

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-24 : 10:11:57

The best way to do this is with a tally table. A tally table is a table of all integers from 1 to whatever(I use 10,000). I keep my tally table in a database called ToolBox and call the table numbers. It has one column "n".


drop table #cigs
go
create table #cigs (
    labelid varchar(50),
    date_scan datetime )

insert into #cigs
select 'Y45768', '07/23/2003 12:34:40' 
union select 'Y45769','07/23/2003 12:45:32'
union select 'Y45770','07/23/2003 10:09:30'
union select 'Y45771','07/23/2003 10:34:21'
go

declare @start datetime, @end datetime
select @start = '07/23/2003', @end = '07/24/2003'
select
    dateadd(hh,n-2,@start) as 'Start of time period',
    count(distinct labelid) as 'Count of labelid'
from
    toolbox.dbo.numbers n
    left join #cigs c
        on c.date_scan >= dateadd(hh,n-2,@start) and
           c.date_scan < dateadd(hh,n,@start)
where
    n <= datediff(hh,@start,@end) and
    n % 2 = 0
group by
    dateadd(hh,n-2,@start)
order by
    dateadd(hh,n-2,@start)
go

Jay White
{0}

ciggs
Starting Member

8 Posts

Posted - 2003-03-24 : 11:22:37

Cheers Jay,

Thats exactly what I was looking for and done using a method I doubt I would ever have thought of (mine would have involved about 100 lines of procedure code, cursors and temporary tables)

Thanks a lot

ciggs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources