October 11, 2011 at 10:25 pm
I have got a table as below
StatementNoLedgerID OpeningBal ClosingBal Rank
------------------- ---------- ---------- ----
1007055110070550-26021
1007055210070550-100532
1007055310070550-200023
1007055410070550-250004
1007055510070550-219905
I want the OpeningBal value to be cumulative value of previous Closing Balance records so the result I am expecting is:
StatementNoLedgerIDOpeningBalClosingBalRank
-------------------------------------------
1007055110070550-26021
100705521007055-2602-126552
100705531007055-12655-326573
100705541007055-32657-576574
100705551007055-57657-796475
What is the simplest way of achieving this result via t-sql? Any help will be appreciated
October 11, 2011 at 11:00 pm
To get it with the optimal performance and the shortest way also, you could run the below :
update tableA SET OpeningBal = SUM (OpeningBal) FROM (SELECT OpeningBal, ClosingBalRank
FROM TABLEA )S inner join tableA on tableA.ClosingBalRank >=s.ClosingBalRank
with takin with conisder to create the below index
CREATE NONCLUSTERED index tableA_index1 on tableA (ClosingBalRank asc) include (OpeningBal)
And if much data entitiyt there, you could use page compression for that index
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
October 12, 2011 at 7:43 am
Thanks for the response. I was looking at the following solution which I managed to work out
SELECT
a.GBSSTATEMENT,
a.GBSLEDGER,
ISNULL(b.cumulativesum,0) as OpeningBal,
a.cumulativesum as ClosingBal
FROM
(select
x.GBSSTATEMENT,
x.GBSLEDGER,
(select sum(y.AMOUNT) from dbo.ledger_summary y where y.ranknum <= x.ranknum) As CumulativeSum,
x.RANKNUM
from dbo.ledger_summary x
) a
LEFT JOIN
(select
x.GBSSTATEMENT,
x.GBSLEDGER,
(select sum(y.AMOUNT) from dbo.ledger_summary y where y.ranknum <= x.ranknum) As CumulativeSum,
x.RANKNUM
from dbo.ledger_summary x
) b
ON a.GBSLEDGER = b.GBSLEDGER
AND a.ranknum = (b.ranknum+1)
November 2, 2011 at 10:08 am
I tired that but gave the below error:
Msg 157, Level 15, State 1, Line 100
An aggregate may not appear in the set list of an UPDATE statement.
This is the reply to: Performance Guard (Shehap)'s solution
November 2, 2011 at 12:06 pm
getusama-1105611 (11/2/2011)
I tired that but gave the below error:Msg 157, Level 15, State 1, Line 100
An aggregate may not appear in the set list of an UPDATE statement.
This is the reply to: Performance Guard (Shehap)'s solution
Shehap's answer to everything is to create an index. He doesn't take into account any other factors that affect performance. For example, in this case, he is using hidden RBAR when it's likely that a "quirky update"[/url] will be able to produce the correct results much more efficiently.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply