SQL Subquery with SUMs

Question

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?

Answer 1

use case
you can build up more advanced logic
for Amount Due I would have nested query

select *, prev - pay + current as 'DUE'
from 
(
select  lmatter
      , sum(case when lperiod <> '1115' and llcode in ('FEES', 'HCOST')  then  lamount  
                 when lperiod <> '1115' and llcode = 'PAY'               then -lamount   
            end) as 'prev'
      , sum(case when lperiod  = '1115' and llcode = 'PAY'                then  lamount  
            end) as 'payment' 
      , sum(case when lperiod  = '1115' and llcode in ('FEES', 'HCOST')  then  lamount     
            end) as 'current'             

from    ledger  
where   lmatter = '1234-ABCD'
and     lzero <> 'R'
group by by lmatter 
) as tt