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 2005 Forums
 Transact-SQL (2005)
 How to generate a report like this?

Author  Topic 

jimmyblues
Starting Member

1 Post

Posted - 2010-01-04 : 10:41:12
Here I have a talbe called "contract". Everyday users create contract data, and data insert into this table. Here if I generate a report by days, it would be like this:
Date Contract Quantity
----------------------------------------
2009-12-01 450
2009-12-02 340
2009-12-03 355
2009-12-04 343
2009-12-05 431

... ...



If I need to generate a report with 3 params: StartDate, EndDate, Interval, the interval means the Contract Quantity will be aggregated from startdate to enddate group by interval, like this:


Date Contract Quantity(Every 3 days)
----------------------------------------
2009-12-01 1250
2009-12-04 1140
2009-12-07 1055
2009-12-10 1143
2009-12-13 1231

Then, how can I write sql to generate such a report?




DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-04 : 10:49:28
How do you want to pick when the first date is? (is it arbitrary, or does it depend on the 1st day of the month, or is it the minimum day in your data?) And also how far do you want to sum up, will there always be data in the 3 day increments.
Go to Top of Page

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-04 : 11:30:45
Some may not like the means of getting this, and many will have shortcuts and best practices to get your answer, but it works. There are better ways, for one the intervals table can be generated better I think by recursion, but this will work.

DECLARE @myTable TABLE (date datetime, Qty Int)
Declare @intervals int
Declare @myMinDate datetime

Insert @myTable
Select '1-1-2009',8 Union all
Select '1-3-2009',10 Union all
Select '1-8-2009',7 Union all
Select '1-9-2009',15 Union all
Select '1-10-2009',16 Union all
Select '1-11-2009',1


Select * from @myTable

--To Get first date and last date of timeframe you want
Select Min(date) as minDate,max(date) as MaxDate from @myTable

--Using the First and last dates, figure out how many 3 day intervals you need
Select Datediff(d, t.minDate, t.maxDate) as DiffofDays, Ceiling(cast((Datediff(d, t.minDate, t.maxDate)) as float)/3) as IntervalsNeeded from (Select Min(date) as minDate,max(date) as maxDate from @myTable) t

--Set intervals
Set @Intervals = (Select Ceiling(cast((Datediff(d, t.minDate, t.maxDate)) as float)/3) as IntervalsNeeded from (Select Min(date) as minDate,max(date) as maxDate from @myTable) t) - 1
Set @myMinDate = (Select Min(date) as minDate from @myTable)

Select @Intervals
Select @myMinDate

DECLARE @myDateRanges TABLE (date datetime)

--insert the beggining date and then every 3 days after that for as many intervals as required, to set a known date change Set @myDate to a specific day
While @Intervals >= 0
Begin
Insert @myDateRanges
Select Dateadd(D,3 * @Intervals,@myMinDate)
Set @Intervals = @intervals - 1
End

Select * from @myDateRanges

Select Convert(varchar(10), b.date ,120) as Date, ISNULL(Sum(case when a.date between b.date and Dateadd(D,2,b.date) then a.qty end),0) as sumQty From @myTable a cross apply @myDateRanges b group by b.date



Edit: To get date output exactly as user supplied
Go to Top of Page
   

- Advertisement -