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
x
-- 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;
Advertisement
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.