Displaying Reporting Weeks

I dont know how to loop through so that the query displays all the weeks from the reporting date? The code determines the Mon - Sun week then should insert the values in a temp tab

Solution 1:

Using DATEADD and updating your @interval initialization and updated loop logic:

DECLARE@REPORT_DATE DATETIME, @WEEK_BEGINING VARCHAR(10)
SELECT@REPORT_DATE ='2011-01-01T00:00:00'--SELECT @REPORT_DATE = GETDATE() -- should grab the date now.SELECT@WEEK_BEGINING ='MONDAY'
IF @WEEK_BEGINING ='MONDAY'SET DATEFIRST 1ELSE IF @WEEK_BEGINING ='TUESDAY'SET DATEFIRST 2ELSE IF @WEEK_BEGINING ='WEDNESDAY'SET DATEFIRST 3ELSE IF @WEEK_BEGINING ='THURSDAY'SET DATEFIRST 4ELSE IF @WEEK_BEGINING ='FRIDAY'SET DATEFIRST 5ELSE IF @WEEK_BEGINING ='SATURDAY'SET DATEFIRST 6ELSE IF @WEEK_BEGINING ='SUNDAY'SET DATEFIRST 7DECLARE@WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME
--GET THE WEEK START DATESELECT@WEEK_START_DATE =@REPORT_DATE - (DATEPART(DW, @REPORT_DATE) -1)
--GET THE WEEK END DATESELECT@WEEK_END_DATE =@REPORT_DATE + (7- DATEPART(DW, @REPORT_DATE))
PRINT 'Week Start: '+CONVERT(VARCHAR, @WEEK_START_DATE)
PRINT 'Week End: '+CONVERT(VARCHAR, @WEEK_END_DATE)

DECLARE@Intervalint= datediff(WEEK,getdate(),@WEEK_START_DATE)+1SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE
INTO #WeekList

WHILE @Interval<=0BEGINset@WEEK_START_DATE=DATEADD(WEEK,1,@WEEK_START_DATE)
    set@WEEK_END_DATE=DATEADD(WEEK,1,@WEEK_END_DATE)
    INSERTINTO #WeekList values (@WEEK_START_DATE,@WEEK_END_DATE)
    SET@Interval+=1;
    ENDSELECT*FROM #WeekList
ORDERBY Start_Week DESCDROPTABLE #WeekList

Results (top 5 and bottom 5 of list):

Start_WeekEnd_Week----------------------------------------------2012-03-12 00:00:00.000 2012-03-18 00:00:00.0002012-03-05 00:00:00.000 2012-03-11 00:00:00.0002012-02-27 00:00:00.000 2012-03-04 00:00:00.0002012-02-20 00:00:00.000 2012-02-26 00:00:00.0002012-02-13 00:00:00.000 2012-02-19 00:00:00.000...2011-01-24 00:00:00.000 2011-01-30 00:00:00.0002011-01-17 00:00:00.000 2011-01-23 00:00:00.0002011-01-10 00:00:00.000 2011-01-16 00:00:00.0002011-01-03 00:00:00.000 2011-01-09 00:00:00.0002010-12-27 00:00:00.000 2011-01-02 00:00:00.000

As an aside, you could also use the date type instead of Datetime, if you don't need to store the time.

Solution 2:

If you would like to list the weeks and the corresponding counts or sums, you can do this as follows

Sample data

myDate----------2012-03-152012-03-152012-03-152012-03-142012-03-142012-03-142012-03-142012-03-092012-03-092012-03-092012-03-082012-03-082012-03-082012-03-012012-03-012012-03-012012-03-012012-02-292012-02-292012-02-292012-02-292012-02-232012-02-232012-02-232012-02-222012-02-222012-02-222012-02-22

Script for sample data

create table#myTable(mydate Date)

insert into #myTable select DATEADD(day, -1, getdate())
insert into #myTable select DATEADD(day, -1, getdate())
insert into #myTable select DATEADD(day, -1, getdate())
insert into #myTable select DATEADD(day, -2, getdate())
insert into #myTable select DATEADD(day, -2, getdate())
insert into #myTable select DATEADD(day, -2, getdate())
insert into #myTable select DATEADD(day, -2, getdate())
insert into #myTable select DATEADD(day, -7, getdate())
insert into #myTable select DATEADD(day, -7, getdate())
insert into #myTable select DATEADD(day, -7, getdate())
insert into #myTable select DATEADD(day, -8, getdate())
insert into #myTable select DATEADD(day, -8, getdate())
insert into #myTable select DATEADD(day, -8, getdate())
insert into #myTable select DATEADD(day, -15, getdate())
insert into #myTable select DATEADD(day, -15, getdate())
insert into #myTable select DATEADD(day, -15, getdate())
insert into #myTable select DATEADD(day, -15, getdate())
insert into #myTable select DATEADD(day, -16, getdate())
insert into #myTable select DATEADD(day, -16, getdate())
insert into #myTable select DATEADD(day, -16, getdate())
insert into #myTable select DATEADD(day, -16, getdate())
insert into #myTable select DATEADD(day, -22, getdate())
insert into #myTable select DATEADD(day, -22, getdate())
insert into #myTable select DATEADD(day, -22, getdate())
insert into #myTable select DATEADD(day, -23, getdate())
insert into #myTable select DATEADD(day, -23, getdate())
insert into #myTable select DATEADD(day, -23, getdate())
insert into #myTable select DATEADD(day, -23, getdate())

Expected Result

CountWeekStartWeekEnd-------------------------72012-02-20  2012-02-2482012-02-27  2012-03-0262012-03-05  2012-03-0972012-03-12  2012-03-16

How to do

SET DATEFIRST 1SELECTCOUNT(*) [Count],
    DATEADD(DD, -(DATEPART(DW, mydate)-1), mydate) [WeekStart],
    DATEADD(DD, 7-(DATEPART(DW, mydate)+2), mydate) [WeekEnd]
FROM
    #myTable
WHERE 
    DATEPART(dw, mydate) >=1AND DATEPART(dw, mydate) <=5-- only weekdaysGROUPBY
    DATEADD(DD, -(DATEPART(DW, mydate)-1), mydate),
    DATEADD(DD, 7-(DATEPART(DW, mydate)+2), mydate)
ORDERBY
    DATEADD(DD, -(DATEPART(DW, mydate)-1), mydate)