# Creating a loop that will go back 52 weeks on calendar

I think I have confused myself on trying to come up with the following SQL logic. Can anyone steer me in right direction?

I need to create a loop that would start on current day, and and look back 52 weeks. But but each week and insert into a table.

for example lets say this ran on this coming friday 05/10. I would need the code look back at last weeks 05/03 and return all records that have a invoice with this date and insert into a table with a column labeling the these records as week17.

Then once it has found all week17 records, I would need it to loop back through to the next week which would be week16. and then repeat this for 52weeks

If you think in terms of actual code (loop) implementation of the problem you are trying to solve, you will paint yourself into a corner.
In plain English state the problem you are to solve without any sort of technical lingo

You don't need a loop, you need to think more like SQL. The basic approach is to use a table of numbers and then generate the dates you need from that. Something a bit like:

``````With E1(N) As (Select 1 From (Values(1),(1),(1),(1),(1)) N(X)),
E2(N) As (Select 1 From E1 Cross Join E1 A Cross Join E1 A2),
E4(N) As (Select 1 From E2 Cross Join E2 A Cross Join E2 A2),
Numbers As
(
Select
Number
From (Select Top(52 * 7) Row_Number() Over (Order By N) - 1 As Number From E4) NUMBERS
)
Select
DateAdd(Day, - Number, Cast(GetDate() As Date)) As Date,
Number / 7 As WeekNumber
From Numbers``````

Here is an example:

``````   With dates
As (
Select *
, EndDate = dateadd(day, (datediff(day, 4, getdate()) / 7) * 7, 4 - (t.n * 7))
From (Select row_number() over(Order By @@spid) - 1 As rn From sys.all_columns ac) As t(n)
Where t.n <= 52
)
Select StartDate = dateadd(day, -6, EndDate)
, EndDate
, WeekNumber = datename(week, StartDate)
From dates
``````

Or - you can base it on start day:

``````   With dates
As (
Select *
, StartDate = dateadd(day, (datediff(day, 5, getdate()) / 7) * 7, 5 - (t.n * 7))
From (Select row_number() over(Order By @@spid) As rn From sys.all_columns ac) As t(n)
Where t.n <= 52
)
Select StartDate
, EndDate = dateadd(day, 6, StartDate)
, WeekNumber = datename(week, StartDate)
From dates
``````

This can then be joined to your Invoice table:

``````   From dbo.Invoice i
Inner Join dates d On i.InvoiceDate >= d.StartDate And i.InvoiceDate < dateadd(day, 7, d.StartDate)
``````

If InvoiceDate is a DATE data type you could use between:

``````   From dbo.Invoice i
Inner Join dates d On i.InvoiceDate Between d.StartDate And dateadd(day, 6, d.StartDate)
``````

This will include invoices for the past year and allow you to categorize each week based on any values you want to calculate.