CODEWITHSUNDEEP
sql-servert-sqlwhile-loopset
Ravina Brar
Ravina Brar

Reputation: 7

SQL - How to convert procedural while loop to Set based

I am trying to improve performance and I have come across some articles about using SET based syntax rather than using a loop. But I'm not quite sure how I would convert the following syntax. I'm posting this to see if anyone can share some ideas with me so I can start understanding this better. Please see code below.

Thanks in advance!

/************************************************/
/****** Loop through duplicates *********************/
/************************************************/
DECLARE CUR CURSOR FOR SELECT Child_Identifier FROM #Dups
DECLARE @Child_Identifier INT

OPEN CUR

-- Fetches Child_Identifier value from #Dups one by one until the entire list of duplicate Child_Identifiers have been accessed.
FETCH NEXT FROM CUR INTO @Child_Identifier 

WHILE @@FETCH_STATUS = 0

BEGIN


-- Add duplicate child_identifier details to #TempDups. #TempDups will store the details pertaining to the given Child_Identifier
SELECT d.Plan_ID
  ,d.Child_Identifier
  ,d.Start_Date
  ,d.Expected_End_Date
  ,d.End_Date
  ,d.Closure_Date
  ,d.Closure_Reason
  , p.LASTUPDATEDON 
INTO #TempDups
FROM #DupsInfo d
INNER JOIN BI_STAGING.ISIS.Stage_ISIS_PLANENTITY p ON d.Plan_ID = p.PLANID
WHERE d.Child_Identifier = @Child_Identifier

DECLARE @HasDuplicateStartDates BIT
DECLARE @NumOfNullEndDates INT
DECLARE @TotalRows INT
DECLARE @AllEndDatesDuplicate BIT
DECLARE @PlanID BIGINT

