Can someone direct me to a query that will return the count of new patients for each insurance plan over a time period?
I would like to input the start and end date like we do with many of the present queries. Or yearly by month would be perfect!
I am looking for:
................Jan....Feb...Mar...
Delta Dental...24.....12....11...
MetLife........15.....10.....9...
etc.
drtmz
Query for new patients and insurance plans
-
Tom Zaccaria
- Posts: 361
- Joined: Mon Feb 25, 2008 3:09 am
Re: Query for new patients and insurance plans
Not what you exactly asked for. Shows existing and new patients. need modification if someone can help further:
/*69 Count of patients by Carrier with procedures completed in date range*/
SET @FromDate='2016-01-01' , @ToDate='2016-01-31';
/*Adjust above Dates AS needed*/
SELECT carrier.CarrierName, COUNT(DISTINCT claimproc.PatNum) AS 'Patients', carrier.Phone
FROM carrier
INNER JOIN insplan ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claim ON insplan.PlanNum=claim.PlanNum
INNER JOIN claimproc ON claim.ClaimNum=claimproc.ClaimNum
INNER JOIN procedurelog ON claimproc.ProcNum=procedurelog.ProcNum
INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
AND (procedurelog.ProcDate BETWEEN @FromDate AND @ToDate)
AND ProcStatus=2
GROUP BY CarrierName
ORDER BY CarrierName;
/*69 Count of patients by Carrier with procedures completed in date range*/
SET @FromDate='2016-01-01' , @ToDate='2016-01-31';
/*Adjust above Dates AS needed*/
SELECT carrier.CarrierName, COUNT(DISTINCT claimproc.PatNum) AS 'Patients', carrier.Phone
FROM carrier
INNER JOIN insplan ON carrier.CarrierNum=insplan.CarrierNum
INNER JOIN claim ON insplan.PlanNum=claim.PlanNum
INNER JOIN claimproc ON claim.ClaimNum=claimproc.ClaimNum
INNER JOIN procedurelog ON claimproc.ProcNum=procedurelog.ProcNum
INNER JOIN procedurecode ON procedurelog.CodeNum=procedurecode.CodeNum
AND (procedurelog.ProcDate BETWEEN @FromDate AND @ToDate)
AND ProcStatus=2
GROUP BY CarrierName
ORDER BY CarrierName;