Please help with this query
Please help with this query
I'm trying to make a query where I can get the number of pending claims for each insurance company for each month of the year and then a total for the whole year. This is as far as I could get, and I can't find a way to distribute the claims for each month. The query and a picture of the result follows:
SET @FromDate='2013-01-01' , @ToDate='2013-12-31';
SELECT ca.CarrierName, MONTH(DateService)=1 AS 'Jan', MONTH(DateService)=2 AS 'Feb', MONTH(DateService)=3 AS 'Mar',MONTH(DateService)=4 AS 'Apr', MONTH(DateService)=5 AS 'May', MONTH(DateService)=6 AS 'Jun', MONTH(DateService)=7 AS 'Jul', COUNT(IF(MONTH(DateService)=8, cl.ClaimNum,0)) AS 'Aug', MONTH(DateService)=9 AS 'Sep', MONTH(DateService)=10 AS 'Oct', MONTH(DateService)=11 AS 'Nov', MONTH(DateService)=12 AS 'Dec', cl.ProvTreat, COUNT(cl.ClaimNum) AS 'Total#Claims',
SUM(cl.InsPayEst) AS '$InsPayEst'
FROM claim cl
INNER JOIN insplan i ON i.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=i.CarrierNum
WHERE
cl.ClaimType<>'PreAuth' AND (cl.ClaimStatus='R' OR cl.ClaimStatus='S') AND
(DateService BETWEEN @FromDate AND @ToDate) AND cl.InsPayAmt = 0 AND cl.InsPayEst>0
GROUP BY ca.CarrierName
SET @FromDate='2013-01-01' , @ToDate='2013-12-31';
SELECT ca.CarrierName, MONTH(DateService)=1 AS 'Jan', MONTH(DateService)=2 AS 'Feb', MONTH(DateService)=3 AS 'Mar',MONTH(DateService)=4 AS 'Apr', MONTH(DateService)=5 AS 'May', MONTH(DateService)=6 AS 'Jun', MONTH(DateService)=7 AS 'Jul', COUNT(IF(MONTH(DateService)=8, cl.ClaimNum,0)) AS 'Aug', MONTH(DateService)=9 AS 'Sep', MONTH(DateService)=10 AS 'Oct', MONTH(DateService)=11 AS 'Nov', MONTH(DateService)=12 AS 'Dec', cl.ProvTreat, COUNT(cl.ClaimNum) AS 'Total#Claims',
SUM(cl.InsPayEst) AS '$InsPayEst'
FROM claim cl
INNER JOIN insplan i ON i.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=i.CarrierNum
WHERE
cl.ClaimType<>'PreAuth' AND (cl.ClaimStatus='R' OR cl.ClaimStatus='S') AND
(DateService BETWEEN @FromDate AND @ToDate) AND cl.InsPayAmt = 0 AND cl.InsPayEst>0
GROUP BY ca.CarrierName
Jorge Bonilla DMD
Open Dental user since May 2005
Open Dental user since May 2005
Re: Please help with this query
Something like this will give you the the count by month of service for each carrier. Its more work to get them to show up vertically, but you can do it with some joins and a bit of work.
Something like this might work but you'd need to join on each month individually.
Code: Select all
SELECT MONTH(DateService),COUNT(*) AS 'Count',ca.CarrierNum
FROM claim cl
INNER JOIN insplan i ON i.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=i.CarrierNum
WHERE cl.ClaimType<>'PreAuth'
AND (cl.ClaimStatus='R' OR cl.ClaimStatus='S')
AND (DateService BETWEEN @FromDate AND @ToDate)
AND cl.InsPayAmt = 0
AND cl.InsPayEst>0
GROUP BY MONTH(DateService),ca.CarrierNum
ORDER BY MONTH(DateService)
Code: Select all
LEFT JOIN (
***CODE ABOVE***
WHERE MONTH(DateService)=1
***The rest of the where clauses and the group/order by***
) jan ON jan.CarrierNum=i.CarrierNum
-
- Posts: 356
- Joined: Mon Feb 25, 2008 3:09 am
Re: Please help with this query
Very nice work but I don't get the purpose.
There are other reports for following up on outstanding claims by date of service.
drtmz
There are other reports for following up on outstanding claims by date of service.
drtmz
Re: Please help with this query
When the pending claims are distributed by month in a spreadsheet style report, you get a more graphic idea of what companies are problematic. This query would be used in combination with other queries that give you a detail of what the pending claims are and what procedures are involved. I already have these other queries finished. The user enters the name of the insurance company in the first line. They go like this:
SET @Carrier='%Humana%';
SELECT carrier.carriername, claim.claimIdentifier, claimproc.provnum, claimproc.patnum, claimproc.procdate, procedurecode.proccode, procedurelog.toothnum, procedurelog.surf, claimproc.inspayest AS 'Balance'
FROM carrier, insplan, claimproc, procedurecode, procedurelog, claim
WHERE claimproc.Inspayamt = 0
AND procedurecode.proccode LIKE 'D%'
AND insplan.plannum = claimproc.plannum
AND insplan.carriernum = carrier.carriernum
AND procedurelog.codenum = procedurecode.codenum
AND claimproc.procnum = procedurelog.procnum
AND claimproc.claimnum = claim.claimnum
AND procedurelog.procstatus = '2'
AND claimproc.inspayest > 0
AND claimproc.status = '0'
AND claimproc.writeoff = 0
AND carrier.carrierName LIKE @Carrier
ORDER by claimproc.procdate, claimproc.patnum
SET @Carrier='%Humana%';
SELECT carrier.carriername, claim.claimIdentifier, claimproc.provnum, claimproc.patnum, claimproc.procdate, procedurecode.proccode, procedurelog.toothnum, procedurelog.surf, claimproc.inspayest AS 'Balance'
FROM carrier, insplan, claimproc, procedurecode, procedurelog, claim
WHERE claimproc.Inspayamt = 0
AND procedurecode.proccode LIKE 'D%'
AND insplan.plannum = claimproc.plannum
AND insplan.carriernum = carrier.carriernum
AND procedurelog.codenum = procedurecode.codenum
AND claimproc.procnum = procedurelog.procnum
AND claimproc.claimnum = claim.claimnum
AND procedurelog.procstatus = '2'
AND claimproc.inspayest > 0
AND claimproc.status = '0'
AND claimproc.writeoff = 0
AND carrier.carrierName LIKE @Carrier
ORDER by claimproc.procdate, claimproc.patnum
Jorge Bonilla DMD
Open Dental user since May 2005
Open Dental user since May 2005
-
- Posts: 356
- Joined: Mon Feb 25, 2008 3:09 am
Re: Please help with this query
I use this on on a weekly basis to go after overdue claims. We give then 20 days and then check.
You could make the interval longer or shorter by changing the 'INTERVAL xx DAY' line below.
/*133 */
SELECT cl.PatNum, cl.DateService,cl.DateSent, cl.ClaimType, ca.CarrierName, ca.Phone, cl.claimfee FROM claim cl
INNER JOIN patient p ON p.PatNum=cl.PatNum
INNER JOIN insplan i ON i.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=i.CarrierNum
WHERE cl.ClaimStatus='S' AND
DateService<(CURDATE()-INTERVAL 20 DAY) AND
ClaimType<>'PreAuth'
ORDER BY cl.claimtype, cl.DateService, p.LName;
drtmz
You could make the interval longer or shorter by changing the 'INTERVAL xx DAY' line below.
/*133 */
SELECT cl.PatNum, cl.DateService,cl.DateSent, cl.ClaimType, ca.CarrierName, ca.Phone, cl.claimfee FROM claim cl
INNER JOIN patient p ON p.PatNum=cl.PatNum
INNER JOIN insplan i ON i.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=i.CarrierNum
WHERE cl.ClaimStatus='S' AND
DateService<(CURDATE()-INTERVAL 20 DAY) AND
ClaimType<>'PreAuth'
ORDER BY cl.claimtype, cl.DateService, p.LName;
drtmz
Re: Please help with this query
I finally managed to write the query I was looking for. The hard part was converting a column into a row. The user can see how many claims are pending per month from each one of the insurance companies. This is how the final result looks:
The query for those who may want to try it out is:
SET @FromDate='2013-01-01' , @ToDate='2013-12-31';
SELECT ca.CarrierName, COALESCE(COUNT(case when Month(Dateservice)=1 then cl.claimnum end),0) AS 'Jan', COALESCE(COUNT(case when Month(Dateservice)=2 then cl.claimnum end),0) AS 'Feb', COALESCE(COUNT(case when Month(Dateservice)=3 then cl.claimnum end),0) AS 'Mar',COALESCE(COUNT(case when Month(Dateservice)=4 then cl.claimnum end),0) AS 'Apr', COALESCE(COUNT(case when Month(Dateservice)=5 then cl.claimnum end),0) AS 'May', COALESCE(COUNT(case when Month(Dateservice)=6 then cl.claimnum end),0) AS 'Jun', COALESCE(COUNT(case when Month(Dateservice)=7 then cl.claimnum end),0) AS 'Jul', COALESCE(COUNT(case when MONTH(DateService)=8 then cl.ClaimNum end),0) AS 'Aug', COALESCE(COUNT(case when Month(Dateservice)=9 then cl.claimnum end),0) AS 'Sep', COALESCE(COUNT(case when Month(Dateservice)=10 then cl.claimnum end),0) AS 'Oct', COALESCE(COUNT(case when Month(Dateservice)=11 then cl.claimnum end),0) AS 'Nov', COALESCE(COUNT(case when Month(Dateservice)=12 then cl.claimnum end),0) AS 'Dec', cl.ProvTreat, COUNT(cl.ClaimNum) AS '#Claims',
SUM(cl.InsPayEst) AS '$InsPayEst'
FROM claim cl
INNER JOIN insplan i ON i.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=i.CarrierNum
WHERE
cl.ClaimType<>'PreAuth' AND cl.ClaimStatus='S' AND
(DateService BETWEEN @FromDate AND @ToDate) AND cl.InsPayAmt = 0 AND cl.InsPayEst>0
GROUP BY ca.CarrierName
The query for those who may want to try it out is:
SET @FromDate='2013-01-01' , @ToDate='2013-12-31';
SELECT ca.CarrierName, COALESCE(COUNT(case when Month(Dateservice)=1 then cl.claimnum end),0) AS 'Jan', COALESCE(COUNT(case when Month(Dateservice)=2 then cl.claimnum end),0) AS 'Feb', COALESCE(COUNT(case when Month(Dateservice)=3 then cl.claimnum end),0) AS 'Mar',COALESCE(COUNT(case when Month(Dateservice)=4 then cl.claimnum end),0) AS 'Apr', COALESCE(COUNT(case when Month(Dateservice)=5 then cl.claimnum end),0) AS 'May', COALESCE(COUNT(case when Month(Dateservice)=6 then cl.claimnum end),0) AS 'Jun', COALESCE(COUNT(case when Month(Dateservice)=7 then cl.claimnum end),0) AS 'Jul', COALESCE(COUNT(case when MONTH(DateService)=8 then cl.ClaimNum end),0) AS 'Aug', COALESCE(COUNT(case when Month(Dateservice)=9 then cl.claimnum end),0) AS 'Sep', COALESCE(COUNT(case when Month(Dateservice)=10 then cl.claimnum end),0) AS 'Oct', COALESCE(COUNT(case when Month(Dateservice)=11 then cl.claimnum end),0) AS 'Nov', COALESCE(COUNT(case when Month(Dateservice)=12 then cl.claimnum end),0) AS 'Dec', cl.ProvTreat, COUNT(cl.ClaimNum) AS '#Claims',
SUM(cl.InsPayEst) AS '$InsPayEst'
FROM claim cl
INNER JOIN insplan i ON i.PlanNum=cl.PlanNum
INNER JOIN carrier ca ON ca.CarrierNum=i.CarrierNum
WHERE
cl.ClaimType<>'PreAuth' AND cl.ClaimStatus='S' AND
(DateService BETWEEN @FromDate AND @ToDate) AND cl.InsPayAmt = 0 AND cl.InsPayEst>0
GROUP BY ca.CarrierName
Jorge Bonilla DMD
Open Dental user since May 2005
Open Dental user since May 2005
Re: Please help with this query
If you add "with rollup" at the end, you will also get totals for each column, but then it won't print out correctly since the print function for queries adds its own sum total for the last column.
Jorge Bonilla DMD
Open Dental user since May 2005
Open Dental user since May 2005
Re: Please help with this query
Nice work.
Thanks for sharing that Jorge.
Thanks for sharing that Jorge.
Cheers,
Bill Starck, DDS
Big Idea Software, LLC
Developer, EASy(Electronic Anesthesia System) for Open Dental
817-807-1709
TX, USA
Bill Starck, DDS
Big Idea Software, LLC
Developer, EASy(Electronic Anesthesia System) for Open Dental
817-807-1709
TX, USA
Re: Please help with this query
I havent used yet, but can you click over the claim and print it out?
Re: Please help with this query
No, you can only get the information from it and then have the claims you are interested in evaluating be listed by using another query like this one for Humana:
SET @Carrier='%Humana%';
SELECT carrier.carriername, claim.claimIdentifier, claimproc.provnum, claimproc.patnum, claimproc.procdate, procedurecode.proccode, procedurelog.toothnum, procedurelog.surf, claimproc.inspayest AS 'Balance'
FROM carrier, insplan, claimproc, procedurecode, procedurelog, claim
WHERE claimproc.Inspayamt = 0
AND procedurecode.proccode LIKE 'D%'
AND insplan.plannum = claimproc.plannum
AND insplan.carriernum = carrier.carriernum
AND procedurelog.codenum = procedurecode.codenum
AND claimproc.procnum = procedurelog.procnum
AND claimproc.claimnum = claim.claimnum
AND procedurelog.procstatus = '2'
AND claimproc.inspayest > 0
AND claimproc.status = '0'
AND claimproc.writeoff = 0
AND carrier.carrierName LIKE @Carrier
ORDER by claimproc.procdate, claimproc.patnum
SET @Carrier='%Humana%';
SELECT carrier.carriername, claim.claimIdentifier, claimproc.provnum, claimproc.patnum, claimproc.procdate, procedurecode.proccode, procedurelog.toothnum, procedurelog.surf, claimproc.inspayest AS 'Balance'
FROM carrier, insplan, claimproc, procedurecode, procedurelog, claim
WHERE claimproc.Inspayamt = 0
AND procedurecode.proccode LIKE 'D%'
AND insplan.plannum = claimproc.plannum
AND insplan.carriernum = carrier.carriernum
AND procedurelog.codenum = procedurecode.codenum
AND claimproc.procnum = procedurelog.procnum
AND claimproc.claimnum = claim.claimnum
AND procedurelog.procstatus = '2'
AND claimproc.inspayest > 0
AND claimproc.status = '0'
AND claimproc.writeoff = 0
AND carrier.carrierName LIKE @Carrier
ORDER by claimproc.procdate, claimproc.patnum
Jorge Bonilla DMD
Open Dental user since May 2005
Open Dental user since May 2005