Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sktravers
Starting Member
1 Post |
 Posted - 2009-11-18 : 14:38:04
|
| 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 Desc2009-11-11 1600-02-F0-00 0 58470.00 Bal:LValue2009-11-11 1600-02-F1-00 0 7930.00 Bal:LValue2009-11-11 1600-02-F3-00 0 22186.60 Bal:LValueWhat 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 Desc2009-11-11 1600-02-F0-00 0 8470.00 Bal:LValue2009-11-11 1600-02-F1-00 0 7000.00 Bal:LValue2009-11-11 1600-02-F3-00 0 23000.00 Bal:LValueMy results should be:Busdate GLaccount Net Change Desc2009-11-11 1600-02-F0-00 0 8470.00 Bal:LValue2009-11-11 1600-02-F1-00 0 930.00 Bal:LValue2009-11-11 1600-02-F3-00 0 -813.40 Bal:LValueI think I need to join by GLaccount but need help with the syntax. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-11-22 : 00:50:42
|
| which is date field on your table? also how you got 8470 as balance for first record? |
 |
|
|
|
|
|
Trying to calculate net change from two records