Create Date Batches from Date Range - SQL Server, Sql Server

Create Date Batches from Date Range - SQL Server

DECLARE @DateStart  DATE = '01-JAN-18';
DECLARE @DateEnd    DATE = '01-FEB-18';
DECLARE @BatchSize  INT  = 7;

PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Started Date Range: ' + CONVERT(VARCHAR(20), @DateStart, 120) + ' -> '  + CONVERT(VARCHAR(20), @DateEnd, 120);

DECLARE @DateActiveStart DATE = @DateStart;
DECLARE @FutureDate      DATE = DateAdd(DAY, @BatchSize - 1, @DateActiveStart); -- Note: subtract 1 from @BatchSize as we include the start date in the batch
DECLARE @DateActiveEnd   DATE = CASE WHEN @FutureDate < @DateEnd THEN @FutureDate ELSE @DateEnd END;

WHILE @DateActiveStart <= @DateEnd
BEGIN
    PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Executing Batch: ' + CONVERT(VARCHAR(20), @DateActiveStart, 120) + ' -> '  + CONVERT(VARCHAR(20), @DateActiveEnd, 120);
    ----------------------------------------------------------------------------
    -- Process
    ----------------------------------------------------------------------------
    SELECT CONVERT(VARCHAR, GetDate(), 120) + ' - Executing Batch: ' + CONVERT(VARCHAR(20), @DateActiveStart, 120) + ' -> '  + CONVERT(VARCHAR(20), @DateActiveEnd, 120) AS [output];
    /*
    EXECUTE [DB].[Schema].[stored_procedure] @DateVariable1 = @DateActiveStart, @DateVariable2 = @DateActiveEnd, ;
    */
 
    ----------------------------------------------------------------------------
    -- Increment
    ----------------------------------------------------------------------------
    SET @DateActiveStart = DateAdd(Day, 1, @DateActiveEnd);
    SET @FutureDate      = DateAdd(DAY, @BatchSize - 1, @DateActiveStart); -- Note: subtract 1 from @BatchSize as we include the start date in the batch
    SET @DateActiveEnd   = CASE WHEN @FutureDate < @DateEnd THEN @FutureDate ELSE @DateEnd END;
END;
 
PRINT CONVERT(VARCHAR, GetDate(), 120) + ' - Completed Date Range: ' + CONVERT(VARCHAR(20), @DateStart, 120) + ' -> '  + CONVERT(VARCHAR(20), @DateEnd, 120);


run \| edit \| history \| help	0