May 31, 2006 at 3:44 pm
I need to make activity report like below:
CUSTOMER : mr. X
description amount Balance
31 jan 2006 beggining balance 100
05 feb 2006 sales 200 300
08 feb 2006 sales again 500 800
12 feb 2006 next sales 100 900
15 feb 2006 another sales 600 1500
i have table for retrieve beggining balance like below:
customer beg balance
mr. x 100
mr. yy 250
so on....
and table transaction...
customer date description amount
mr. x 05 feb 2006 sales 200
mr. x 08 feb 2006 sales again 500
mr. x 12 feb 2006 next sales 100
mr. x 15 feb 2006 another sales 600
mr. yy 05 mar 2006 2 piece 200
mr. yy 08 apr 2006 just one piece 500
so on....
please give some advice, how do i calculate balance with t-sql for activity report
Thank's dan Best Regards,
Sandi Antono
June 1, 2006 at 7:09 am
It's a running summary issue.The following code is an sample:
DECLARE @Balance TABLE (RID int identity not null, [description] nvarchar(255) not null, [date] datetime not null, amount in tnot null, balance int not null)
-- Insert initial balnace for the customer
INSERT @Balance ([description],[date],amount,balance)
VALUES ('Initial balance','Jan 31 2006',0,@InitBalance
-- load the transaction for the user in the time order
INSERT @Balance ([description],[date],amount,balance)
SELECT ([description],[date],amount,balance)
FROM Transaction
WHERE Customer='Mr.x'
ORDER BY Date -- or by TransactionID if you have
-- Update running summary (balance)
UPDATE A SET Balance=@InitBalance+(SELECT SUM(amount) FROM @Balance B WHERE B.RID<=A.RID)
FROM @Balance A
Now you can output your report.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply