SQL Subquery with SUMs

Viewed 88

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:

enter image description here

From this, I want to know if it is possible to create a final result of:

enter image description here

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?

1 Answers
Related