Skip to content
Advertisement

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

Advertisement

Answer

You seem to want an aggregation:

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

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement