Apply week number to dates for whole weeks only

Question

The time is: (m/d/yyyy) => 2009/01/04

Using this command using datepart(wk,'20090104') I can get the week number (for any given date).

So :

SELECT datepart(wk,'20090101') //1
SELECT datepart(wk,'20090102') //1
SELECT datepart(wk,'20090103') //1
SELECT datepart(wk,'20090104') //2

So far so good.

The problem :

Those 3 first dates are not part of a full week, so I can't put them in a fixed 52-week chart.

Our company needs to see information about each whole week in the 52 weeks of a year. (Each year has 52 whole weeks).

So 20090101 doesn't belong to the first week of 2009 !

It belongs to the previous year (which is irrelevant to my question)

So I need a UDF (I've been searching a lot, and ISOWEEK is not answering my needs) which by a given datetime, will give me the Week Number (week = whole week, so partial weeks aren't considered).

Example :

calcweekNum ('20090101') //52 ...from the last year
calcweekNum ('20090102') //52 ...from the last year
calcweekNum ('20090103') //52 ...from the last year
calcweekNum ('20090104') //1
..
..
calcweekNum ('20090110') //1
calcweekNum ('20090111') //2
calcweekNum ('20090112') //2
...

Answer 1

Here's a function for you to calculate it on the fly:

CREATE FUNCTION dbo.WholeWeekFromDate (
   @Date datetime
)
RETURNS tinyint
AS BEGIN
RETURN (
   SELECT DateDiff(Day, DateAdd(Year, DateDiff(Year, 0, CalcDate), 0), CalcDate) / 7 + 1
   FROM (SELECT DateAdd(Day, (DateDiff(Day, 0, @Date) + 1) / 7 * 7, 0)) X (CalcDate)
);
END;

I don't recommend you use it, as it may perform badly due to being called once for every row. If you absolutely must have a function to use in real queries, then convert it to an inline function returning a single column and row, and use it as so:

SELECT
   OtherColumns,
   (SELECT WeekNumber FROM dbo.WholeWeekFromDate(DateColumn)) WeekNumber
FROM
   YourTable;

This will allow it to be "inlined" in the execution plan and perform significantly better.

But even better, as others have suggested, is to use a BusinessDate table. Here's a head start on creating one for you:

CREATE TABLE dbo.BusinessDate (
   BusinessDate date NOT NULL CONSTRAINT PK_BusinessDate PRIMARY KEY CLUSTERED,
   WholeWeekYear smallint NOT NULL
      CONSTRAINT CK_BusinessDate_WholeWeekYear_Valid
      CHECK (WholeWeekYear BETWEEN 1900 AND 9999),
   WholeWeekNumber tinyint NOT NULL
      CONSTRAINT CK_BusinessDate_WholeWeekNumber_Valid
      CHECK (WholeWeekNumber BETWEEN 1 AND 53),
   Holiday bit CONSTRAINT DF_BusinessDate_Holiday DEFAULT (0),
   Weekend bit CONSTRAINT DF_BusinessDate_Weekend DEFAULT (0),
   BusinessDay AS
      (Convert(bit, CASE WHEN Holiday = 0 AND Weekend = 0 THEN 1 ELSE 0 END)) PERSISTED
);

And I'll even populate it from 1900-01-01 through 2617-09-22 (Is that enough for the projected life of your product? And it's only 7.8MB so don't fret over size):