SET @TotalRows = (SELECT COUNT(*) FROM #TempDups) -- Get total rows of duplicates for the specified @Child_Identifier
SET @HasDuplicateStartDates = (SELECT CASE WHEN EXISTS (SELECT * FROM #TempDups HAVING COUNT(Start_Date) > 1) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END)

IF @HasDuplicateStartDates = 1 /*** REF# IF_1.0 ***/
    -- Has Multiple Duplicate Start Dates
    BEGIN
        SET @NumOfNullEndDates = (SELECT COUNT(*) FROM #TempDups WHERE End_Date IS NULL) -- Get the count of null dates within the #TempDups table

        IF @NumOfNullEndDates = 1  /*** REF# IF_2.0 ***/
            -- Only has 1 record with NULL as End_Date. --> This record is the correct record to be returned.
            BEGIN
                -- Add to @Plans table for end results
                SET @PlanID = (SELECT Plan_ID FROM #Results WHERE End_Date IS NULL AND Child_Identifier = @Child_Identifier)

                INSERT INTO @Plans
                SELECT Plan_ID, Child_Identifier, 'Only 1 duplicate record has End_Date as NULL, therefore this record is selected'
                FROM #Results
                WHERE Plan_ID = @PlanID AND Child_Identifier = @Child_Identifier    
            END
        ELSE IF @NumOfNullEndDates = 0 /*** REF# IF_2.0 ***/
            -- There are 0 Empty (NULL) End Dates
            BEGIN
                --Check if all enddates are identical   
                SET @AllEndDatesDuplicate = (SELECT CASE WHEN EXISTS (SELECT COUNT(*) FROM #TempDups GROUP BY End_Date HAVING COUNT(*) = @TotalRows) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END)

                IF @AllEndDatesDuplicate = 1 /*** REF# IF_3.0 ***/
                    BEGIN
                        --All end dates are the same and therefore need to check LASTUPDATEDON field and select the record which holds the most recent date
                        -- Add to @Plans table for end results
                        SET @PlanID = (SELECT TOP 1(PLANID)
                                        FROM BI_STAGING.ISIS.Stage_ISIS_PLANENTITY
                                        WHERE PLANID IN (SELECT Plan_ID FROM #TempDups)
                                        ORDER BY LASTUPDATEDON DESC)

                        INSERT INTO @Plans
                        SELECT Plan_ID, Child_Identifier, 'All duplicate end dates - Selected by checking LASTUPDATEDON field' 
                        FROM #Results
                        WHERE Plan_ID = @PlanID AND Child_Identifier = @Child_Identifier    

                    END
                ELSE /*** REF# IF_3.0 ***/
                    -- End Dates are not the same, choose the most recent End Date record
                    BEGIN
                        -- Add to @Plans table for end results
                        SET @PlanID = (SELECT Plan_ID FROM #Results WHERE Child_Identifier = @Child_Identifier 
                                        AND End_Date = (SELECT TOP 1 End_Date FROM #Results WHERE Child_Identifier = @Child_Identifier ORDER BY End_Date DESC))

                        INSERT INTO @Plans
                        SELECT Plan_ID, Child_Identifier, 'End dates are NOT identifical - Therefore, selected the most recent End_Date'
                        FROM #Results
                        WHERE Plan_ID = @PlanID AND Child_Identifier = @Child_Identifier    

                END
            END
        ELSE -- /*** REF# IF_2.0 ***/
             -- There are multiple NULL End Dates
            BEGIN
                --Check LASTUPDATEDON field and select the record which holds the most recent date
                -- Add to @Plans table for end results
                SET @PlanID = (SELECT TOP 1(PLANID)
                                FROM BI_STAGING.ISIS.Stage_ISIS_PLANENTITY
                                WHERE PLANID IN (SELECT Plan_ID FROM #TempDups)
                                ORDER BY LASTUPDATEDON DESC)

                INSERT INTO @Plans
                SELECT Plan_ID, Child_Identifier, 'Multiple Null End Dates - Selected by checking LASTUPDATEDON field' 
                FROM #Results
                WHERE Plan_ID = @PlanID AND Child_Identifier = @Child_Identifier    
            END
    END
ELSE -- /*** REF# IF_1.0 ***/
    -- Does not have duplicate Start Dates, therefore select the most recent start date
    BEGIN
        -- Add to @Plans table for end results
        SET @PlanID = (SELECT TOP 1 Plan_ID FROM #Results WHERE Child_Identifier = @Child_Identifier ORDER BY [Start_Date] DESC)

        INSERT INTO @Plans
        SELECT Plan_ID, Child_Identifier, 'Does not have duplicate start dates - Most recent start date chosen'
        FROM #Results
        WHERE Plan_ID = @PlanID AND Child_Identifier = @Child_Identifier        
    END

DROP TABLE #TempDups -- Drop temp table to be used again for the next iteration
FETCH NEXT FROM CUR INTO @Child_Identifier -- Get next Child_Identifier from #Dups
END

CLOSE CUR
DEALLOCATE CUR

Upvotes: 0

Views: 347

Answers (1)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5787

I am not sure that somebody will answer your question properly. However I'll try to point out how to do that. So, as I see that the workflow is something like:

LOOP
(
IF (duplicates) THEN ...
ELSE ....
)

I'll not say anything about duplicates part as you'll need a lot of different CASEes to do it with the same logic, I'll try to show how you can do ELSE part. Worth to mention that I did not check for the syntax errors or other things and the example can have errors, but it might get you to the right direction

 INSERT INTO @Plans
    SELECT Plan_ID, Child_Identifier, 'Does not have duplicate start dates - Most recent start date chosen'
    FROM #Results r
    JOIN #DupsInfo d ON d.Child_Identifier = r.Child_Identifier
    JOIN (
            SELECT d.Child_Identifier,
                    d.Start_Date
    FROM #DupsInfo d
    INNER JOIN BI_STAGING.ISIS.Stage_ISIS_PLANENTITY p ON d.Plan_ID = p.PLANID
    GROUP BY d.Child_Identifier, d.Start_Date
    HAVING COUNT(*) =1
    ) no_dupl ON no_dupl.Child_Identifier=r.Child_Identifier
    WHERE EXISTS 
    (
      SELECT TOP 1 r2.Plan_ID
      FROM #Results r2
      WHERE r2.Child_Identifier = r.Child_Identifier
      ORDER BY r2.Start_Date DESC

    )

Upvotes: 1

Related Questions