Datepart into previous year based on week

user_10047672 0
Viewed 180

I am learning SQL at the moment and needed a query that would return the previous 12 weeks (excluding the current week) and the below worked just fine - that was until we moved in to 2019!

My table has 4 columns, BuildWeek, BuildYear, Info1, Info2, all are int.

select * 
from Dashboard 
where BuildWeek in (datepart(week, getdate()) - 1, 
                    datepart(week, getdate()) - 2,  
                    datepart(week, getdate()) - 3,  
                    datepart(week, getdate()) - 4,  
                    datepart(week, getdate()) - 5,
                    datepart(week, getdate()) - 6, 
                    datepart(week, getdate()) - 7, 
                    datepart(week, getdate()) - 8, 
                    datepart(week, getdate()) - 9, 
                    datepart(week, getdate()) - 10, 
                    datepart(week, getdate()) - 11, 
                    datepart(week, getdate()) - 12, 
                    datepart(week, getdate()) - 13)
  and BuildYear = datepart(year, getdate()) 
order by 
    BuildWeek desc

I know this is not the cleanest query so I'm quite open to being educated, I have tried a few things (using dateadd to no avail) but cannot seem to get it to function how I wish. I'm guessing the above stems from the query perhaps looking into a minus (current date -1 would be 0, -2 would be -1 so no results would be found?) but I'm not sure how to make it look backwards to return those extra weeks.

sql-server sql
2 Answers

Assuming you have one row per week:

select top (12) d.*
from Dashboard d
order by d.year desc, d.BuildWeek desc;

To avoid future weeks:

select top (12) d.*
from Dashboard d
where year < year(getdate()) or
      (year = year(getdate()) and buildweek <= datepart(week, getdate())
order by d.year desc, d.BuildWeek desc;

Or, if you want to use a where and years have 52 weeks:

select d.*
from dashboard d
where (y.year * 52 + buildweek) >= year(getdate()) * 52 + datepart(week, getdate());

This method becomes a bit trickier if years can have 53 weeks.

user_11440350

Another solution would be to generate a date from BuildWeek and BuildYear (say, the first day of the week), that can be used in the WHERE clause.

The advantage with this approach is that is guaranteed to return the records of the last 12 weeks, even if there is not exactly 12 of them (for example if you have gaps in the weeks series), or if future records exist.

SELECT d.*
FROM Dashboard d
WHERE 
    DATEADD( wk, DATEDIFF( wk, 7, CAST( d.BuildYear AS NVARCHAR(100) ) ) + (d.BuildWeek-1) , 7 )
        BETWEEN DATEADD( week, -12, GETDATE() ) AND GETDATE()
ORDER BY 
    d.BuildYear DESC,
    d.BuildWeek DESC

Tested in this db fiddle.

user_106767160
Related