I create a count of appointment dates with the most recent date's value. This will be a portion of a larger query for where to find all patients in the system who were first seen over a year ago, have 2 or more visits in the current calendar year, and what the insurance/payor was on their most recent visit. This query is just focusing on the 2+ visits in the calendar year and the insurance on the most recent visit part.

I have a query that is almost there, but due to the required grouping, it separates out the sum of appointments based on the payor:

select
      tab.PrimaryMrn
      ,sum(tab.Appt_Count) ct
      ,tab.Payor
from
    (
    select
        PatientDim.PrimaryMrn
        ,VisitFact.AppointmentDateKey
        ,(CASE  
            WHEN CoverageDim.PayorFinancialClass IN ('Blue Cross Commercial','Commercial') THEN 'Private' 
            WHEN CoverageDim.PayorFinancialClass IN ('Managed Care','Medicaid','Medicaid Replacement','Medicare','Medicare Replacement','Pending Medicaid','Self-Pay') THEN 'Medicaid'
            ELSE 'Other'
            END) as 'Payor'
        ,count(distinct VisitFact.EncounterKey) Appt_Count
        ,row_number() OVER (PARTITION BY PatientDim.PrimaryMrn ORDER BY VisitFact.AppointmentDateKey DESC) AS rn
    from VisitFact
    INNER JOIN PatientDim
        on VisitFact.PatientDurableKey = PatientDim.DurableKey and PatientDim.IsCurrent=1 
    INNER JOIN CoverageDim
        ON VisitFact.CoverageKey = CoverageDim.CoverageKey
    where VisitFact.AppointmentDateKey between '20230101' and '20231231'
    group by PatientDim.PrimaryMrn ,VisitFact.AppointmentDateKey ,CoverageDim.PayorFinancialClass
    ) tab
group by PrimaryMrn, tab.Payor
order by PrimaryMrn

The inner query does what I expect:

PrimaryMrn	AppointmentDateKey	Payor	Appt_Count	rn
1234567	20230724	Medicaid	1	1
1234567	20230427	Private	1	2
1234567	20230123	Private	1	3

The result I want:

PrimaryMrn	Payor	Appt_Count
1234567	Medicaid	3

'Medicaid' should be pulled in as the 'Payor' because it is associated with the most recent visit (2023-07-24), and the appointment counts should sum to the total of 3 since there were 3 visits. I will then filter this list to only include patients with 2 or more visits based on the Appt_Count field.

What my query gives:
| PrimaryMrn | Payor | Appt_Count |
| ------------ | ------------ | ------------ |
| 1234567 | Medicaid | 1 |
| 1234567 | Private | 2 |

I tried pulling by row number=1, but that removes the other visits I need to sum. I tried summing the visits first and then taking the max Payor, but that results in 'Private' since it chooses the max alphabetically.

How I can do what I'm trying to accomplish?

select
tab.PrimaryMrn
, REPLACE(max(AppointmentDateKeyPayor), max(AppointmentDateKey), '') Payor
,sum(tab.Appt_Count) ct
from (
select
PatientDim.PrimaryMrn
,VisitFact.AppointmentDateKey
,(CASE
WHEN CoverageDim.PayorFinancialClass IN ('Blue Cross Commercial','Commercial') THEN 'Private'
WHEN CoverageDim.PayorFinancialClass IN ('Managed Care','Medicaid','Medicaid Replacement','Medicare','Medicare Replacement','Pending Medicaid','Self-Pay') THEN 'Medicaid'
ELSE 'Other'
END) as 'Payor'
,CAST(VisitFact.AppointmentDateKey as VARCHAR(10))
+(CASE
WHEN CoverageDim.PayorFinancialClass IN ('Blue Cross Commercial','Commercial') THEN 'Private'
WHEN CoverageDim.PayorFinancialClass IN ('Managed Care','Medicaid','Medicaid Replacement','Medicare','Medicare Replacement','Pending Medicaid','Self-Pay') THEN 'Medicaid'
ELSE 'Other'
END) as 'AppointmentDateKeyPayor'
,count(distinct VisitFact.EncounterKey) Appt_Count
from VisitFact
INNER JOIN PatientDim
on VisitFact.PatientDurableKey = PatientDim.DurableKey and
PatientDim.IsCurrent=1
INNER JOIN CoverageDim
ON VisitFact.CoverageKey = CoverageDim.CoverageKey
where VisitFact.AppointmentDateKey between '20230101' and '20231231'
group by PatientDim.PrimaryMrn ,VisitFact.AppointmentDateKey
,CoverageDim.PayorFinancialClass
) tab
group by PrimaryMrn
order by PrimaryMrn

1条答案

按热度按时间

vxf3dgd41#

You can try below query:

Here, I have added a new column AppointmentDateKeyPayor. It is a concatenation of AppointmentDateKey and Payor.

It is a trick to associate the payor on the AppointmentDate. Now, if we take max of AppointmentDateKeyPayor, we have max AppointmentDateKey alone with payor on that date.

Lastly, max AppointmentDateKey from AppointmentDateKeyPayor is removed using RELPACE function to only get the Payor we need.

This approach avoids adding one more subquery or OUTER APPLY.

select
  tab.PrimaryMrn
  , REPLACE(max(AppointmentDateKeyPayor), max(AppointmentDateKey), '') Payor
  ,sum(tab.Appt_Count) ct 
from (
select
PatientDim.PrimaryMrn
,VisitFact.AppointmentDateKey
,(CASE  
    WHEN CoverageDim.PayorFinancialClass IN ('Blue Cross Commercial','Commercial') THEN 'Private' 
    WHEN CoverageDim.PayorFinancialClass IN ('Managed Care','Medicaid','Medicaid Replacement','Medicare','Medicare Replacement','Pending Medicaid','Self-Pay') THEN 'Medicaid'
    ELSE 'Other'
    END) as 'Payor'
 ,CAST(VisitFact.AppointmentDateKey as VARCHAR(10))
 +(CASE  
    WHEN CoverageDim.PayorFinancialClass IN ('Blue Cross Commercial','Commercial') THEN 'Private' 
    WHEN CoverageDim.PayorFinancialClass IN ('Managed Care','Medicaid','Medicaid Replacement','Medicare','Medicare Replacement','Pending Medicaid','Self-Pay') THEN 'Medicaid'
    ELSE 'Other'
    END) as 'AppointmentDateKeyPayor'
,count(distinct VisitFact.EncounterKey) Appt_Count
from VisitFact
INNER JOIN PatientDim
    on VisitFact.PatientDurableKey = PatientDim.DurableKey and 
PatientDim.IsCurrent=1 
INNER JOIN CoverageDim
ON VisitFact.CoverageKey = CoverageDim.CoverageKey
where VisitFact.AppointmentDateKey between '20230101' and '20231231'
group by PatientDim.PrimaryMrn ,VisitFact.AppointmentDateKey 
,CoverageDim.PayorFinancialClass
) tab
group by PrimaryMrn
order by PrimaryMrn

展开查看全部

赞(0）回复(0）举报 2024-01-05

SQL Server SQL Count of Dates and Most Recent Value

1条答案

相关问题

热门标签

最新问答