Solved

How would I do an ORDER BY with this UNION of two SELECT Statements?

Posted on 2016-11-03
9
67 Views
1 Endorsement
Last Modified: 2016-11-04
Here's my current SELECT statement that works great, as far as giving me the data that I need:

SELECT
 '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
 FROM [PCAR_Data].[dbo].[txn]
 WHERE
 type='C'
 AND
 accountid=9949388
 GROUP BY encountercode, dos, pfirst+' '+plast
 having max(txn.created) > DATEADD(DAY, -45, GETDATE())
 
 UNION

SELECT
LTRIM( case when type = 'a' then 'Adjustment' when type = 'p' then 'Payment' when type = 'c' then 'Charge' end ) as txntype, 
[date], 
patientname, 
description, 
amount, 
physicianid, 
practclaimid, 
priority, 
cpt, case when practclaimid = '' then 1 else 0 end as toppriority, 
balance, 
firstdos
FROM 
	( 
	SELECT 
	paymentmethod, 
	payerdesc, 
	type, 
	[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, 
	amount,
	'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 left join chargegroup cg on t.chargegroupid = cg.id 
	 WHERE
	 type<>'C'
	 AND
	 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 
	 ) u 

 ORDER BY practclaimid, priority ASC, date

Open in new window


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.

Thoughts?
experts_exchange.xlsx
1
Comment
Question by:brucegust
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41872594
Change the ORDER BY accordingly.  You might need to add a special column with a sorting-only value.  In that case, you can add an outer query to do the sort while not listing the sort-only column.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41872597
You need order by which columns ?

Give us 2-3 rows and their expected output.

May be this ...

SELECT
 '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
 ,1 AS Ors
 FROM [PCAR_Data].[dbo].[txn]
 WHERE
 type='C'
 AND
 accountid=9949388
 GROUP BY encountercode, dos, pfirst+' '+plast
 having max(txn.created) > DATEADD(DAY, -45, GETDATE())
 
 UNION

SELECT
LTRIM( case when type = 'a' then 'Adjustment' when type = 'p' then 'Payment' when type = 'c' then 'Charge' end ) as txntype, 
[date], 
patientname, 
description, 
amount, 
physicianid, 
practclaimid, 
priority, 
cpt, case when practclaimid = '' then 1 else 0 end as toppriority, 
balance, 
firstdos
,2 AS Ors
FROM 
	( 
	SELECT 
	paymentmethod, 
	payerdesc, 
	type, 
	[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, 
	amount,
	'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 left join chargegroup cg on t.chargegroupid = cg.id 
	 WHERE
	 type<>'C'
	 AND
	 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 
	 ) u 

 ORDER BY practclaimid, priority ASC, date, Ors

Open in new window

0
 
LVL 11

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 125 total points
ID: 41872607
The solution from SQLAuthority should work. Simple reason being that ORDER BY is evaluated after the final result set has been built (considering all the SELECTs and the UNIONs).

If I am reading your requirement correctly, you need the output to be primarily grouped by the practclaimid. Within each group, the sort should be by the following:
1. Sort Key Pair1 = All "Charges" ordered by date
2. Sort Key Pair2 = All "Adjustments" ordered by date
3. Sort Key Pair3 = All "Payments" ordered by date

The Excel sheet appears to be correct if the above interpretation is true. If not, can you please provide a sample expected output?
0
 

Author Comment

by:brucegust
ID: 41872655
Gentlemen!

The requested spreadsheet is attached. I've got two worksheets; one entitled, "present" which represents the current recordset and another entitled "desired," which is the order that I need the rows to be.

Nakul, to answer your question specifically:

Charges ordered by date
 
  •    all corresponding "Adjustments" and Payments" that have the same practclaimid listed beneath  that charge

Pawan, I'm not familiar with the ",2 ors" dynamic that you're suggesting. That could be a winner if it results in not having to create a temp table.

Hopefully with the added explanation and spreadsheet, y'all will be able to better approach my quandary.

Thank you for your time!
experts_exchange_new.xlsx
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 125 total points
ID: 41872677
Try..

SELECT
 '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
 FROM [PCAR_Data].[dbo].[txn]
 WHERE
 type='C'
 AND
 accountid=9949388
 GROUP BY encountercode, dos, pfirst+' '+plast
 having max(txn.created) > DATEADD(DAY, -45, GETDATE())
 
UNION

SELECT
LTRIM( case when type = 'a' then 'Adjustment' when type = 'p' then 'Payment' when type = 'c' then 'Charge' end ) as txntype, 
[date], 
patientname, 
description, 
amount, 
physicianid, 
practclaimid, 
priority, 
cpt, case when practclaimid = '' then 1 else 0 end as toppriority, 
balance, 
firstdos
FROM 
( 
	SELECT 
	paymentmethod, 
	payerdesc, 
	type, 
	[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, 
	amount,
	'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 left join chargegroup cg on t.chargegroupid = cg.id 
	 WHERE
	 type<>'C'
	 AND
	 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 
) u 

ORDER BY date, patientname , description , practclaimid, priority

Open in new window

0
 

Author Comment

by:brucegust
ID: 41872715
Hey, Pawan!

Take a look at the screenshot below.

The practclaimids have to be grouped together.

The very first charge ias a practclaimed of MCE557787A. The payment on line 10 needs to be on line 2 and that needs to be followed by the adjustment on line 5.

You can mix and match the adjustments and the payments as much as you like, but they HAVE to be grouped with their corresponding charge.

What do you think?
0
 

Author Comment

by:brucegust
ID: 41872720
Another thing, guys!

This SELECT is being called by a PHP page. I just tested a script that establishes a temp table and I don't have the necessary permissions to do that, so...

Can you see a way to order the results without having to use a temp table?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
ID: 41872752
Again:

Change the ORDER BY to match the specific sequence you need. [The ORDER BY is applied to the already-UNIONed data.]  You might need to add a special column with a sorting-only value.  In that case, you can add an outer query to do the sort while not listing the sort-only column.
0
 

Author Comment

by:brucegust
ID: 41874084
Scott!

I appreciate your weighing in. I woke up this am thinking, "How am I going to pull this off without a temp table?" and your comment was waiting in my inbox. So, I'm stoked thinking there's a way around the temp table thing.

The problem is that I HAVE to sory by my practclaimid's first and foremost in order to get them to display together and then I throw in the "priority" dynamic to get my "charge" to show up first within the group of practclaimids:

order by practcalimid, priority
But you'll notice on line 46 everything goes south. I've got everything the way that I need it, but all of sudden I go from October 2016 to May 2016.

If I change my ORDER BY to date, practclaimid, priority, I get this:

....

Wait a minute...

Scott, do you hear that? That's the sound of an orchestral string section swelling to magnificent major chord! And...light bulb!

You were right. The challenge however was that the date associated with the practclaimid was not always in keeping with what translated to a truly chronological order. That's what kept the solution from being intuitive.

But there's another field called "firstdos," which stands for first date of service. That date is attached to the charge, the adjustment and the payment. The date, on the other hand was different depending on when the customer was charged, when the insurance company responded and when the customer made a payment. Hence, the ball of snot.

But with firstdos, everything lines up exactly as it needs to!

Thanks for your patience and everyone else for their insight!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now