SQL Pivot--returning the same values in each pivot entry

CMCITD
CMCITD used Ask the Experts™
on
WITH DATA 
AS (
select CAST(enc_timestamp AS DATE) as [Date of Drug Administration], p.person_nbr AS [Medical Record], enc_nbr AS [Encounter Number],
'OP' as [Patient Type], pp.service_item_id as [Charge Code], pp.service_item_desc AS [Charge Description], pp.units AS [Dispensed Quantity],
pm.payer_name, cob from patient_encounter pe
JOIN person p ON p.person_id = pe.person_id
JOIN encounter_payer ep ON pe.person_id = ep.person_id AND pe.enc_id = ep.enc_id
JOIN payer_mstr pm ON pm.payer_id = pe.cob1_payer_id
JOIN patient_procedure pp ON pp.enc_id = pe.enc_id
where CAST(enc_timestamp AS DATE) >= dateadd(day,datediff(day,365,GETDATE()),0) 
and pe.billable_ind = 'Y' and pe.clinical_ind = 'Y' and pe.practice_id = '0001'
and pp.service_item_id IN ( 'J0585','J0696','J0702','J1030','J1040','J1050','J1071','J1100','J1200','J1650','J1885','J2060','J2175','J2270','J2300',
							'J2405','J2550','J2791','J2930','J3030','J3301','J3420','J3430','J3490G','J3490TH','J7298','J7613','J7620'
							)
and pp.amount <> 0.00 and pp.delete_ind = 'N')
select * 
from DATA d
	Pivot (MAX(payer_name)
			FOR cob IN ([1], [2], [3] )) as P;

Open in new window



This is working---however the payer name is the same in 1, 2 or 3 (depending on the patient's number of insurances).  For example, if a patient has Medicare and a supplement--it will at least recognize there are two entries (cob 1 and cob 2)--but instead of the supplemental name, it is supplying the same insurance name (Medicare) for both entries.  If the patient has 3 insurnaces, then it would have 3 Medicare names across 1, 2 and 3.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I realize this is private data, but without seeing any data at all makes it very hard to assist you.

If you change to using MIN() do you get a different result?

SELECT
    *
FROM data d
PIVOT (
             MIN(payer_name) FOR cob IN ([1], [2], [3])
            ) AS p;

Which table does [cob] come from? (In the "data" query there is no table prefix used for [cob])

Can you provide any sample data? (private info can be altered or omitted)

Author

Commented:
Sorry Paul, been tied up with other reports--I will get an example of data to you tomorrow

Author

Commented:
Ok tried min--same behavior.  It's picking up the first insurance--and then just listing it for every available insurance (if they have multiple).  See attached2018_09_19_08_52_27_Book2_Excel.png
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Forgot to address your cob question.  

COB comes from the encounter_payer table.  For example the following query;

select cob, pm.payer_name, enc_id from encounter_payer ep
JOIN payer_mstr pm ON pm.payer_id = ep.payer_id
order by enc_id, cob asc

Open in new window


Will produce the following;
Example-cob-order-with-multiple-paye.png

So my original query is recognizing the fact that a particular patient encounter has more than one insurance (multiple rows same encounter, different cob).  However, instead of pulling the cob 2 or cob 3 insurance name--it's just populating all cobs it recognizes as existing with the first insurance name.  Hope this helps you help me :).
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
Even in little queries, the discipline of prefixing EVERY column references is necessary:

select ep.cob, pm.payer_name, ep.enc_id from encounter_payer ep
JOIN payer_mstr pm ON pm.payer_id = ep.payer_id

I am going to suggest the following, note I have used ??????. where there is a missing column prefix
SELECT
    CAST(??????.enc_timestamp AS date) AS [Date of Drug Administration]
  , p.person_nbr AS [Medical Record]
  , ?????.enc_nbr AS [Encounter Number]
  , 'OP' AS [Patient Type]
  , pp.service_item_id AS [Charge Code]
  , pp.service_item_desc AS [Charge Description]
  , pp.units AS [Dispensed Quantity]
  , ep.[1]
  , ep.[2]
  , ep.[3]
FROM patient_encounter pe
JOIN person p ON p.person_id = pe.person_id
JOIN (
    SELECT
        ep.enc_id
      , MAX(CASE WHEN ep.cob = 1 THEN pm.payer_name END) AS [1]
      , MAX(CASE WHEN ep.cob = 2 THEN pm.payer_name END) AS [2]
      , MAX(CASE WHEN ep.cob = 3 THEN pm.payer_name END) AS [3]
    FROM encounter_payer ep
    JOIN payer_mstr pm ON pm.payer_id = ep.payer_id
    GROUP BY
        ep.enc_id
    ) AS ep ON pe.enc_id = ep.enc_id
JOIN patient_procedure pp ON pp.enc_id = pe.enc_id
WHERE CAST(enc_timestamp AS date) >= DATEADD(DAY, DATEDIFF(DAY, 365, GETDATE()), 0)
AND pe.billable_ind = 'Y'
AND pe.clinical_ind = 'Y'
AND pe.practice_id = '0001'
AND pp.service_item_id IN ('J0585', 'J0696', 'J0702', 'J1030', 'J1040'
                        , 'J1050', 'J1071', 'J1100', 'J1200', 'J1650'
                        , 'J1885', 'J2060', 'J2175', 'J2270', 'J2300'
                        , 'J2405', 'J2550', 'J2791', 'J2930', 'J3030'
                        , 'J3301', 'J3420', 'J3430', 'J3490G'
                        , 'J3490TH', 'J7298', 'J7613', 'J7620'
                        )
AND pp.amount <> 0.00
AND pp.delete_ind = 'N'
;

Open in new window

In that query I have opted to use a more traditional "pivot" which I believe will help document exactly what is going on. To expand the number of columns for [1],[2],[3]  there is a reduction of rows implied as well, in the traditional form we use GROUP BY and case expressions inside the aggregate function MAX() to achieve this effect.

When using the "PIVOT" feature, it does this for you with less SQL coding, but it is effectively still doing the same stuff.

So, when using GROUP BY or PIVOT consider doing this BEFORE joining to other tables, it is far easier (and often more efficient) to control the outcome of a small subquery than it is to debug a much larger single query.

[+edit]
Note also, I don't believe in using WITH merely as a way to "improve readability". To me it does not actually do that at all.

This, I know, is a personal preference, but the added reason for disliking overuse of WITH is that it implies that the query is executed in a linear logic  fashion, and this just isn't true.

Author

Commented:
That worked, you're an ace Paul!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial