Data with group of week Ranges for current and previous year

Question

I have a table that has data for current year and previous year. The individual records in this tables represent each transaction that took place on each day of the year. There can be multiple records on each date.

I am also displaying data related to this table in an SSRS dashboard report. There are two parameters @StartDate and @EndDate being passed from this report. I need to write a SQL Server Stored Procedure that accepts a @StartDate and @EndDate paramenters.

The challenge I am facing here is, Based on the end date passed from the report, I need to get previous 8 weeks of data from current year. Also, the data from previous year for the same date ranges and display like a weekly breakdown comparison on an SSRS chart.

For example, If the End date on my report is 2016-09-21, I need to get data for previous 8 week ranges i.e.(9/15-9/21,9/8-9/14,9/1-9/7 and so on up to 7/28-8/3.) I also need to get data for the same date ranges in the previous year.

I also have to display the week date ranges on the X-axis of a chart on the SSRS report side.

Please let me know if I can provide any further details from my end. Thank You.

I assume you're asking for just Tsql help and the fact that it's going to SSRS is just secondary to the question. First, if you know that you want 8 weeks of data from the end date, then it sounds like the start date is not needed. Consider though sending the start date as 8 weeks prior and then your stored procedure doesn't have to be hard coded to "go back" 8 weeks. Alternatively consider end date and number of weeks to return as input parameters. — paulbarbin
Also, I'm just reading that you need the same 8 week session from the previous year. I'd still consider one procedure that returns your data given the start and end (or end minus 8 weeks) and another procedure or just calling code that handles asking for these dates, subtracting a year and asking for the same dates (for last year) — paulbarbin
This is a dashboard report that I am working on, so the start date is a parameter that I am passing to obtain different values on the report. Of course, we are not using the start date. The 8 weeks have to be considered from the end date parameter itself. @paulbarbin — Kashyap MNVL

John Cappelletti John Cappelletti · Accepted Answer · 2016-09-21T13:58:41

IF it helps, I use a UDF to generate dynamic date ranges. A tally or even a date table would do the trick as will.

Declare @Date2 Date = GetDate()
Declare @Date1 Date = DateAdd(DAY,-49,@Date2)

Select DateR1=Cast(RetVal as Date)
      ,DateR2=Cast(DateAdd(DAY,6,RetVal) as Date) 
 From  [dbo].[udf-Create-Range-Date](@Date1,@Date2,'WK',1)
 Order By 1

Returns

DateR1      DateR2
2016-08-03  2016-08-09
2016-08-10  2016-08-16
2016-08-17  2016-08-23
2016-08-24  2016-08-30
2016-08-31  2016-09-06
2016-09-07  2016-09-13
2016-09-14  2016-09-20
2016-09-21  2016-09-27

The UDF if needed

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)
As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )
    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)
    Return
End

-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1)

EDIT - Sample via Join

Select D.DateR1
      ,D.DateR2
      ,TotalSales = sum(S.Sales)
 From  MySalesData S
 Join (
        Select DateR1=Cast(RetVal as Date)
              ,DateR2=Cast(DateAdd(DAY,6,RetVal) as Date) 
         From  [dbo].[udf-Create-Range-Date](@Date1,@Date2,'WK',1)
      ) D 
  on  S.Date Between D.DateR1 and D.DateR2

Data with group of week Ranges for current and previous year

1 Answers