sql - 计算每个人每个日期发生一次

Question

我有一个查询，其中包含一个人使用的保险和服务日期。如果一个人在同一确切日期使用了医疗保险和牙科保险，我只想计算医疗保险。

With medical as
   ( select p.person_nbr as PNum
    , pe.date
    , pm.payer
    , 'Medical' as PayerGroup
    from person p
JOIN patient_encounter pe on p.person_id = pe.person_id
JOIN payer_mstr pm on pe.payer_id = pe.payer_id
where pm.payer like '%Medical%'

--insert medical into and create temp table 
SELECT
PNum
,Date
,Payer
,PayerGroup
INTO #WTEMP
FROM Medical; 

With dental as
   ( select p.person_nbr as PNum
    , pe.date
    , pm.payer
    ,'Dental' as PayerGroup
    from person p
JOIN patient_encounter pe on p.person_id = pe.person_id
JOIN payer_mstr pm on pe.payer_id = pe.payer_id
where pm.payer like '%Dental%'

--insert dental into temp table 
SELECT
PNum
,Date
,Payer
,PayerGroup
INTO #WTEMP
FROM Dental; 

--Pull from temp
select Pnum
,Date
,Payer
,PayerGroup
FROM #WTEMP
DROP TABLE #WTEMP;

如果那个人那天有就诊，我基本上只是想消除牙科记录

标签： sqlsql-server

Answer 1

我会使用 GROUP BY 子句。

假设Payer是“人”并且Date是“服务日期”，您可以尝试：

SELECT Pnum, Date, Payer, PayerGroup FROM #WTEMP GROUP BY Payer, Date DROP TABLE #WTEMP;