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 intDeclare @myMinDate datetimeInsert @myTableSelect '1-1-2009',8 Union allSelect '1-3-2009',10 Union allSelect '1-8-2009',7 Union allSelect '1-9-2009',15 Union allSelect '1-10-2009',16 Union allSelect '1-11-2009',1Select * from @myTable--To Get first date and last date of timeframe you wantSelect Min(date) as minDate,max(date) as MaxDate from @myTable--Using the First and last dates, figure out how many 3 day intervals you needSelect 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 intervalsSet @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) - 1Set @myMinDate = (Select Min(date) as minDate from @myTable)Select @IntervalsSelect @myMinDateDECLARE @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 dayWhile @Intervals >= 0 BeginInsert @myDateRangesSelect Dateadd(D,3 * @Intervals,@myMinDate)Set @Intervals = @intervals - 1End Select * from @myDateRangesSelect 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