Conditional UPDATE statement
-
Any thought on how to accomplish this?
Two tables,
1. #temp holds transaction amounts and Fiscal_Period of the transaction
2. Acct_Balance holds account balances for each Fiscal_Period
I want to update the correct balance in Acct_Balance with the Trans_Amount from #temp.
Something like this ...
UPDATE dbo.Acct_Balance
CASE t.Fiscal_Period
WHEN 1 THEN SET b.Period_01_Balance = b.Period_01_Balance + t.Trans_Amount
WHEN 2 THEN SET b.Period_02_Balance = b.Period_01_Balance + t.Trans_Amount
END
FROM #temp t JOIN Acct_Balance b
ON t.Acct_ID = b.Acct_ID AND
t.Fiscal_Yr_Ending = b.Fiscal_Year
This does not compile. Is it possible to do this without a cursor?
Thanks!
jmatt
-
could you do something like
SET b.Period_01_Balance = b.Period_01_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 1 THEN 1 ELSE 0 END),
b.Period_02_Balance = b.Period_02_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 2 THEN 1 ELSE 0 END)
...assuming the second update was meant to say
b.Period_02_Balance = b.Period_02_Balance + ...
-
That's brilliant!
I will give it a try and psot the final code.
Thank you!
jmatt
-
I think I am half way there ...
Here is my code
CREATE TABLE #temp
(
Fiscal_Yr_Ending int,
Fiscal_Period int,
Trans_Amount money,
Acct_ID int
)
INSERT #temp
(
Fiscal_Yr_Ending,
Fiscal_Period,
Trans_Amount,
Acct_ID
)
SELECT
t.Fiscal_Yr_Ending,
t.Fiscal_Period,
CASE WHEN tl.Trans_Type = 'D' THEN (tl.Trans_Amount * -1) ELSE tl.Trans_Amount END 'Trans_Amount',
tl.Acct_ID
FROM
Transactions t JOIN Transactions_Line tl
ON t.Trans_ID = tl.Trans_ID
--Update Balances
UPDATE b
SET
b.Period_01_Balance = b.Period_01_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 1 THEN 1 ELSE 0 END),
b.Period_02_Balance = b.Period_02_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 2 THEN 1 ELSE 0 END),
b.Period_03_Balance = b.Period_03_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 3 THEN 1 ELSE 0 END),
b.Period_04_Balance = b.Period_04_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 4 THEN 1 ELSE 0 END),
b.Period_05_Balance = b.Period_05_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 5 THEN 1 ELSE 0 END),
b.Period_06_Balance = b.Period_06_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 6 THEN 1 ELSE 0 END),
b.Period_07_Balance = b.Period_07_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 7 THEN 1 ELSE 0 END),
b.Period_08_Balance = b.Period_08_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 8 THEN 1 ELSE 0 END),
b.Period_09_Balance = b.Period_09_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 9 THEN 1 ELSE 0 END),
b.Period_10_Balance = b.Period_10_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 10 THEN 1 ELSE 0 END),
b.Period_11_Balance = b.Period_11_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 11 THEN 1 ELSE 0 END),
b.Period_12_Balance = b.Period_12_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 12 THEN 1 ELSE 0 END)
FROM #temp t JOIN dbo.Acct_Balance b
ON t.Acct_ID = b.Acct_ID AND t.Fiscal_Yr_Ending = b.Fiscal_Year
DROP TABLE #temp
My problem is that if an account has more than one Trans_Amount in same Fiscal_Year only the first Trans_Amount is added to the balance.
Any thoughts?
-
How about :
alter the insert statement :
INSERT #temp
(
Fiscal_Yr_Ending,
Fiscal_Period,
Trans_Amount,
Acct_ID
)
SELECT
t.Fiscal_Yr_Ending,
t.Fiscal_Period,
sum( CASE WHEN tl.Trans_Type = 'D' THEN (tl.Trans_Amount * -1)
ELSE tl.Trans_Amount END ) Trans_Amount,
tl.Acct_ID
FROM
Transactions t JOIN Transactions_Line tl
ON t.Trans_ID = tl.Trans_ID
group by t.Fiscal_Yr_Ending, t.Fiscal_Period, tl.Acct_ID
OR alter the Update statement :
--Update Balances
UPDATE b
SET
b.Period_01_Balance = b.Period_01_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 1 THEN 1 ELSE 0 END),
b.Period_02_Balance = b.Period_02_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 2 THEN 1 ELSE 0 END),
b.Period_03_Balance = b.Period_03_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 3 THEN 1 ELSE 0 END),
b.Period_04_Balance = b.Period_04_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 4 THEN 1 ELSE 0 END),
b.Period_05_Balance = b.Period_05_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 5 THEN 1 ELSE 0 END),
b.Period_06_Balance = b.Period_06_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 6 THEN 1 ELSE 0 END),
b.Period_07_Balance = b.Period_07_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 7 THEN 1 ELSE 0 END),
b.Period_08_Balance = b.Period_08_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 8 THEN 1 ELSE 0 END),
b.Period_09_Balance = b.Period_09_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 9 THEN 1 ELSE 0 END),
b.Period_10_Balance = b.Period_10_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 10 THEN 1 ELSE 0 END),
b.Period_11_Balance = b.Period_11_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 11 THEN 1 ELSE 0 END),
b.Period_12_Balance = b.Period_12_Balance + t.Trans_Amount * (CASE t.Fiscal_Period WHEN 12 THEN 1 ELSE 0 END)
FROM dbo.Acct_Balance b
inner join (select Fiscal_Yr_Ending,
Fiscal_Period,
Acct_ID, sum(Trans_Amount) as Trans_Amount
from #temp
group by Fiscal_Yr_Ending, Fiscal_Period, Acct_ID ) t
ON t.Acct_ID = b.Acct_ID
AND t.Fiscal_Yr_Ending = b.Fiscal_Year
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Great! Used your first suggestion and consolidated in the temp table. Things are working perfectly now.
Thanks for your help!
jmatt
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply