Calling all query wizards!

For users or potential users.
Post Reply
msmfd
Posts: 21
Joined: Mon Feb 22, 2021 7:32 am

Calling all query wizards!

Post by msmfd » Wed Apr 13, 2022 11:25 am

Goal:
Generate a database of patients with a particular insurance, filtering list to include only patients that have been seen within the last two years, say from 1/1/2020-now. We need to do a mailing to include only patients that fit these parameters. Can you help?

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Calling all query wizards!

Post by Tom Zaccaria » Thu Apr 14, 2022 2:30 am

Try this
Change dates in line 2.
Change insurance to replace Careington in the second last line.
drtmz


/*Modified #93, Insurance and Patient income for time period summed carrier. Patient income only shows on primary insurance carrier*/
SET @FromDate='2021-1-01', @ToDate='2021-12-31';
SELECT A.CarrierName AS 'Carrier Name',
A.PatName AS 'Patient Name',
A.$InsPayment AS '$InsPayment_',
A.$PatPayment AS '$PatPayment_',
$InsPayment+$PatPayment AS '$Total_'
FROM (
SELECT 0 AS ItemOrder,
carrier.CarrierName,
CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI) AS PatName,
IFNULL(SUM(claimproc.InsPayAmt),0) AS $InsPayment,
(CASE WHEN patplan.Ordinal=1 THEN COALESCE(patPayment.TotalPay,0) ELSE 0 END) AS $PatPayment
FROM patient
INNER JOIN patplan ON patplan.PatNum=patient.PatNum
INNER JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum
INNER JOIN insplan ON insplan.PlanNum=inssub.PlanNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claimproc ON claimproc.PatNum=patient.PatNum AND claimproc.PlanNum=insplan.PlanNum
LEFT JOIN(
SELECT SUM(paysplit.SplitAmt) AS TotalPay,paysplit.PatNum
FROM paysplit
WHERE paysplit.DatePay BETWEEN @FromDate AND @ToDate
GROUP BY paysplit.PatNum
) patPayment ON patPayment.PatNum=patient.PatNum AND patPlan.Ordinal=1
WHERE patient.PatStatus=0
AND claimproc.DateCP BETWEEN @Fromdate AND @Todate
AND (claimproc.Status=1 OR claimproc.Status=4)
GROUP BY patient.PatNum,carrier.CarrierNum

UNION ALL

SELECT 1 AS ItemOrder,
'--------------------' AS CarrierName,
'-----' AS PatNum,
NULL AS $InsPayment,
NULL AS $PatPayment

UNION ALL

SELECT 2 AS ItemOrder,
'Total:' AS CarrierName,
NULL AS PatNum,
SUM(B.$InsPayment) AS $InsPayment,
SUM(B.$PatPayment) AS $PatPayment
FROM(
SELECT carrier.CarrierName,
CONCAT(patient.LName,', ',patient.FName,' ',patient.MiddleI) AS PatName,
IFNULL(SUM(claimproc.InsPayAmt),0) AS $InsPayment,
(CASE WHEN patplan.Ordinal=1 THEN COALESCE(patPayment.TotalPay,0) ELSE 0 END) AS $PatPayment
FROM patient
INNER JOIN patplan ON patplan.PatNum=patient.PatNum
INNER JOIN inssub ON patplan.InsSubNum=inssub.InsSubNum
INNER JOIN insplan ON insplan.PlanNum=inssub.PlanNum
INNER JOIN carrier ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claimproc ON claimproc.PatNum=patient.PatNum AND claimproc.PlanNum=insplan.PlanNum
LEFT JOIN(
SELECT SUM(paysplit.SplitAmt) AS TotalPay,paysplit.PatNum
FROM paysplit
WHERE paysplit.DatePay BETWEEN @FromDate AND @ToDate
GROUP BY paysplit.PatNum
) patPayment ON patPayment.PatNum=patient.PatNum AND patPlan.Ordinal=1
WHERE patient.PatStatus=0
AND claimproc.DateCP BETWEEN @Fromdate AND @Todate
AND (claimproc.Status=1 OR claimproc.Status=4)

GROUP BY patient.PatNum,carrier.CarrierNum
)B
) A
Where A.carriername LIKE '%Careington%'
ORDER BY A.ItemOrder,A.CarrierName,A.PatName

msmfd
Posts: 21
Joined: Mon Feb 22, 2021 7:32 am

Re: Calling all query wizards!

Post by msmfd » Tue Apr 19, 2022 1:56 pm

Awesome, thank you!

Post Reply