Count decline Attempts with CURSOR in Sql Server

Hi i am stuck at this problem, i have worked a solution through CURSOR in SQL SERVER but my rows are around 400K and output is taking hours, can anyone do this faster? maybe through CROSS APPLY or anything. Thank you

Problem: Only Transaction Status “Declined” needs to be counted and if TransactionStatus is “Approved” Counter will be reset back to Zero.

Transactions Log

AccountNumber  CardNumber  TransactionStatus   EntryDate
10845522-XYZ    5471XXXXXXXX1111    Approved    10/09/2013
10845522-XYZ    5471XXXXXXXX1111    Declined    10/09/2014
10845522-XYZ    5471XXXXXXXX1111    Declined    10/09/2015
10845522-XYZ    5471XXXXXXXX9999    Approved    10/09/2016
10845522-ABC    5471XXXXXXXX6666    Declined    10/09/2012
10845522-ABC    5471XXXXXXXX6666    Declined    10/09/2019
10845522-DEF    5471XXXXXXXX7777    Declined    10/09/2016
10845522-DEF    5471XXXXXXXX7777    Approved    10/09/2019

Output Needed

AccountNumber  CardNumber  DeclineCounter  ModifiedDate
10845522-XYZ    5471XXXXXXXX1111    2   10/09/2015
10845522-XYZ    5471XXXXXXXX9999    0   10/09/2016
10845522-ABC    5471XXXXXXXX6666    2   10/09/2019
10845522-DEF    5471XXXXXXXX7777    0   10/09/2019

My Code/Approach with Cursor and Merge Statement, its working fine but taking Hours to complete due to CURSOR

-- Main Transaction Table
Select row_number() Over(Order by EntryDate) RowNumber
,Account, CARD_NUMBER, TransactionStatus, EntryDate
into dbo.TransactionLog_Temp
from dbo.TransactionLog


-- Cursor
DECLARE @RowNumber AS INT
DECLARE C CURSOR FOR
        Select Rownumber from dbo.TransactionLog_Temp order by Rownumber ASC

OPEN C

FETCH NEXT FROM C INTO @RowNumber;

    WHILE @@FETCH_STATUS = 0
    BEGIN

    -- Decide Update or Insert
    MERGE INTO dbo.Declined_Counter_Table AS T
    USING (Select Account,TransactionStatus,CARD_NUMBER,EntryDate 
    from TransactionLog_Temp where Rownumber=@RowNumber) S ON T.Account=S.Account AND T.CARD_NUMBER=S.CARD_NUMBER
        WHEN MATCHED AND T.ModifiedDate<>S.EntryDate
            THEN UPDATE SET T.[Counter] = CASE WHEN S.TransactionStatus ='Declined' THEN ISNULL(T.[Counter],0) + 1
                                                WHEN S.TransactionStatus ='Approved' THEN 0 END
                    ,T.ModifiedDate = S.EntryDate                             
        WHEN NOT MATCHED BY TARGET AND S.TransactionStatus ='Declined'
            THEN INSERT VALUES (S.Account, S.CARD_NUMBER,1, S.EntryDate);

    -- remove from main table, to resume the query anyother time since its taking too long
        Delete from dbo.TransactionLog_Temp where Rownumber=@RowNumber

    print @RowNumber
        FETCH NEXT FROM C INTO @RowNumber;
    END;

CLOSE C;
DEALLOCATE C;

Answer

You seem to want an aggregation:

Select Account, CARD_NUMBER,
       sum(case when TransactionStatus = 'Declined' then 1 else 0 end) as num_all_declines,
       sum(case when TransactionStatus = 'Declined' and
                     (EntryDate > last_approved_ed or last_approved_ed is null
                     )
                then 1 else 0 
           end) as num_recent_declines,           
       max(EntryDate)
from (select tl.*,
             max(case when TransactionStatus = 'Approved' then EntryDate end) over (partition by Account, CARD_NUMBER) as last_approved_ed
      from dbo.TransactionLog tl) query
group by Account, CARD_NUMBER;

This should be much, much faster than attempting to use a cursor.

Advertisement

Answer