Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to update the same column multiple times in an UPDATE statement? Expand / Collapse
Author
Message
Posted Wednesday, January 22, 2014 3:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:20 AM
Points: 110, Visits: 771
Hi All,
I'm working on a project where I have to get prices for a stock, adjusted for any splits that may have occurred. The problem I am having is when multiple splits occur for the same stock.

Test code:
DECLARE @StockPrice TABLE (
Name NVARCHAR(25),
ClosePrice FLOAT,
PriceDate DATE
)

INSERT @StockPrice (Name, ClosePrice, PriceDate)
VALUES ('CompanyA', 81.37, '11/30/2012'),
('CompanyA', 83.57, '12/31/2012'),
('CompanyA', 44.51, '1/31/2013'),
('CompanyA', 48.36, '2/28/2013'),
('CompanyA', 47.73, '3/31/2013'),
('CompanyA', 14.30, '4/30/2013'),
('CompanyA', 12.72, '5/31/2013'),
('CompanyA', 10.41, '6/30/2013'),
('CompanyA', 10.30, '7/31/2013'),
('CompanyA', 8.53, '8/31/2013'),
('CompanyA', 9.96, '9/30/2013'),
('CompanyA', 10.61, '10/31/2013'),
('CompanyA', 10.65, '11/30/2013'),
('CompanyA', 11.34, '12/31/2013')

DECLARE @Splits TABLE (
Name NVARCHAR(25),
ExDate DATE,
ShareIn FLOAT,
ShareOut FLOAT
)

INSERT @Splits (Name, ExDate, ShareIn, ShareOut)
VALUES ('CompanyA', '1/24/2013', 1, 2),
('CompanyA', '4/30/2013', 1, 3)

SELECT 'Before', *
FROM @StockPrice
ORDER BY PriceDate

UPDATE s
SET ClosePrice = ClosePrice * (ShareOut / ShareIn)
FROM @StockPrice s
JOIN @Splits sp
ON s.Name = sp.Name
AND s.PriceDate >= sp.ExDate

SELECT 'After', *
FROM @StockPrice
ORDER BY PriceDate

I want to have prices for Jan 2013 - Mar 2013 multiplied by 2, and prices for April 2013 - Dec 2013 multiplied by 6, but what is happening is that prices for Jan 2013 - Dec 2013 are multiplied by 2. Why isn't the second split processed (or how is it overwritten)?
Post #1533871
Posted Wednesday, January 22, 2014 4:00 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:55 PM
Points: 947, Visits: 2,870
Hi

The culprit would be the join condition in the update clause. This will return both splits for prices past Mar 2013 because of the >= operator. If you change your update to a select you will see these showing up.

You could change your update to something like the following to resolve it
--UPDATE s
-- SET ClosePrice = ClosePrice * (ShareOut / ShareIn)
SELECT *
FROM @StockPrice s
CROSS APPLY (
SELECT TOP 1 ExDate, ShareIn, ShareOut, Name
FROM @Splits ssp
WHERE s.PriceDate >= ssp.ExDate AND s.Name = ssp.Name
ORDER BY ExDate DESC
) sp

Post #1533878
Posted Wednesday, January 22, 2014 4:00 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:38 PM
Points: 3,313, Visits: 7,149
I can't remember if it's true, or maybe my mind is playing tricks on me, but an update will only update rows once even if a correspondant select would show multiple lines.
Here's a trick that might be adequate to your situation, but I can't claim it will be good enough performance wise.
WITH rowsCTE AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ExDate) rn
FROM @Splits
),
recCTE AS(
SELECT *
FROM rowsCTE
WHERE rn = 1
UNION ALL
SELECT row.Name, row.ExDate, row.ShareIn * rec.ShareIn, row.ShareOut * rec.ShareOut, row.rn
FROM rowsCTE row
JOIN recCTE rec ON row.Name = rec.Name
AND row.rn = rec.rn + 1
)
UPDATE s
SET ClosePrice = ClosePrice * Share
FROM @StockPrice s
CROSS APPLY( SELECT TOP 1 ShareOut / ShareIn AS Share FROM recCTE sp WHERE s.Name = sp.Name
AND s.PriceDate >= sp.ExDate
ORDER BY sp.ExDate DESC )x





Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1533879
Posted Wednesday, January 22, 2014 5:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:28 PM
Points: 3,609, Visits: 5,219
Try this:

UPDATE s
SET ClosePrice = ClosePrice * ISNULL(EXP(so) / EXP(si), 1)
FROM @StockPrice s
CROSS APPLY
(
SELECT SUM(LOG(ShareIn)), SUM(LOG(ShareOut))
FROM @Splits sp
WHERE s.Name = sp.Name
AND s.PriceDate >= sp.ExDate
) b (si, so);


Edit: Just make sure that none of your share in/out values are zero because of this:

SELECT LOG(0.)


Msg 3623, Level 16, State 1, Line 53
An invalid floating point operation occurred.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1533883
Posted Thursday, January 23, 2014 7:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:20 AM
Points: 110, Visits: 771
Thanks guys. Cross Apply did the trick. I seem to have a block on that; it just never occurs to me to use Cross Apply.

Cheers,
Tom
Post #1534064
Posted Thursday, January 23, 2014 5:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:28 PM
Points: 3,609, Visits: 5,219
Tom Bakerman (1/23/2014)
Thanks guys. Cross Apply did the trick. I seem to have a block on that; it just never occurs to me to use Cross Apply.

Cheers,
Tom


You might want to test the case where you have a 2 for 3 split. I didn't check it myself, but if it causes you a problem you may need to modify the CA to SUM the LOG of the ratio (instead of each of the parts), and then in the outer query multiply the price by the EXP of the summed logarithmic ratio.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1534298
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse