Script: Chunk dates for reporting

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

General SQL Server Forums

Script Library

Script: Chunk dates for reporting

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-04-05 : 14:16:48

Here's a handy function that I use for reporting; it lets you specify a start date and number of days per "chunk". Use it in a group by to easily have the same report function for per-day, per-week, per-month, etc.

For example,

select dbo.f_util_DateChunk('2003-01-01',7,sales_date),sum(sales_price)
from sales
group by dbo.f_util_DateChunk('2003-01-01',7,sales_date)
order by dbo.f_util_DateChunk('2003-01-01',7,sales_date) asc

will give weekly sales totals. Replace the "7" in both DateChunk functions with "30" and use the same report to get monthly totals. Makes it easy to parameterize reports.

Cheers
-b

CREATE FUNCTION dbo.f_util_DateChunk (@dStartDate datetime,@iInterval int,@dDate datetime)
RETURNS datetime AS  
BEGIN 
DECLARE @iDays int

select @dStartDate=dbo.f_util_DateOnly(@dStartDate)
select @dDate=dbo.f_frnk_util_DateOnly(@dDate)

select @iDays=datediff(day,@dStartDate,@dDate)
select @iDays=@iDays/@iInterval

select @dDate=dateadd(day,@iDays*@iInterval,@dStartDate)

return @dDate
END

CREATE FUNCTION dbo.f_util_DateOnly  (@dDate as datetime)  
RETURNS datetime  AS  
BEGIN 
DECLARE @d datetime
select @d=convert(datetime,convert(varchar(10),@dDate,101))
return @d
END

Edited by - aiken on 04/05/2003 14:17:47

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources