How do I take the day of the year and "bucket" for several weeks in Microsoft SQL? Used in production scenarios for material requirements
I need to create a Gross Requirements report that takes into account the supply and demand for an item in inventory from start date onwards, and buckets of it in different weeks of the year so that material planners know when they will need an item and if this time they will have enough stocks in their inventory.
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 that the key date falls on. In this case, Monday will be 8/25/08. This becomes the first day of the first bucket. All transactions that fall before this are assigned to week # 0 and will be summarized as the opening balance for the report. The rest of the buckets are calculated from this point. There is no end date for the 8th bucket, so any transactions after that 8th bucket start date are counted as 8 weeks.
WEEK # START DATE END DATE
0 ....... Without .......... 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 week #, start date, end date for a given date?
source to share
You can get Monday for any date in the week like:
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 transmit
@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
source to share
I have always found it easiest and most efficient (for SQL Server) to create a table with one row every week in the future across your domain horizon; and join this (with "WHERE GETDATE ()> = MONDATE AND NOT EXISTS (SELECT 1 FROM table WHERE MONDATE <GETDATE ())."
Anything you are trying to do with UDF will be much less efficient and will be more difficult for me to use.
source to share
- SQL sets the first day of the week to be Sunday and for our purposes we want it to be Monday.
- This team does it.
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
source to share
The problem I see with one bucket at a time is that it is difficult to make it scaled,
If you join a custom function you will get better performance, you can use this starting point
source to share
Why not use a combination of DATEPART (year, date-column) and DATEPART (week, date-column) and a group by those values. This works if the week in DATEPART is aligned to Mondays as required by ISO 8601. In the schema:
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...
source to share