November 17, 2009 at 7:35 pm
Hi Gurus.
Here are the details of an issue that I want to fix, without using cursors.
My Table is:
CREATE TABLE [dbo].[Transfer](
[FromInvestorID] [int] NULL,
[ToInvestorID] [int] NULL,
[TransferRatio] [float] NULL,
[StartingBasis] [float] NULL,
[BasisTransfer] [float] NULL,
[TransferRank] [int] NULL,
[TransferDate] [datetime] NULL
) ON [PRIMARY]
Here is the script to generate some sample data:
INSERT INTO [Transfer] ([FromInvestorID],[ToInvestorID],[TransferRatio],[StartingBasis],[BasisTransfer],[TransferRank],[TransferDate])VALUES(127,128,2.843583028359050e-001,4.977220000000000e+005,0.000000000000000e+000,1,'May 1 2008 12:00:00:000AM')
INSERT INTO [Transfer] ([FromInvestorID],[ToInvestorID],[TransferRatio],[StartingBasis],[BasisTransfer],[TransferRank],[TransferDate])VALUES(127,129,2.091341428218890e-001,4.977220000000000e+005,0.000000000000000e+000,2,'Jul 1 2008 12:00:00:000AM')
INSERT INTO [Transfer] ([FromInvestorID],[ToInvestorID],[TransferRatio],[StartingBasis],[BasisTransfer],[TransferRank],[TransferDate])VALUES(129,128,1.000000000000000e+000,1.340023000000000e+006,0.000000000000000e+000,1,'Aug 1 2008 12:00:00:000AM')
INSERT INTO [Transfer] ([FromInvestorID],[ToInvestorID],[TransferRatio],[StartingBasis],[BasisTransfer],[TransferRank],[TransferDate])VALUES(126,130,1.000000000000000e+000,1.061797090000000e+008,0.000000000000000e+000,1,'Nov 1 2008 12:00:00:000AM')
So the table looks something like this:
FromInvestorID ToInvestorID TransferRatio StartingBasis BasisTransfer TransferRank TransferDate
-------------- ------------ ---------------------- ---------------------- ---------------------- ------------ -----------------------
127 128 0.284358302835905 497722 0 1 2007-03-01 00:00:00.000
127 129 0.209134142821889 497722 0 2 2007-06-01 00:00:00.000
129 128 1 1340023 0 1 2007-09-01 00:00:00.000
126 130 1 106179709 0 1 2007-12-01 00:00:00.000
Most of the column names are self-explanatory. But here goes:
The first 2 columns are the ids of investors.
Transfer ratio is the percentage of cash that was transferred. It is the percentage based on income at a point of time.
StartingBasis is the amount of cash available to the source investor, at the beginning of year.
Basis Transfer is the column that I need to update. It is all 0 in the beginning.
Transfer Rank is a derived column that I pre-calculate. You can ignore it if you want. I use it though. What does it represent ? If there are 10 transfers made by an investor, then they are given a rank if 1 - to - 10 in chronological order.
Date is the date of transfer.
So, my requirement is that I have to update BasisTransfer for each row. This column represents the actual amount that was transferred.
BasisTransfer = TransferRatio * StartingBasis.
Simple enough?
But the problem is that with each transfer the value of "StartingBasis" changes for the corresponding investors. So the first transfer results in
increasing the BasisTransfer for investorid=128 (recipient).
So its new StartingBasis will be : StartingBasis + BasisTransfer from this transaction.
This transfer also decreases the "StartingBasis" of investorid=127 (source). So it new StartingBasis becomes:
StartingBasis - BasisTransfer from this transaction.
Also, if there are 2 transfers from the same investor, then both the transfers will use the same StartingBasis (there will not
be any change in StartingBasis "for that source investor" because they happened on the same day.) The recipient StartingBasis will change, of course.
This new starting basis will be used for subsequent calculations concerning these investors.
My attempt at resolving this was like this:
Declare @lastBasisTransferred float
Declare @lastTransfer DateTime
Set @lastBasisTransferred=0
Update Transfer Set @lastBasisTransferred = BasisTransfer =
Case
When TransferRank = 1 Then StartingBasis*TransferRatio
When (TransferRank <> 1 And DateDiff(dd, @lastTransfer, TransferDate) = 0) Then StartingBasis*TransferRatio
Else TransferRatio*(StartingBasis-@lastBasisTransferred)
End
, @lastTransfer = TransferDate
November 18, 2009 at 3:31 am
Check this great article by Jeff Moden:
http://qa.sqlservercentral.com/articles/T-SQL/68467/
It should do the trick for you.
Regards
Gianluca
-- Gianluca Sartori
November 18, 2009 at 6:52 am
Two things.
One, can you add a few more sample records that will cover all the cases you have described in your problem statement, for example, multiple transfers from the same investor on the same day.
Two, can you provide the expected results. It would help to see what the data should look like after running the update.
November 18, 2009 at 10:35 am
Here is a sample of the result that I wish to achieve:
TransferID FromInvestorID ToInvestorID TransferRatio StartingBasis BasisTransfer TransferRank TransferDate
----------- -------------- ------------ ---------------------- ---------------------- ---------------------- ------------ -----------------------
1 127 128 0.284358302835905 423230.380675188 141531.383204092 1 2008-05-01 00:00:00.000
2 127 129 0.209134142821889 356190.616795908 74491.6193248121 2 2008-07-01 00:00:00.000
3 129 128 1 1414514.61932481 1414514.61932481 1 2008-08-01 00:00:00.000
4 126 130 1 106179709 106179709 1 2008-11-01 00:00:00.000
Notice how the StartingBasis as well as Basis Transfer change.
I achieved it using cursors .... (and adding an ID column) ... but I would love to let go of the cursor.
PS: I will read Jeff article next to see if this can be improved.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply