Here's my SELECT:
[date] = case when type = 'c' then dos else posted end,
pfirst+' '+plast as patientname,
LTRIM( case when type = 'a' then payerdesc when type = 'p' then payerdesc when type = 'c' then ISNULL(cpt, '') +' - '+ ISNULL(left(cptdesc,100),'') end ) as description,
'Medical Oncology' as physicianid,
coalesce(cg.encountercode, '') as practclaimid,
case when type = 'c' then 0 else 1 end as priority,
left(cptdesc,100) as cptdesc,
cpt as cpt,
(SELECT sum(amount) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid) as balance,
(SELECT min(dos) from txn where t.chargegroupid = txn.chargegroupid and t.accountid = txn.accountid and type = 'c') as firstdos
FROM txn t
chargegroup cg on t.chargegroupid = cg.id
WHERE t.accountid = 9949388
AND (cg.status1 not in ('rt','hd','rp','fc') or cg.status1 is null)
AND cg.id in ( SELECT chargegroupid from txn where accountid = 9949388
chargegroupid having max(txn.created) > DATEADD(DAY, -365, GETDATE()) )
reversedref is null
The results are attached.
What I need is included in the attached spreadsheet. You'll see it represented beneath the "Here's what I need" row. Bottom line: I need to group the "C" transaction type as a SUM of whatever it is that is categorized under the same "practclaimid." Ultimately, I need to display these row with the charges (sum of the charges) displayed at the top and I'm thinking I can do that by ordering by "amount," give the fact that a "charge" is always going to be a positive value.
How would I do this?