T-SQL query relative week numbering issue when turning the year

Question

I have this view that has a specific, relative week numbering column called WeekNr. The two columns (WeekNr and CURWEEK) in the source table are held with the usual calendar week numbering: 1, 2, 3... to 52.

This view is used in an Excel file, and business wants to see a relative week numbering from -12 to +6: the past 12 weeks and 6 weeks projection, relative to week Zero which is the Current Week (today, 13 January 2015, this is calendar week 2).

So, this code worked just fine, up until the year turned. After 52, the WeekNr - CURWEEK doesn't work anymore as wished.

Do you have some kind of elegant solution for this?

CREATE VIEW [dbo].[vw_NOSSCE_HealthKPIs_DEV]
 AS 
 (
SELECT 
[Region]
      ,[Country]
      ,[GlobalMaterialCode]
      ,[Material]
      ,[MaterialCode]
      ,[PlantCode]
      ,[Plant]
      ,[Brand]
      ,
      (WeekNr - (SELECT TOP 1 CURWEEK FROM [TO_BDB].[dbo].[TO_BDB_NOSSCE_FACT_NORMALIZED]) ) as 
      WeekNr 
      ,[MEDICAL_NEED]
      ,[TOP_SELLER]
      ,[PHARMABU_CODE]
      ,[PharmaBU]
      ,[SourceLocationCode]
      ,[SourceLocation]
      ,[InventoryTotal]
      ,[MaxStockQtyTotal]
      ,[TotalSafetyStockTotal]
      ,[DangerTreshold]
      ,[FORECAST_FLG]
      ,[DangerZoneDesc]
 FROM [TO_BDB].[dbo].[TO_BDB_NOSSCE_PAST12_HIST] 
 WHERE (WeekNr - (SELECT TOP 1 CURWEEK
 FROM [TO_BDB].[dbo].[TO_BDB_NOSSCE_FACT_NORMALIZED]) )
  BETWEEN -12 and 6
  GROUP BY [Region]
      ,[Country]
      ,[GlobalMaterialCode]
      ,[Material]
      ,[MaterialCode]
      ,[PlantCode]
      ,[Plant]
      ,[Brand]
,WeekNr, [MEDICAL_NEED]
      ,[TOP_SELLER]
      ,[PHARMABU_CODE]
      ,[PharmaBU]
      ,[SourceLocationCode]
      ,[SourceLocation]
      ,[InventoryTotal]
      ,[MaxStockQtyTotal]
      ,[TotalSafetyStockTotal]
      ,[DangerTreshold]
      ,[FORECAST_FLG]
      ,[DangerZoneDesc]
 UNION ALL
 SELECT * FROM [dbo].[vw_NOSSCE_HealthKPIs] WHERE WeekNr > 0
)

UPDATE, I created the Calendar table, why does it give me a Week 53?

CREATE TABLE [dbo].[TO_BDB_NOSSCE_CALENDAR](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DATE] [date] NOT NULL,
    [YEAR]  AS (datepart(year,[DATE])) PERSISTED,
    [SEMESTER]  AS (case when datepart(month,[DATE])<(7) then '1' else '2' end) PERSISTED NOT NULL,
    [TRIMESTER]  AS (case when datepart(month,[DATE])<(4) then '1' else case when datepart(month,[DATE])<(7) then '2' else case when datepart(month,[DATE])<(10) then '3' else '4' end end end) PERSISTED NOT NULL,
    [MONTH]  AS (case when len(CONVERT([varchar](2),datepart(month,[DATE]),0))=(1) then '0'+CONVERT([varchar](2),datepart(month,[DATE]),0) else CONVERT([varchar](2),datepart(month,[DATE]),0) end) PERSISTED,
    [WEEK]  AS (case when len(CONVERT([varchar](2),datepart(week,[DATE]),0))=(1) then '0'+CONVERT([varchar](2),datepart(week,[DATE]),0) else CONVERT([varchar](2),datepart(week,[DATE]),0) end),
    [DAY]  AS (case when len(CONVERT([varchar](2),datepart(day,[DATE]),0))=(1) then '0'+CONVERT([varchar](2),datepart(day,[DATE]),0) else CONVERT([varchar](2),datepart(day,[DATE]),0) end) PERSISTED,
    [WEEKNUMBER]  AS (datepart(week,[DATE])),
PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Answer 1

First, if you're using code to generate an Excel file, it might be better to do the relative numbering with Excel formulas rather than using SQL.

Second, week numbers don't identify weeks. To identify a week, you need both the year and the week number, or you need a date. That means you might need to change a table to store the year in addition to the week.

Here we use a calendar table to simplify queries like this one. A minimal, simplified calendar table might look like this. We use ISO years and weeks, but don't let that confuse you. You can use just about any kind of numbering you want. (Written for PostgreSQL, but the principles are the same for SQL Server.)

create table calendar (
  cal_date date primary key,
  iso_year integer not null,
  iso_week integer not null
);

create index on calendar (iso_year, iso_week);

We use check constraints (not shown) to guarantee that the values for iso_year and iso_week are right for every value of cal_date. Very few (highly trusted) people have privileges to insert and delete rows.

In standard SQL, I might write something like this to get the absolute weeks we're interested in.

select distinct iso_year, iso_week
from calendar
where cal_date between cast(current_date - interval '12 weeks' as date) 
                   and cast(current_date + interval  '6 weeks' as date) 
order by iso_year, iso_week;

iso_year  iso_week
2014      43
2014      44
2014      45
...
2015      7
2015      8
2015      9

It returns 19 rows, which seems to be the right number for your case. (Watch for off-by-one mistakes on my part.) Put that into a common table expression or a view, and you can number the relative weeks with simple arithmetic. (I used CTEs so this answer is more or less self-contained.)

with absolute_weeks as (
  select distinct iso_year, iso_week
  from calendar
  where cal_date between cast(current_date - interval '12 weeks' as date) 
                     and cast(current_date + interval  '6 weeks' as date) 
)
select *, (row_number() over (order by iso_year, iso_week) - 13) as relative_week
from absolute_weeks 
order by iso_year, iso_week;

iso_year  iso_week  relative_week
--
2014      43        -12
2014      44        -11
2014      45        -10
...
2015      7           4
2015      8           5
2015      9           6

If your data contains the year and the week, you can just join your query to this on iso_year and iso_week. If your data has the date instead, you can move the previous query into a CTE or a view, and join the calendar table to get the dates for each week.

with absolute_weeks as (
  select distinct iso_year, iso_week
  from calendar
  where cal_date between cast(current_date - interval '12 weeks' as date) 
                     and cast(current_date + interval  '6 weeks' as date) 
), relative_weeks as (
  select *, (row_number() over (order by iso_year, iso_week) - 13) as relative_week
  from absolute_weeks 
)
select c.cal_date, c.iso_year, c.iso_week, r.relative_week
from calendar c
inner join relative_weeks r on c.iso_year = r.iso_year and c.iso_week = r.iso_week
order by c.cal_date;

cal_date    iso_year  iso_week  relative_week
--
2014-10-20  2014      43        -12
2014-10-21  2014      43        -12
2014-10-22  2014      43        -12
...
2015-01-12  2015       3          0
2015-01-13  2015       3          0
2015-01-14  2015       3          0
...
2015-02-27  2015       9          6
2015-02-28  2015       9          6
2015-03-01  2015       9          6

The current week--the week of 2015-01-13--is ISO week 3, not week 2 as you number them. Your own calendar table should reflect your own logic, not mine.

This query returns 133 rows, which seems to be the right number. Every ISO week has seven days, so 7 * 19 rows is the right number for a calendar table based on ISO weeks. Your own requirements might give you an occasional week that has a different number of days.