something from internet

Posted - 01/04/2010 :  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