Another Way - SQL Server Forums

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

Transact-SQL (2000)

Another Way

Author

Topic

filipinoheat
Starting Member

8 Posts

Posted - 2006-07-18 : 11:42:47

My manager wanted a report that will display the counts of transactions per hour. The date ranges from 01/01/06 to 06/15/06.

I figured it out and everything but I'm just curious on how someone else would write it, in a more proper way. Any feed backs on how I should of approached it would be great, here is my code:

/*******************************************************************

Tables:

1)work_table - Holds all transactions for that specific date.
2)cashworks - Holds all the transactions.

*******************************************************************/

--TRIGGER CREATION
create trigger delete_trigger on work_table
after delete
as
declare @count int, @start datetime,@end datetime

select @start = min(transdate),@end = max(transdate),@count = count(transdate)
from deleted

insert into result_table (start_time,end_time,total)
values (@start,@end,@count)

--PROCEDURE
create procedure get_transaction_per_hour
(
@parameter varchar(50)
)
as
insert into work_table
select transdate
from cashworks
where cast(floor(cast(transdate as float)) as datetime) = @parameter
order by transdate

while exists (select * from work_table)
begin

declare @search_by datetime, @hour_ahead datetime

set @search_by = (select min(transdate) from work_table)
set @hour_ahead = (select dateadd(mi,60,@search_by))

delete work_table
where transdate between @search_by and @hour_ahead

end

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-18 : 12:04:47

This will give you the total for each hour over that time range for the hours that have transactions.


select
	DATE_HOUR = dateadd(hh,datediff(hh,0,transdate),0),
	TRANSACTION_COUNT = count(*)
from
	cashworks
where
`	transdate >= '20060101' and
`	transdate  < '20060616'
group by
	dateadd(hh,datediff(hh,0,transdate),0)
order by
	dateadd(hh,datediff(hh,0,transdate),0)

CODO ERGO SUM

filipinoheat
Starting Member

8 Posts

Posted - 2006-07-18 : 15:12:02

Thanks for the response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources