Looping Update Statement

Question

Looping Update Statement

BIjourneyMan

SSC Enthusiast

Points: 199
More actions
January 22, 2016 at 6:42 am

#324202

Hi Gurus,
I'm not sure if that's the correct term in my subject. I'm a bit a new to tsql. I'm struggling to get the output I desire.
Heres My Data:
ROWNO IDDate Identifier [SettledQty]
1 12320160111SET 100
2 12320160111SET 400
3 12320160111RET 200
4 12320160111SET 700
Legend:
SET = Less
RET = Add
Total of 1000.
Essentially here's what I want to achieve:
ROWNO ID Date [UnsettledQty] ZeroOut
1 123 20160111 900 N
2 123 20160111 500 N
3 123 20160111 700 N
4 123 20160111 0 Y
Here's the explanation:
Rowno 1 Total(900) = Total(1000) - UnsettledQty(100)
Rowno 2 Total(500) = Total(900) - UnsettledQty(400)
Rowno 3 Total(700) = Total(500) + UnsettledQty(200)
Rowno 4 Total(0) = Total(700) - UnsettledQty(700)
Hopefully you can visualize on what I'm trying to achieve.
Regards.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

Here's a quick attempt:

PS: For a tested solution, please post CREATE TABLE statements, plus INSERT statements with sample data, plus expected results.

WITH HelperCTE AS

(SELECT *, SUM(CASE WHEN Identifier = 'SET' THEN SettledQty ELSE -SettledQty END) OVER (ORDER BY ROWNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunTot

FROM YourTable)

SELECT ROWNO, ID, Date, 1000 - RunTot AS UnsettledQty, CASE WHEN UnsettledQty = 1000 THEN 'Y' ELSE 'N' AS ZeroOut

FROM HelperCTE;

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Lowell SSC Guru Points: 323518 More actions · Answer 2

nice solution from Hugo, as always;

for me, i really hate running totals stored in the table itself; plus it's breaking some of those rules for normalization, of course.

eventually, the data get disconnected and inaccurate over time.

I prefer to create a view over the top of the table, and use something like Hugo's example as the definition of the view;

that way it is always accurate when queried.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!