Problems with Iterating through Data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problems with Iterating through Data

Author

Topic

JCurr
Starting Member

2 Posts

Posted - 2012-02-09 : 13:08:47

I'm relatively new to SQL and am having some difficulties finding a remotely efficient way to write code that has SQL iterate through a table to do the following:

I have a history of records for given individuals (PersonID) that have transactions (transid) with payments (payamt) on certain dates (transdt). The problem is that some of the transaction's are 'split' into multiple transaction ID's but should really be mapped to one original transaction.

This occurs for transactions that are on consecutive dates (i.e. the transdt fields are one day apart) and the initial transaction has a 'partial' payment status (paystat='P') a paystat value of 'F' on any line denotes that the status is complete and the next transaction will be the start of another unique transaction. I need to assign the initial transaction ID to all of the partial transactions in the database for reporting purposes. Hopefully this makes sense.

Any ideas are greatly appreciated. I've always used other languages (SAS) in the past to do things like this, and the only way I've figured out to do this so far is really brute-force code that is terribly inefficient.

Thanks

Joe

** As Requested **
The following is an example of the data I'm working with. The first five fields are original and the sixth field, 'Orig_Transid' is the column I need to add to the data. Here is an example:

-----------Original Data----------------------- - New Field-
Personid TransID Transdt Payamt PayStat Orig_TransID
1234 1 1/1/2012 $200.00 P 1
1234 2 1/2/2012 $150.00 F 1
1234 3 1/3/2012 $325.00 F 3
2122 4 1/6/2012 $400.00 F 4
2122 5 1/10/2012 $700.00 F 5
2122 6 1/11/2012 $134.00 F 6
3451 7 1/7/2012 $343.00 P 7
3451 8 1/8/2012 $542.00 P 7
3451 9 1/9/2012 $300.00 F 7

Hope this clarifies

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 13:15:31

maybe it is only me but I can understand the problem and (maybe) give a solution when you can post the table structures, some sample data and the wanted result.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-09 : 13:55:04

do you mean something like below
if this is giving you correct completedid you can just transform select to an update


SELECT t.*,
CASE WHEN partialcnt > 0 THEN MinPartial ELSE 
t.transid  END AS ActualTransClosedID
FROM table t
OUTER APPLY (SELECT MAX(transid) AS maxclosed
             FROM table
             WHERE PersonID= t.PersonID
             AND transdt < t.transdt
             AND paystat = 'F'
            )t1
OUTER APPLY (SELECT COUNT(1) AS partialcnt,
             MIN(transid) AS MinPartial
             FROM table
             WHERE PersonID= t.PersonID
             AND transdt < t.transdt
             AND transdt > maxclosed
             AND paystat = 'P'
            )t2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources