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 table to then query the weeks. I have hard coded the report_date and its hould display more than one record.
any ideas
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 1
ELSE IF @WEEK_BEGINING = 'TUESDAY'
SET DATEFIRST 2
ELSE IF @WEEK_BEGINING = 'WEDNESDAY'
SET DATEFIRST 3
ELSE IF @WEEK_BEGINING = 'THURSDAY'
SET DATEFIRST 4
ELSE IF @WEEK_BEGINING = 'FRIDAY'
SET DATEFIRST 5
ELSE IF @WEEK_BEGINING = 'SATURDAY'
SET DATEFIRST 6
ELSE IF @WEEK_BEGINING = 'SUNDAY'
SET DATEFIRST 7
DECLARE @WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME
--GET THE WEEK START DATE
SELECT @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW, @REPORT_DATE) - 1)
--GET THE WEEK END DATE
SELECT @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)
CREATE TABLE #WeekList
(
month_date date
)
DECLARE @Interval int = 1
INSERT INTO #WeekList SELECT @WEEK_START_DATE
WHILE @Interval < 1
BEGIN
INSERT INTO #WeekList SELECT DATEADD(MONTH, - @Interval, @WEEK_START_DATE)
SET @Interval = @Interval + 1
END
SELECT
--Create the month ID code:
@WEEK_START_DATE AS Start_Week, @WEEK_END_DATE AS End_Week
FROM #WeekList
ORDER BY Start_Week DESC
DROP TABLE #WeekList
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 1
ELSE IF @WEEK_BEGINING = 'TUESDAY'
SET DATEFIRST 2
ELSE IF @WEEK_BEGINING = 'WEDNESDAY'
SET DATEFIRST 3
ELSE IF @WEEK_BEGINING = 'THURSDAY'
SET DATEFIRST 4
ELSE IF @WEEK_BEGINING = 'FRIDAY'
SET DATEFIRST 5
ELSE IF @WEEK_BEGINING = 'SATURDAY'
SET DATEFIRST 6
ELSE IF @WEEK_BEGINING = 'SUNDAY'
SET DATEFIRST 7
DECLARE @WEEK_START_DATE DATETIME, @WEEK_END_DATE DATETIME
--GET THE WEEK START DATE
SELECT @WEEK_START_DATE = @REPORT_DATE - (DATEPART(DW, @REPORT_DATE) - 1)
--GET THE WEEK END DATE
SELECT @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 @Interval int = datediff(WEEK,getdate(),@WEEK_START_DATE)+1
SELECT Start_Week=@WEEK_START_DATE
, End_Week=@WEEK_END_DATE
INTO #WeekList
WHILE @Interval <= 0
BEGIN
set @WEEK_START_DATE=DATEADD(WEEK,1,@WEEK_START_DATE)
set @WEEK_END_DATE=DATEADD(WEEK,1,@WEEK_END_DATE)
INSERT INTO #WeekList values (@WEEK_START_DATE,@WEEK_END_DATE)
SET @Interval += 1;
END
SELECT *
FROM #WeekList
ORDER BY Start_Week DESC
DROP TABLE #WeekList
Results (top 5 and bottom 5 of list):
Start_Week End_Week
----------------------- -----------------------
2012-03-12 00:00:00.000 2012-03-18 00:00:00.000
2012-03-05 00:00:00.000 2012-03-11 00:00:00.000
2012-02-27 00:00:00.000 2012-03-04 00:00:00.000
2012-02-20 00:00:00.000 2012-02-26 00:00:00.000
2012-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.000
2011-01-17 00:00:00.000 2011-01-23 00:00:00.000
2011-01-10 00:00:00.000 2011-01-16 00:00:00.000
2011-01-03 00:00:00.000 2011-01-09 00:00:00.000
2010-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.
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-15
2012-03-15
2012-03-15
2012-03-14
2012-03-14
2012-03-14
2012-03-14
2012-03-09
2012-03-09
2012-03-09
2012-03-08
2012-03-08
2012-03-08
2012-03-01
2012-03-01
2012-03-01
2012-03-01
2012-02-29
2012-02-29
2012-02-29
2012-02-29
2012-02-23
2012-02-23
2012-02-23
2012-02-22
2012-02-22
2012-02-22
2012-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
Count WeekStart WeekEnd
----- ---------- ----------
7 2012-02-20 2012-02-24
8 2012-02-27 2012-03-02
6 2012-03-05 2012-03-09
7 2012-03-12 2012-03-16
How to do
SET DATEFIRST 1
SELECT
COUNT(*) [Count],
DATEADD(DD, -(DATEPART(DW, mydate)-1), mydate) [WeekStart],
DATEADD(DD, 7-(DATEPART(DW, mydate)+2), mydate) [WeekEnd]
FROM
#myTable
WHERE
DATEPART(dw, mydate) >= 1 AND DATEPART(dw, mydate) <= 5 -- only weekdays
GROUP BY
DATEADD(DD, -(DATEPART(DW, mydate)-1), mydate),
DATEADD(DD, 7-(DATEPART(DW, mydate)+2), mydate)
ORDER BY
DATEADD(DD, -(DATEPART(DW, mydate)-1), mydate)
