Can a recursive CTE always replace a cursor?

Hi,

Some time ago, I had a data problem in SQL Server 2008 that I solved with a cursor. It's bugged me ever since that I couldn't think how to solve the problem without a cursor. I know that cursors are generally considered bad practice in SQL Server and I am particularly interested in recursive CTEs and how they might provide a set-based solution to problems that used always to need a cursor.

Here is an outline of the problem:
There are three tables: User, Award and CardTransaction. Award and CardTransaction have foreign key relationships to User on UserID. The application has been running for some time, capturing awards for users in the Award table (credits) and periodically capturing card transactions for users in the CardTransaction table (debits). The card transactions are just aggregated outstanding user balances at a snapshot moment in time. Due to a design flaw, there was no relationship between awards and card transactions. However, requirements emerged and it became apparent that individual awards needed to be identified as paid or unpaid and, if paid, which card transaction paid them.

The design flaw is addressed in the application and a new foreign key, CardTransactionID, is posted into the Award table. It is full of NULL values initially and the challenge is to populate this column with the appropriate values from CardTransaction.

What I want to do is group the awards per UserID and rank them by AwardDate and AwardID. Then I want to compare them to card transactions that are grouped by UserID and ranked by TransactionDate and CardTransactionID. For each award that is dated on or before a card transaction, I want to loop through, starting from the earliest non-allocated award, taking the award amount out of the reducing card transaction amount to check that balance is greater than or equal to zero. If true, then the award gets allocated to the card transaction, otherwise it remains unallocated (i.e. CardTransactionID remains NULL in Award table) and the award can be included in the next loop, if there is a later card transaction for the user.

I appreciate this is a tricky problem and I know it's one that can be solved using a cursor. What I'm really after here is a better understanding of how to avoid cursors when faced with this kind of data problem that needs a script to fix it. I'm a fan of CTEs but haven't yet found much use for recursive CTEs beyond the classic hierarchical data issue of an Employee table and determining organisational tree levels per employee from the self referencing relationship of ManagerID to EmployeeID. If anyone can at least help me understand recursive CTEs better or any other set based approach to this kind of issue, I will be very grateful.

To help better understand the data problem I describe above, I have prepared the following script that will set up the three tables and data. Thanks in advance.

USE Test
GO

CREATE TABLE dbo.[User] (
UserID int NOT NULL,
UserName varchar(50) NOT NULL)
GO

ALTER TABLE dbo.[User]
ADD CONSTRAINT PK_User
      PRIMARY KEY (UserID)
GO

CREATE TABLE dbo.Award (
AwardID int NOT NULL,
AwardAmount decimal(6,2) NOT NULL,
AwardDate date NOT NULL,
CardTransactionID int NULL,
UserID int NOT NULL)
GO

ALTER TABLE dbo.Award
ADD CONSTRAINT PK_Award
      PRIMARY KEY (AwardID)
GO

ALTER TABLE dbo.Award
ADD CONSTRAINT FK_Award_User
      FOREIGN KEY (UserID) REFERENCES dbo.[User](UserID)
GO

CREATE TABLE dbo.CardTransaction (
CardTransactionID int NOT NULL,
TransactionDate date NOT NULL,
TransactionAmount decimal(8,2) NOT NULL,
UserID int NOT NULL)
GO

ALTER TABLE dbo.CardTransaction
ADD CONSTRAINT PK_CardTransaction
      PRIMARY KEY (CardTransactionID)
GO

ALTER TABLE dbo.CardTransaction
ADD CONSTRAINT FK_CardTransaction_User
      FOREIGN KEY (UserID) REFERENCES dbo.[User](UserID)
GO

ALTER TABLE dbo.Award
ADD CONSTRAINT FK_Award_CardTransaction
      FOREIGN KEY (CardTransactionID) REFERENCES dbo.CardTransaction(CardTransactionID)
GO

INSERT INTO dbo.[User] (UserID,UserName)
VALUES(1,'testuser')
GO

INSERT INTO dbo.Award (AwardID,AwardAmount,AwardDate,UserID)
VALUES(1,100.00,'2011-01-01',1),
      (2,55.00,'2011-02-01',1),
      (3,30.00,'2011-02-05',1),
      (4,45.00,'2011-02-15',1),
      (5,15.00,'2011-02-23',1),
      (6,5.00,'2011-02-21',1)
GO

INSERT INTO dbo.CardTransaction (CardTransactionID,TransactionDate,TransactionAmount,UserID)
VALUES(1,'2011-02-05',155.00,1),
      (2,'2011-02-21',80.00,1)
GO
irb56Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I don't think you can solve this with recursive queries, as this is not the type of recursive stuff that the recursive CTE can do.
explanation: the CTE is based on static data.
your problem is that with each record (transation) processed (hence award record updated) the data is changing.

another issue is that your design is still not complete, because the relationship between Awards and transations is not 1-to-many, but can be many-to-many, so you would need another link table, which indicates with amount is assigned between an Award record and an Transaction record.
so, 1 transaction may pay several awards, or several transactions may pay 1 award.
this might not be the case for your system, though, but in real-life systems I have worked for, it was always the case, in the end.

anyhow, with this additional table, you can also see easily if an award is fully paid checking the total amounts assigned with this table to each award etc ...

and this table can be filled up more easily (though I don't have my code ready), as it could be maintained with triggers on the 2 tables ... on the fly

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
>> Can a recursive CTE always replace a cursor? <<

No.  Certain complex recursions require row-by-row logic.  Sometimes just complex logic does, like yours.

Cursors should generally be avoided, but sometimes can't be.
irb56Author Commented:
Thanks both for your advice. It's nice to know that my original cursor based fix was appropriate. It kept bugging me to think that such a problem could be solved more efficiently using set based logic. You've set my mind at rest!  :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.