How to find previous row using Week Number and Year

Question

I have table that has week number and year as two columns. Week number could be from 0 to 52 for each year, now problem is I have to find out previous update as week number and year on the basis of current update value, for example if current update is week-17 year-2018 then find previous update. It could be 2017 year's week 52 or 2018 years week 15. What could be the query to do just that.

I have tried this :

SELECT TOP(1) WEEKNUM AS WEEK,YEARNUM AS YEAR 
FROM UPDATE_TABLE
WHERE (WEEKNUM < 22 and YEARNUM = 2019) or (YEARNUM < 2019 ) 
ORDER BY WEEKNUM DESC,YEARNUM DESC

but it fails at OR part as only year less than 2018 are selected.

Here is table ┌─────────┬─────────┐ │ weeknum │ yearnum │ ├─────────┼─────────┤ │ 28 │ 2018 │ │ 19 │ 2018 │ │ 17 │ 2018 │ │ 21 │ 2018 │ │ 21 │ 2017 │ │ 30 │ 2017 │ │ 31 │ 2017 │ │ 48 │ 2017 │ │ 49 │ 2017 │ │ 50 │ 2017 │ │ 51 │ 2017 │ │ 22 │ 2019 │ └─────────┴─────────┘

Above query returns me this result

WEEK YEAR 51 2017

What I am expecting is

WEEK YEAR 28 2018

which is a last update in the system. Similarly if I consider 17 2018 as current update I should get 51 2017 as output.

Hint: Week number could be less than or greater than current update week and year could be current year or any year before current update year.

Answer 1

You merely got your ORDER BY clause mixed up. Order by year first, then by week:

SELECT TOP(1) WEEKNUM AS WEEK, YEARNUM AS YEAR 
FROM UPDATE_TABLE
WHERE (WEEKNUM < 22 and YEARNUM = 2019) or (YEARNUM < 2019 ) 
ORDER BY YEARNUM DESC, WEEKNUM DESC;

This should get you the last update week before 2019W22.

Answer 2

ORDER and filter by the WEEKNUM + (YEARNUM * 52) as a single derived column instead of trying to apply some kind of AND/OR logic.

You might also use ROW_NUMBER() to create a cardinal column so you can easily get the previous row. Or you might look into using the LAG() function.