Payments: Open AR by Client

Description: This query finds all the unpaid late fees and invoices by client. Simply edit the c.key field with the client number in question, or null said line to get all AR by client.

This query is useful in comparing what we show as open to what a firm shows as open.

Query: 

select c.key, c.name,
       SUM(case when a.type = 'LateFee' then (amount-allocated) else 0 end) as "Late Fee",
       SUM(case when a.type = 'Invoice' then (amount-allocated) else 0 end) as "Invoice",
       SUM(amount-allocated) as "Total"
from ar_tran a
    inner join client c on c.id = a.client_id
where type in ('Invoice','LateFee')
  and amount > allocated
  and amount <> 0
  --and c.key = '5309'
Group by c.key, c.name, c.id