I have the following query from a Ledger table in my accounting database:
select lmatter
, ltradat
, llcode
, lamount
, lperiod
from ledger
where lmatter = '1234-ABCD'
and lzero <> 'R'
order by lperiod
The results are:
From this, I want to know if it is possible to create a final result of:
The way I got these #'s is as follows:
We are working under the assumption that today is December 1st, 2015 and we are billing for November 2015 data. Which makes the lperiod we're working with as "current" equal to '1115'
- Matter ID is a distinct lmatter
- Prev. Bal is SUM(FEES + HCOST) - SUM(PAY) where lperiod <> '1115'
- Payments is SUM(PAY) where lperiod = '1115'
- Current Charges is SUM(FEES + HCOST) where lperiod = '1115'
- Amount Due is Prev.Bal - Payments + Current Charges
Is this possible under one query with use of sub-queries, or possibly even the use of a couple temp tables?

