How to exclude weeks based on Year and Week columns?

Question

I have a [Year/Week] column, based on which I want to have a window of 12 weeks inclusive of current week. For example: If current week is Week 5, I want to show my window from Week 5 to Week 16, regardless if the current day is Monday or Friday.

I have tried setting some numbers to adjust this window in the code below, but I believe there must be a better way.

CREATE TABLE #DATES ([Year/Week] VARCHAR(MAX))

INSERT INTO #DATES ([Year/Week]) VALUES
('2019/W01'),('2019/W02'),('2019/W03'),('2019/W04'),('2019/W05')
,('2019/W06'),('2019/W07'),('2019/W08'),('2019/W09'),('2019/W10')
,('2019/W11'),('2019/W12'),('2019/W13'),('2019/W14'),('2019/W15')
,('2019/W16'),('2019/W17'),('2019/W18'),('2019/W19'),('2019/W20')
,('2019/W21'),('2019/W22'),('2019/W23'),('2019/W24'),('2019/W25')
,('2019/W26'),('2019/W27'),('2019/W28'),('2019/W29'),('2019/W30')
,('2019/W31'),('2019/W32'),('2019/W33'),('2019/W34'),('2019/W35')

SELECT DISTINCT 
CONVERT(INT, LEFT([Year/Week], 4)) AS [Year]
,CONVERT(INT, RIGHT([Year/Week], 2)) AS [Week]
,dateadd (week, CONVERT(INT, RIGHT([Year/Week], 2)), dateadd (year, 
CONVERT(INT, LEFT([Year/Week], 4))-1900, 0)) - 4 -
       datepart(dw, dateadd (week, CONVERT(INT, RIGHT([Year/Week], 2)), 
dateadd (year, CONVERT(INT, LEFT([Year/Week], 4))-1900, 0)) - 4) + 1  AS 
[Week Date]

FROM #DATES
WHERE dateadd (week, CONVERT(INT, RIGHT([Year/Week], 2)), dateadd (year, 
CONVERT(INT, LEFT([Year/Week], 4))-1900, 0)) - 4 -
       datepart(dw, dateadd (week, CONVERT(INT, RIGHT([Year/Week], 2)), 
dateadd (year, CONVERT(INT, LEFT([Year/Week], 4))-1900, 0)) - 4) + 1 
BETWEEN GETDATE() - 3 AND GETDATE() + 81

ORDER BY [Year], [Week]

Why are you storing that as one string instead of two numbers? — sticky bit, Jan 30 '19 at 00:15
If the data is already stored like that, it may be easier to create a second reference table where 2019/W01 is defined as 2019-01-01 startdate and 2019-01-07 EndDate. If you want to define your weeks based on the first Monday, then adjust your dates accordingly. — Nick A, Jan 30 '19 at 00:26
By the way, the [ISO 8601 standard format for a week](https://en.wikipedia.org/wiki/ISO_8601#Week_dates) uses a hyphen rather than the slash seen your text: `2019-W01`. Or, optionally, in the “Basic” variation minimizing the use of delimiters, no hyhpen: `2018W01`. (I recommend using full length rather than Basic, wherever feasible.) — Basil Bourque, Jan 30 '19 at 22:37

score 3 · Answer 1 · answered Jan 30 '19 at 00:29

3

I suggest you to have Calendar table for reporting purposes.

After that you can easily get same result with small query

select Year, Week 
from Auxiliary.Calendar 
where Date between GetDate() and DATEADD(MONTH, 4, GetDate())

Here is DBFiddle

answered Jan 30 '19 at 00:29

Derviş Kayımbaşıoğlu

28,492
4
50
72

1

@shantanu This is what I was suggesting. – Nick A Jan 30 '19 at 00:30

shawnt00 · Answer 2 · 2019-01-30T19:04:41.533

It looks like you're using ISO weeks. You should be able to perform the comparison "alphabetically" with something like this:

select [Year/Week] from #DATES
where [Year/Week] between
    format(getdate() -  3, 'yyyy/\W') + format(datepart(iso_week, getdate() -  3), '0#') and
    format(getdate() + 81, 'yyyy/\W') + format(datepart(iso_week, getdate() + 81), '0#')
order by [Year/Week];

Make sure the week numbers match with yours.

Unfortunately I didn't think of a way to avoid repeating the date expression. Perhaps there's even something a bit more compact. An expression like this will probably be better at using an index on your Year/Week column.

How to exclude weeks based on Year and Week columns?

2 Answers2