Home > SQL Challenge > SQL – Using a WHILE Loop to Run a Procedure as Needed

SQL – Using a WHILE Loop to Run a Procedure as Needed

There are times we need to loop through a date range (each day, each month, each year etc.) to refresh our data. A WHILE loop is a convenient way to do this. This example loop through the first month when we have data from a table to refresh data in the past and to 6 month ahead for forecasting purpose.

ALTER PROCEDURE [dbo].[usp_SOME_RPT_RUN]
AS
BEGIN

    — ================================================================
    — CREATED BY: SHERRY LI ON 10/07/2009
    — ================================================================

    SET NOCOUNT ON

    DECLARE @START_DT DATETIME, @END_DT DATETIME
    DECLARE    @DT1 DATETIME, @DT2 DATETIME
    DECLARE    @MNTH INT, @COUNTER INT, @FROM_DT DATETIME, @TO_DT DATETIME
    DECLARE @OPTION VARCHAR(25)
    DECLARE @OPTION_RUN INT

    — ================================================================
    — WE NEED TO RUN THE PROCESS START FROM THE BEGINNING AND TO 6
    — MONTHS IN THE FUTURE
    — THIS IS BECAUSE WE NEED TO REFERSH THE FORCASTING
    — ================================================================

    — @DT1: THE EARLIEST DATE WE NEED TO GO BACK TO RERESH DATE
    — @DT2: THE FAREST DATE WE NEED TO GO IN THE FUTURE; 6 MONTHS INTO THE FUTURE
    SELECT @DT1 = MIN(DT) FROM tbl_SOME_RPT
    SELECT @DT2 = DATEADD(MONTH, 6, CONVERT(DATETIME, CONVERT(CHAR(10), GETDATE(), 101)))
    –PRINT @DT1
    –PRINT @DT2

    — ================================================================================
    — GET THE START AND END DATE AND THE NUMBER OF MONTHS WE NEED TO RUN THE PROCESS
    — ================================================================================
    — @START_DT: BOM FOR @DT1
    — @END_DT: EOM FOR @DT2
    — @MNTH: NO OF MONTH FROM @START_DT TO @END_DT

    SELECT    @START_DT = CONVERT(DATETIME, CONVERT(CHAR(10), DATEADD(DD, 1 – DAY(@DT1), @DT1), 101))
    SELECT    @END_DT = DATEADD(DAY, -1, CONVERT(DATETIME, CONVERT(CHAR(10), DATEADD(DD, 1 – DAY(@DT2), @DT2), 101)))
    SELECT    @MNTH = DATEDIFF(MONTH, @START_DT, @END_DT)
    — @COUNTER AND @FROM_DT ARE USED IN THE WHILE LOOP
    SELECT    @COUNTER = 0
    SELECT    @FROM_DT = @START_DT

    — USE THE WHILE LOOP TO CYCLE THROUGH EACH MONTH
    WHILE @COUNTER <= @MNTH
    BEGIN
        SELECT @TO_DT = DATEADD(DAY, -1, DATEADD(MONTH, 1, @FROM_DT))

        IF CONVERT(DATETIME, CONVERT(CHAR(10), DATEADD(DAY, -1, GETDATE()), 101)) BETWEEN @FROM_DT AND @TO_DT
            — IF IN THE CURRENT MONTH, LET’S REFRESH BOTH APPROVED AND COMPLETED
            BEGIN
                SELECT @OPTION = ‘BOTH’
                SELECT @OPTION_RUN = 1
            END
        ELSE
            BEGIN
                — IF IN THE PAST OR IN THE FUTURE, JUST REFRESH APPROVED ONLY
                SELECT @OPTION = ‘APPROVED ONLY’
                SELECT @OPTION_RUN = 1
                — IF IN THE PAST, AND THERE IS NO MORE APPROVED, SKIP IT BY SETTING THE  @OPTION_RUN = 0
                IF @FROM_DT < GETDATE()
                BEGIN   
                    SELECT @OPTION_RUN = COUNT(*) FROM tbl_SOME_RPT WHERE STATUS = ‘APPROVED’ AND DT BETWEEN @FROM_DT AND @TO_DT
                END
            END

        IF @OPTION_RUN >= 1
        BEGIN
            EXECUTE usp_SOME_RPT @FROM_DT, @TO_DT, @OPTION
        END

        SELECT @COUNTER = @COUNTER + 1
        SELECT @FROM_DT = DATEADD(MONTH, 1, @FROM_DT)
    END

END

Categories: SQL Challenge
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: