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 «««23456

Calculate the Running Total for the last five Transactions Expand / Collapse
Author
Message
Posted Monday, December 1, 2008 5:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
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!
Post #611210
Posted Monday, December 1, 2008 5:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #611231
Posted Monday, December 1, 2008 6:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 5:44 AM
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


Post #611245
Posted Monday, December 1, 2008 7:02 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
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?
Post #611299
Posted Saturday, April 19, 2014 10:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 20, 2014 11:03 PM
Points: 1, Visits: 7
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.
Post #1563269
Posted Sunday, April 20, 2014 10:48 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Saturday, August 16, 2014 7:06 AM
Points: 143, Visits: 548
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
Post #1563343
Posted Sunday, April 20, 2014 12:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 36,958, Visits: 31,465
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563349
Posted Sunday, April 20, 2014 1:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 5,967, Visits: 8,221
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
Post #1563356
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse