Here's my current SELECT statement that works great, as far as giving me the data that I need:
'Charge' AS txntype,
dos as date,
pfirst+' '+plast as patientname,
'charge to account' AS description,
SUM(amount) AS amount,
'Medical Oncology' AS physicianid,
encountercode as practclaimid,
'0' as priority,
'' AS cpt,
'' AS toppriority,
'' AS balance,
dos AS firstdos
GROUP BY encountercode, dos, pfirst+' '+plast
having max(txn.created) > DATEADD(DAY, -45, GETDATE())
LTRIM( case when type = 'a' then 'Adjustment' when type = 'p' then 'Payment' when type = 'c' then 'Charge' end ) as txntype,
cpt, case when practclaimid = '' then 1 else 0 end as toppriority,
[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
txn t left join chargegroup cg on t.chargegroupid = cg.id
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
GROUP BY chargegroupid having max(txn.created) > DATEADD(DAY, -45, GETDATE()) ) and reversedref is NULL
ORDER BY practclaimid, priority ASC, date
The attached spreadsheet shows you the resulting recordset and it is exactly what I need.
However, if you look at what I've attached you'll see a row highlighted in red. The reason it constitutes a problem is because I sorted according to the practclaimid in order to batch those Claim IDs together, the date of that claim gets lost and instead of each claimid being presented in chronological order, you get a jumbled up result and you can see that where I've got the row in red.
Each charge is highlighted in grey and it's going along fine right up to the point where you get to row #47. The date of that claim is May 11th, The date of the claim just before it is October 19th.
I need to group the practclaimids together and then order those according to priority because of the way that positions the charges above the adjustments and payments.
But - and here's the challenge - I need to arrange each of those "groupings" of practclaimids and their corresponding charges, adjustments and payments - chronologically.
I was poking around Google and I found this article about setting up a temp table (http://blog.sqlauthority.com/2012/10/30/sql-server-union-all-and-order-by-how-to-order-table-separately-while-using-union-all/
), and that looked like it might work, but I want some other eyes on this.
I can't see any way to sort what I have as it's coming out of the chute. But even with a temp table, I'm not sure how I can INSERT and then SELECT those same rows in a way where it would solve my problem.
But I'll bet there's some ninja out there who's been down this road before who can help me out.