Passing Dates in batches of 7 days to a stored proc until the last 7 days

Question

So I have stored proc which will be used to retrieve data that will be imported into another table for a long period. '2020-01-01' to '2022-02-28' I want to do this in batches of 7 days. I don't want to manually run the PROC and pass 7 days range for a 2 year period.

Pseudo example:

INSERT INTO dbo.MyImportedData
INSERT INTO dbo.MyImportedData
EXEC [dbo].[MyLongRangeData]
          @StartDate = @StartDate,
          @EndDate = @EndDate -- First 7 Day Period (2020-01-01 to 2020-01-07)

INSERT INTO dbo.MyImportedData
EXEC [dbo].[MyLongRangeData]
          @StartDate = @StartDate,
          @EndDate = @EndDate -- second 7 Day Period (2020-01-08 to 2020-01-14) --Doing this until 2022-02-28.

Thanks in advance for help.

Answer 1

Assuming you just want simple 7-day blocks and don't need to align to the first day of the year or fiscal periods, you could do a simple loop, something like this:

DECLARE @d date = '20200101', @e date;

WHILE @d <= '20220228'
BEGIN
  SET @e = DATEADD(DAY, 6, @d);

  INSERT dbo.MyImportedData(<always list columns here!>)
    EXEC [dbo].[MyLongRangeData] @StartDate = @d, @EndDate = @e;

  SET @d = DATEADD(DAY, 7, @d);
END

But better would be to re-write the procedure (or create a new one) to handle 7-day chunks for you across any date range, so you don't have to call it 100 times.