How do I take a day of the year and 'bucket it' into weeks of the year in Microsoft SQL? Used in manufacturing scenarios for material requirements

Question

I have a need to create a gross requirements report that takes how much supply and demand of a item in inventory from a start date onwards and 'buckets' it into different weeks of the year so that material planners know when they will need a item and if they have enough stock in inventory at that time.

As an example, today’s date (report date) is 8/27/08. The first step is to find the date for the Monday of the week the report date falls in. In this case, Monday would be 8/25/08. This becomes the first day of the first bucket. All transactions that fall before that are assigned to week #0 and will be summarized as the beginning balance for the report. The remaining buckets are calculated from that point. For the eighth bucket, there is no ending date so any transactions after that 8th bucket start date are considered week #8.

WEEK# START DATE END DATE
0.......None..........8/24/08
1.......8/25/08.......8/31/08
2.......9/1/08.........9/7/08
3.......9/8/08.........9/14/08
4.......9/15/08.......9/21/08
5.......9/22/08.......9/28/08
6.......9/29/08.......10/5/08
7.......10/06/08.....10/12/08
8.......10/13/08......None

How do I get the week #, start date, end date for a given date?

Answer 1

--SQL sets the first day of the week as sunday and for our purposes we want it to be Monday.
--This command does that.

SET DATEFIRST 1

DECLARE 
    @ReportDate DATETIME, 

    @Weekday INTEGER, 
    @NumDaysToMonday INTEGER, 
    @MondayStartPoint DATETIME,
    @MondayStartPointWeek INTEGER,
    @DateToProcess DATETIME,
    @DateToProcessWeek INTEGER,
    @Bucket VARCHAR(50),
    @DaysDifference INTEGER,
    @BucketNumber INTEGER,
    @NumDaysToMondayOfDateToProcess INTEGER,
    @WeekdayOfDateToProcess INTEGER,
    @MondayOfDateToProcess DATETIME,
    @SundayOfDateToProcess DATETIME

SET @ReportDate = '2009-01-01'
print @ReportDate

SET @DateToProcess = '2009-01-26'
--print @DateToProcess

SET @Weekday = (select DATEPART ( dw , @ReportDate ))
--print @Weekday

--print DATENAME(dw, @ReportDate)

SET @NumDaysToMonday = 
    (SELECT
      CASE 
         WHEN @Weekday =  1 THEN 0
         WHEN @Weekday =  2 THEN 1
         WHEN @Weekday =  3 THEN 2
         WHEN @Weekday =  4 THEN 3
         WHEN @Weekday =  5 THEN 4
         WHEN @Weekday =  6 THEN 5
         WHEN @Weekday =  7 THEN 6
      END)

--print @NumDaysToMonday

SET @MondayStartPoint =  (SELECT DATEADD (d , -1*@NumDaysToMonday, @ReportDate))
--print @MondayStartPoint

SET @DaysDifference = DATEDIFF ( dd , @MondayStartPoint , @DateToProcess )
--PRINT @DaysDifference

SET @BucketNumber = @DaysDifference/7
--print @BucketNumber

----Calculate the start and end dates of this bucket------
PRINT 'Start Of New Calc'

print @DateToProcess

SET @WeekdayOfDateToProcess = (select DATEPART ( dw , @DateToProcess ))
print @WeekdayOfDateToProcess

SET @NumDaysToMondayOfDateToProcess= 
    (SELECT
      CASE 
         WHEN @WeekdayOfDateToProcess =  1 THEN 0
         WHEN @WeekdayOfDateToProcess =  2 THEN 1
         WHEN @WeekdayOfDateToProcess =  3 THEN 2
         WHEN @WeekdayOfDateToProcess =  4 THEN 3
         WHEN @WeekdayOfDateToProcess =  5 THEN 4
         WHEN @WeekdayOfDateToProcess =  6 THEN 5
         WHEN @WeekdayOfDateToProcess =  7 THEN 6
      END)

print @NumDaysToMondayOfDateToProcess
SET @MondayOfDateToProcess =  (SELECT DATEADD (d , -1*@NumDaysToMondayOfDateToProcess, @DateToProcess))
print @MondayOfDateToProcess   ---This is the start week

SET @SundayOfDateToProcess = (SELECT DATEADD (d , 6, @MondayOfDateToProcess))
PRINT @SundayOfDateToProcess

Answer 2

The problem I see with the one bucket at a time approach is that its hard to make it scale,

If you join into a user defined function you will get better performance, you could use this a a starting point

Answer 3

I've always found it easiest and most efficient (for SQL Server) to construct a table with one row for every week into the future through your domain horizon; and join to that (with a "WHERE GETDATE() >= MONDATE AND NOT EXISTS (SELECT 1 FROM table WHERE MONDATE < GETDATE())".

Anything you try to do with UDF's will be much less efficient and I find more difficult to use.

Answer 4

You can get Monday for any given date in a week as:

DATEADD(d, 1 - DATEPART(dw, @date), @date)

and you can write a stored procedure with the following body

-- find Monday at that week
DECLARE @currentDate SMALLDATETIME
SELECT @currentDate = DATEADD(d, 1 - DATEPART(dw, @date), @date)

-- create a table and insert the first record
DECLARE @weekTable TABLE (Id INT, StartDate SMALLDATETIME, EndDate SMALLDATETIME)
INSERT INTO @weekTable VALUES (0, NULL, @currentDate)

-- increment the date
SELECT @currentDate = DATEADD(d, 1, @currentDate)

-- iterate for 7 more weeks
DECLARE @id INT
SET @id = 1
WHILE @id < 8
BEGIN
    INSERT INTO @weekTable VALUES (@id, @currentDate, DATEADD(d, 6, @currentDate))
    SELECT @currentDate = DATEADD(ww, 1, @currentDate)
    SET @id = @id + 1
END

-- add the last record
INSERT INTO @weekTable VALUES (8, @currentDate, NULL)

-- select the values
SELECT Id 'Week #', StartDate 'Start Date', EndDate 'End Date'
FROM @weekTable

When I pass

@date = '20080827'

to this procedure, I get the following

Week #  Start Date    End Date
0   NULL             2008-08-24 00:00:00
1   2008-08-25 00:00:00 2008-08-31 00:00:00
2   2008-09-01 00:00:00 2008-09-07 00:00:00
3   2008-09-08 00:00:00 2008-09-14 00:00:00
4   2008-09-15 00:00:00 2008-09-21 00:00:00
5   2008-09-22 00:00:00 2008-09-28 00:00:00
6   2008-09-29 00:00:00 2008-10-05 00:00:00
7   2008-10-06 00:00:00 2008-10-12 00:00:00
8   2008-10-13 00:00:00 NULL

Answer 5

Why not use a combination of DATEPART(year, date-column) and DATEPART(week, date-column) and group by these values. This works if the week in DATEPART is aligned on Mondays as ISO 8601 requires. In outline:

SELECT DATEPART(year, date_column) AS yyyy,
       DATEPART(week, date_column) AS ww,
       ...other material as required...
    FROM SomeTableOrOther
    WHERE ...appropriate filters...
    GROUP BY yyyy, ww -- ...and other columns as necessary...