Financial Query Help

I am in need of a query that would calculate the balance as of a certain
date, say 12/31/2005 for AccountID = 1.
Current Balance = 100
Balance as of 12/31/05 should be 200.
But how do I write a query like this?
Thanks
Sonny
tblAccount - Shows current balances
ID--Balance--AccountName
1 -- 100 -- Test1
2 -- 200 -- Test2
3 -- 200 -- Test3
tblAccountHistory - Show transactions
ID--AccountID--TransDate--Amount--Action
1 -- 1 -- 12/1/2005 -- 500 -- Credit
1 -- 1 -- 12/5/2005 -- 100 -- Debit
1 -- 1 -- 12/10/2005 -- 100 -- Debit
1 -- 1 -- 12/15/2005 -- 100 -- Debit
1 -- 1 -- 1/5/2006 -- 100 -- DebitSonny
Can you post sample data?
SELECT Current Balance *2 FROM Table WHERE AccountID = 1
"Sonny Sablan" <sonny@.sablan.org> wrote in message
news:ODbjNxnQGHA.2536@.tk2msftngp13.phx.gbl...
>I am in need of a query that would calculate the balance as of a certain
>date, say 12/31/2005 for AccountID = 1.
> Current Balance = 100
> Balance as of 12/31/05 should be 200.
> But how do I write a query like this?
> Thanks
> Sonny
>
> tblAccount - Shows current balances
> ID--Balance--AccountName
> 1 -- 100 -- Test1
> 2 -- 200 -- Test2
> 3 -- 200 -- Test3
>
> tblAccountHistory - Show transactions
> ID--AccountID--TransDate--Amount--Action
> 1 -- 1 -- 12/1/2005 -- 500 -- Credit
> 1 -- 1 -- 12/5/2005 -- 100 -- Debit
> 1 -- 1 -- 12/10/2005 -- 100 -- Debit
> 1 -- 1 -- 12/15/2005 -- 100 -- Debit
> 1 -- 1 -- 1/5/2006 -- 100 -- Debit
>
>|||There is sample data...
After my question.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OUERjZoQGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Sonny
> Can you post sample data?
> SELECT Current Balance *2 FROM Table WHERE AccountID = 1
>
>
>
> "Sonny Sablan" <sonny@.sablan.org> wrote in message
> news:ODbjNxnQGHA.2536@.tk2msftngp13.phx.gbl...
>|||he did. ;-)
select AccountID, sum(Amount*(case Action when 'Credit' then 1 when 'Debit'
then -1 else 0 end)) [total]
from tbAccountHistory
where AccountID=1 and TransDate <= '20051231'
group by AccountID
-oj
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OUERjZoQGHA.4952@.TK2MSFTNGP09.phx.gbl...
> Sonny
> Can you post sample data?
> SELECT Current Balance *2 FROM Table WHERE AccountID = 1
>
>
>
> "Sonny Sablan" <sonny@.sablan.org> wrote in message
> news:ODbjNxnQGHA.2536@.tk2msftngp13.phx.gbl...
>|||Thanks oj...
Sonny
"oj" <nospam_ojngo@.home.com> wrote in message
news:u6heUeoQGHA.4960@.TK2MSFTNGP12.phx.gbl...
> he did. ;-)
> select AccountID, sum(Amount*(case Action when 'Credit' then 1 when
> 'Debit' then -1 else 0 end)) [total]
> from tbAccountHistory
> where AccountID=1 and TransDate <= '20051231'
> group by AccountID
> --
> -oj
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OUERjZoQGHA.4952@.TK2MSFTNGP09.phx.gbl...
>|||Thanks, I really need a cofee.
"oj" <nospam_ojngo@.home.com> wrote in message
news:u6heUeoQGHA.4960@.TK2MSFTNGP12.phx.gbl...
> he did. ;-)
> select AccountID, sum(Amount*(case Action when 'Credit' then 1 when
> 'Debit' then -1 else 0 end)) [total]
> from tbAccountHistory
> where AccountID=1 and TransDate <= '20051231'
> group by AccountID
> --
> -oj
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OUERjZoQGHA.4952@.TK2MSFTNGP09.phx.gbl...
>