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

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.
CMCITDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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)
0
CMCITDAuthor Commented:
Sorry Paul, been tied up with other reports--I will get an example of data to you tomorrow
0
CMCITDAuthor 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
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

CMCITDAuthor 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 :).
0
PortletPaulEE Topic AdvisorCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CMCITDAuthor Commented:
That worked, you're an ace Paul!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.