SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculate the Running Total for the last five Transactions


Calculate the Running Total for the last five Transactions

Author
Message
Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65243 Visits: 9671
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
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207656 Visits: 41965
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 roundtrips 1 1.0000
TDS packets sent from client 1 1.0000
TDS packets received from server 1 1.0000
Bytes sent from client 378 378.0000
Bytes received from server 771 771.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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
arkhan
arkhan
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 44
see Previous query's Network statistics (Byte sent and receide) hereunder:

Network Statistics
Number of server roundtrips 1 1.0000
TDS packets sent from client 1 1.0000
TDS packets received from server 1 1.0000
Bytes sent from client 1184 1184.0000
Bytes received from server 816 816.0000

Ninja's_RGR'us
Ninja's_RGR'us
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65243 Visits: 9671
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
stephenejones1960
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 11
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
Divya Agrawal
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1024 Visits: 604
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
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207656 Visits: 41965
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18417 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search