Calculate Net Change in Previous Day Balance

[sktravers]

I want to create a calculated field that compares results between two records.

When I run the following query:

declare @BusDate as datetime
set @BusDate = '11/11/2009'

select @BusDate as Busdate,
'1600-02-' + AcctgCAcctGLMap.GLCode + '-' +
AcctgCurr.GLCurrCode as GLaccount,
0 as DebitPostval, vw_SelectFBalance.Lval as CreditPostVal,
'Bal:LValue' as Desc
from
vw_SelectFBalance Bal,
AcctgCAcctGLMap ACAG,
AcctgCurr Curr
where Bal.CFirm = ACAG.CFirm and Bal.Account = ACAG.CAcct
and Bal.BalanceCurrency = Curr.CurrencyCode
and OfficeCode <> 'RPT'
and BusinessDate = @BusDate
and Lval > 0



I get the following results:

Busdate GLaccount DebitPostval CreditPostVal Desc
2009-11-11 1600-02-F0-00 0 58470.00 Bal:LValue
2009-11-11 1600-02-F1-00 0 7930.00 Bal:LValue
2009-11-11 1600-02-F3-00 0 22186.60 Bal:LValue

What I want to do is pull the same information from the previous day, subtract the LVal balance from the LVal for the current day, and create a new field called NetChange. If the data for the previous day is as follows:

Busdate GLaccount DebitPostval CreditPostVal Desc
2009-11-11 1600-02-F0-00 0 8470.00 Bal:LValue
2009-11-11 1600-02-F1-00 0 7000.00 Bal:LValue
2009-11-11 1600-02-F3-00 0 23000.00 Bal:LValue

My results should be:

Busdate GLaccount Net Change Desc
2009-11-11 1600-02-F0-00 0 8470.00 Bal:LValue
2009-11-11 1600-02-F1-00 0 930.00 Bal:LValue
2009-11-11 1600-02-F3-00 0 -813.40 Bal:LValue

I think I need to join by GLaccount but need help with the syntax.

[HENARRY JAYA]

A very warm welcome to you....

This is my second post in this forum, I really enjoyed the way existing members are greeting new members....

It's really a very nice and interactive forum, I feel great I landed here....

Regards,