Calculate balance for activity report

Question

Post reply

Calculate balance for activity report

Sandi Antono

SSC Enthusiast

Points: 174
More actions
May 31, 2006 at 3:21 pm

#114386

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

Here's one way, using pure SQL. I've had to guess at your table structures and some other things.

select

c.FullName

,

sp.StartDate Date

,

cast('Opening Balance' as varchar(1000)) Description

,

cob.Amount

,

cob.Amount Balance

from

CustOpeningBal cob

join

SalesPeriod sp

on

cob.SalesPeriodID = sp.SalesPeriodID

join

Customer c

on

cob.CustID = c.CustID

where

sp.StartDate = '2006-01-31 00:00:00'

union

all

select

c.FullName, t.Date, t.Description, t.Amount, sum(t2.amount)+cob.amount

from

Transactions t

join

Customer c

on t.CustID = c.CustID

join

CustOpeningBal cob on cob.CustID = t.CustID

join

SalesPeriod sp on cob.SalesPeriodID = sp.SalesPeriodID

--use date matching if the sales period is not recorded for each transaction:

and t.Date >= sp.StartDate

and t.Date < sp.EndDate --assuming you have a denormalised end date

left

join Transactions t2 on t2.CustID = t.CustID

and t2.Date >= sp.StartDate

and t2.Date <= t.Date

and t2.TransactionSerialNo <= t1.TransactionSerialNo

--don't know what fields you have available to order the transactions by time...

where

sp.StartDate = '2006-01-31 00:00:00'

--? and t.StartDate < select floor(cast(getdate() as float))

-- --(only show trans up to end of yesterday)

group

by c.FullName, t.Date, t.Description, t.Amount

order

by FullName, case Description when 'Opening Balance' then 0 else 1 end, Date

If you could supply more info about your tables, it would help.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

JeffB Hall of Fame Points: 3541 More actions · Answer 2

This will work but I don't know how it will perform with a large number of rows in the transaction table.

DECLARE @begbal TABLE (customer varchar(20), balance int, baldate datetime)

insert @begbal VALUES ('mr. x', 100, '31 Jan 2006')

insert @begbal Values('mr. yy', 250, '31 Jan 2006')

DECLARE @transaction TABLE (customer varchar(20), trandate datetime, description varchar(20), amount int)

INSERT @transaction values('mr. x', '05 feb 2006', 'sales', 200)

INSERT @transaction values('mr. x', '08 feb 2006', 'sales again', 500)

INSERT @transaction values('mr. x', '12 feb 2006', 'next sales', 100)

INSERT @transaction values('mr. x', '15 feb 2006', 'another sales', 600)

INSERT @transaction values('mr. yy', '05 mar 2006', '2 piece', 200)

INSERT @transaction values('mr. yy', '08 apr 2006', 'just one piece', 500)

SELECT b.baldate, 'Beginning balance', null, balance

FROM @begbal b

WHERE b.customer = 'mr. x'

UNION ALL

SELECT t.trandate, t.description, t.amount, b.balance + x.runningbal

FROM @begbal b

inner join @transaction t

on b.customer = t.customer

INNER JOIN (select a.customer, a.trandate, sum(b.amount) runningbal

FROM @transaction a

INNER JOIN @transaction b

on b.trandate <= a.trandate

GROUP BY a.customer, a.trandate) x

ON t.customer = x.customer AND t.trandate = x.trandate

WHERE b.customer = 'mr. x'

ORDER BY 1