Creating a loop that will go back 52 weeks on calendar

Transact-SQL
1

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

2

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

3

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
4

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.