Epi-week query/method for SQL Server

Question

The definition for epi-week is the following:

"The first epi week of the year ends, by definition, on the first Saturday of January, as long as it falls at least four days into the month. Each epi week begins on a Sunday and ends on a Saturday."

Ref: 1

I implemented them in server language (C#/Java) but my problem is to return a epi week and epi year from a SQL Server stored procedure

Answer 1

Find EpiWeek:

 CREATE FUNCTION [dbo].[EPIWeek] 
( 
    @Date SMALLDATETIME 
) 
RETURNS TINYINT 
AS 
BEGIN 
    DECLARE @EPIWeek TINYINT 

    --Fix first epi week (i.e if 0401 of year falls in first week, then it will give perfect start epiweek)
    SET @EPIWeek = DATEPART(WEEK,@Date)+1 - DATEPART(WEEK,RTRIM(YEAR(@Date))+'0104') 
    --if 0401 of year falls in second week, then change to epiweek of last year
    IF @EPIWeek = 0 
    BEGIN 
        SET @EPIWeek = dbo.EPIWeek(RTRIM(YEAR(@Date)-1)+'1231')
    END 

    --Fix last epi week (ie. If any date after 2812 falls below wednesday, then it is first epiweek of next year)
    IF MONTH(@Date) = 12 AND DAY(@Date)-DATEPART(DW,@Date) >= 28 
    BEGIN 
        SET @EPIWeek=1 
    END 

    RETURN(@EPIWeek) 
END 

Find EpiYear:

CREATE FUNCTION [dbo].[EPIYear] 
( 
    @Date DATETIME
) 
RETURNS INT
AS
BEGIN
  DECLARE @EPIYear INT = DATEPART(YEAR, @Date);

  -- WHEN January 1-3 may belong to the previous year
  IF (DATEPART(MONTH, @DATE) = 1 AND dbo.EPIWeek(@DATE) > 50)
    SET @EPIYear = @EPIYear - 1;

  -- WHEN December 29-31 may belong to the next year
  IF (DATEPART(MONTH, @DATE) = 12 AND dbo.EPIWeek(@DATE) < 45)
    SET @EPIYear = @EPIYear + 1;

  RETURN @EPIYear;

END

Answer 2

I would highly recommend to create a Calendar table in your database if you don't have it already.

It is a table that has a row for each day for the period long enough to cover your needs.

Add a column EpiWeek to such table and populate it once. Then use anywhere you need it instead of calculating it on the fly.

Calendar table is useful in many areas and helps to solve various problems using SQL set-based queries. Calendar table is a special case of Numbers table, which is also often useful.

For detailed example see:

Why should I consider using an auxiliary calendar table?

This article has a section about ISO week number. I'm not sure if ISO Week and Epi Week are the same, but you can see an example how Aaron Bertrand calculated the ISO week number:

Many, many, many people are dissatisfied with the way SQL Server calculates week number. Its strange algorithm used in DATEPART(WEEK, <date>) does not always yield the same number as the ISO standard, which dictates that week 1 is the first week with 4 days. To deal with that, we can borrow from Books Online and create the following function:

CREATE FUNCTION dbo.ISOWeek 
( 
    @dt SMALLDATETIME 
) 
RETURNS TINYINT 
AS 
BEGIN 
    DECLARE @ISOweek TINYINT 

    SET @ISOweek = DATEPART(WEEK,@dt)+1 
        -DATEPART(WEEK,RTRIM(YEAR(@dt))+'0104') 

    IF @ISOweek = 0 
    BEGIN 
        SET @ISOweek = dbo.ISOweek 
        ( 
            RTRIM(YEAR(@dt)-1)+'12'+RTRIM(24+DAY(@dt)) 
        ) + 1 
    END 

    IF MONTH(@dt) = 12 AND DAY(@dt)-DATEPART(DW,@dt) >= 28 
    BEGIN 
        SET @ISOweek=1 
    END 

    RETURN(@ISOweek) 
END 
GO

Then we can update the table:

UPDATE Calendar SET W = dbo.ISOWeek(dt)

Also, here are few links about Numbers table:

You REQUIRE a Numbers table!

Generate a set or sequence without loops