WITH A (N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
   UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
B (N) AS (SELECT 1 FROM A F, A A, A L, A C, A O, A N),
C (N) AS (SELECT Row_Number() OVER (ORDER BY (SELECT 1)) FROM B),
Dates AS (
   SELECT
      N,
      DateAdd(Day, N, '18991231') Dte,
      DateAdd(Day, N / 7 * 7, '19000101') CalcDate
   FROM C
)
INSERT dbo.BusinessDate
SELECT
   Dte,
   Year(CalcDate),
   DateDiff(Day, DateAdd(Year, DateDiff(Year, 0, CalcDate), 0), CalcDate) / 7 + 1,
   0,
   (N + 6) % 7 / 5 -- calculate weekends
FROM Dates; -- 3-7 seconds or so on my VM server

Then join to the table on the date, and use the WholeWeekNumber column for your output. You might also consider adding a WeekNumberYear because it's going to be a tad difficult to figure out that the 52 of 2009-01-01 really belongs to 2008 without this... a strange data point in there for sure if you don't (laugh).

Example table contents:

BusinessDate WholeWeekYear WholeWeekNumber Holiday Weekend BusinessDay
------------ ------------- --------------- ------- ------- -----------
   1/1/2009          2008              52       0       0           1 
   1/2/2009          2008              52       0       0           1 
   1/3/2009          2008              52       0       1           0 
   1/4/2009          2009               1       0       1           0 
   1/5/2009          2009               1       0       0           1 
   1/6/2009          2009               1       0       0           1 
   1/7/2009          2009               1       0       0           1 
   1/8/2009          2009               1       0       0           1 
   1/9/2009          2009               1       0       0           1 
  1/10/2009          2009               1       0       1           0 
  1/11/2009          2009               2       0       1           0 

If you really don't want to use this as a general business date calculation table, you can drop the last 3 columns, otherwise, update the Holiday column to 1 for company holidays.

Note: if you actually make the above table, and your access to it most often uses JOIN or WHERE conditions on a different column than BusinessDate, then make the primary key nonclustered and add a clustered index starting with the alternate column.

Some of the above scripts require SQL 2005 or higher.

Answer 2

DECLARE @StartDate DATE;
SET @StartDate = '20120101';

WITH Calendar AS (
    SELECT @StartDate AS DateValue
        ,DATEPART(DW, @StartDate) AS DayOfWeek
        ,CASE WHEN DATEPART(DW, @StartDate) = 1 THEN 1 ELSE 0 END AS WeekNumber
    UNION ALL
    SELECT DATEADD(d, 1, DateValue)
        ,DATEPART(DW, DATEADD(d, 1, DateValue)) AS DayOfWeek
        ,CASE WHEN DayOfWeek = 7 THEN WeekNumber + 1 ELSE WeekNumber END
    FROM Calendar 
    WHERE DATEPART(YEAR, DateValue) = DATEPART(YEAR, @StartDate)
)
SELECT DateValue, WeekNumber
FROM Calendar
WHERE WeekNumber BETWEEN 1 AND 52
    AND DATEPART(YEAR, DateValue) = DATEPART(YEAR, @StartDate)
OPTION (MAXRECURSION 0);

Answer 3

Here's a different approach. All you need to supply is the year:

DECLARE @year INT = 2009;


DECLARE @start SMALLDATETIME;
SET @start = DATEADD(YEAR, @year-1900, 0);

;WITH n AS
(
  SELECT TOP (366) -- in case of leap year
      d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1, @start)
    FROM sys.all_objects
),
x AS 
(
  SELECT md = MIN(d) FROM n 
    WHERE DATEPART(WEEKDAY, d) = 1 -- assuming DATEFIRST is Sunday
),
y(d,wk) AS
(
  SELECT n.d, ((DATEPART(DAYOFYEAR, n.d) - DATEDIFF(DAY, @start, x.md)-1)/7) + 1
  FROM n CROSS JOIN x
  WHERE n.d >= x.md
  AND n.d < DATEADD(YEAR, 1, @start)
)
SELECT [date] = d, [week] = wk
FROM y WHERE wk < 53
ORDER BY [date];

Results:

date        week
----------  ----
2009-01-04  1
2009-01-05  1
2009-01-06  1
2009-01-07  1
2009-01-08  1
2009-01-09  1
2009-01-10  1
2009-01-11  2
2009-01-12  2
...
2009-12-25  51
2009-12-26  51
2009-12-27  52
2009-12-28  52
2009-12-29  52
2009-12-30  52
2009-12-31  52

Note that week 52 won't necessarily be a full week, and that in some cases (e.g. 2012), the last day or two of the year might fall in week 53, so they're excluded.

An alternative approach is to repeat the MIN expression twice:

DECLARE @year INT = 2009;


DECLARE @start SMALLDATETIME;
SET @start = DATEADD(YEAR, @year-1900, 0);

;WITH n AS
(
  SELECT TOP (366) -- in case of leap year
      d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY name)-1, @start)
    FROM sys.all_objects
),
y(d,wk) AS
(
  SELECT n.d, ((DATEPART(DAYOFYEAR, n.d) - DATEDIFF(DAY, @start, (SELECT MIN(d) 
    FROM n WHERE DATEPART(WEEKDAY, d) = 1))-1)/7) + 1
  FROM n
  WHERE n.d >= (SELECT md = MIN(d) FROM n WHERE DATEPART(WEEKDAY, d) = 1)
  AND n.d < DATEADD(YEAR, 1, @start)
)
SELECT [date] = d, [week] = wk
FROM y WHERE wk < 53
ORDER BY d;

Answer 4

It would be relatively easy to setup a custom calendar table with one row for each date of the year in it, and then have other fields that will allow you to rollup however you want. I do this when I have clients using varying calendars, i.e. fiscal years, and it makes the query logic very simple.

Then you just join date-to-date and get the week-number that you want.

date       | reporting year | reporting week
-----------|----------------|---------------
2009-01-01 | 2008           | 52
2009-01-02 | 2008           | 52
2009-01-03 | 2008           | 52
2009-01-04 | 2009           | 01
2009-01-05 | 2009           | 01
etc.

and then to use it ( for example to get total sales rollup by your custom weeks, didn't validated my sql):

select reporting_year, reporting_month, sum(sales) 
  from sales
  inner join custom_date_table cdt on cdt.sysdate = sales.sysdate
  group by reporting_year, reporting_month
  where report_year=2009

Answer 5

Don't use a UDF, use a calendar table instead, then you can define week numbers exactly as your company requires and simply query them from the table, which will be much easier and possibly much faster than using a UDF.

A calendar table has numerous uses in SQL (search this site or Google) so you should probably create it anyway.

Answer 6

There is no good answer for this.

A year is NOT 52 weeks long.

It is 52 weeks and one day in normal years, and 52 weeks and two days in leap years.