Calculate the Running Total for the last five Transactions

Question

Calculate the Running Total for the last five Transactions

Viewing 13 posts - 46 through 57 (of 57 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Heh... Read it? I wrote it! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Jeff Moden (11/28/2008)
Heh... Read it? I wrote it! 😉

Somebody deleted some posts because that message is not making any sense now!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

That would be true. Some good fellow had the link to my Triangular join article with the comment that folks should take a gander. I didn't mean to scare him away with my comment. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I know what you mean, I used to take all those comments as they weredirected straight at me some time... forgetting the nature of the boeard :)... but that's makes you so god ;).

arkhan SSC Rookie Points: 26 More actions · Answer 5

One line statement:-

SELECT ID,TransactionDate,Balance,CASE WHEN id<5 THEN 0 ELSE(select SUM(balance) FROM Accounts where ID between acc.id-4 and acc.id) end as RunningTotal from Accounts acc

The Network rtraffic would be lesser, as per below statistics:-

Network Statistics

Number of server roundtrips11.0000

TDS packets sent from client11.0000

TDS packets received from server11.0000

Bytes sent from client378378.0000

Bytes received from server771771.0000

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Can you explain to me why the network traffic would change for the exact same result set... having the exact same amount of data?

I know that query is about 30 bytes smaller... but I mean come on, that won'thurt any network any time soon!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

arkhan (12/1/2008)
One line statement:-
SELECT ID,TransactionDate,Balance,CASE WHEN id<5 THEN 0 ELSE(select SUM(balance) FROM Accounts where ID between acc.id-4 and acc.id) end as RunningTotal from Accounts acc
The Network rtraffic would be lesser, as per below statistics:-
Network Statistics
Number of server roundtrips11.0000
TDS packets sent from client11.0000
TDS packets received from server11.0000
Bytes sent from client378378.0000
Bytes received from server771771.0000

Ummm.... lesser than what? I don't see any actual comparison here...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

arkhan SSC Rookie Points: 26 More actions · Answer 8

see Previous query's Network statistics (Byte sent and receide) hereunder:

Network Statistics

Number of server roundtrips11.0000

TDS packets sent from client11.0000

TDS packets received from server11.0000

Bytes sent from client11841184.0000

Bytes received from server816816.0000

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

I need some help here... how can the same amount of data sent on the wire require less resources, less work on the 2nd run... all that because of the query itself?

stephenejones1960 Grasshopper Points: 11 More actions · Answer 10

Thanks for the code. I just have a general question: Does someone know how to change the code so that the five items can be multiplied, instead of added? Such a script could be used to calculate running total returns.

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 11

stephenejones1960 (4/19/2014)
Thanks for the code. I just have a general question: Does someone know how to change the code so that the five items can be multiplied, instead of added? Such a script could be used to calculate running total returns.

Multiplying values is not a straight forward function in sql server, hence it has to be a different work around. One alternative can be using a pivot function. Please find the query for it:

SELECT distinct p.id, max(p.[1]) * max(p.[2]) * max(p.[3]) * max(p.[4]) * max(p.[5])

FROM (SELECT A.ID AS ID,B.ID AS BID, B.Balance ,ROW_NUMBER() OVER(Partition by a.id order by b.id) R

FROM Accounts A

cross JOIN Accounts B

WHERE B.ID BETWEEN A.ID-4

AND A.ID AND A.ID>4

)T

pivot (max(balance) for r in ([1],[2],[3],[4],[5]))P

group by p.id

Let me know if it helps.

--Divya

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 12

stephenejones1960 (4/19/2014)
Thanks for the code. I just have a general question: Does someone know how to change the code so that the five items can be multiplied, instead of added? Such a script could be used to calculate running total returns.

You'll have to check for accuracy because of the nature of FLOAT calculations but if you sum the LOG of numbers, the ANTILOG (which is EXP in SQL Server) will be the PRODUCT of the numbers.

For example,

WITH

cteSomeNumbers(N) AS

(

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5

)

SELECT EXP(SUM(LOG(N)))

FROM cteSomeNumbers

;

The answer is 120.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 13

That works for positive numbers > 0. If numbers can also be negative or 0, you can use this formula:

CASE

WHEN MIN(ABS(Value)) = 0 THEN 0

ELSE EXP(SUM(LOG(ABS(NULLIF(Value,0))))) * ((SUM(CAST(SIGN(Value) AS int) - 1) % 4) + 1)

END AS Product

It looks daunting, but it's not that bad.

The WHEN checks for at least one zero in the range of numbers to multiply and returns zero when that is the case.

The ELSE has two parts. The first part is the formula Jeff gave, but on the absolute number of the value (ignore the NULLIF for now, I'll get to that later), so this produces the product of the absolute values. The second part counts the number of negative values in the range, and if it's odd it multiplies by -1 (otherwise by 1). The trick here is that SIGN returns -1 or 1, subtracting 1 makes that 0 or -2, and summing that results in (-2) times the number of negative values - the remainder after division by -4 is 0 when there's an even number of negatives, or -2 when it's odd. Add 1 to get a multiplier of either 1 or -1.

The NULLIF is required to prevent errors. SQL Server can only evaluate the WHEN expression after grouping, but by then it has lost the individual values, so it will calculate parts of the expressions before grouping. One of those parts is LOG(ABS(Value)) - which would return an error for values of 0. The NULLIF replaces them with NULL (and because of the WHEN, the resulting intermediates will never be used).

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/