Please help with this query

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
User avatar
Jorgebon
Posts: 502
Joined: Mon Jun 18, 2007 2:25 pm
Location: Mayaguez, PR
Contact:

Please help with this query

Post by Jorgebon » Mon Oct 07, 2013 1:18 pm

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

Image
Jorge Bonilla DMD
Open Dental user since May 2005

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Please help with this query

Post by tgriswold » Mon Oct 07, 2013 2:40 pm

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.

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)
Something like this might work but you'd need to join on each month individually.

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
Travis Griswold
Open Dental Software
http://www.opendental.com

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

Re: Please help with this query

Post by Tom Zaccaria » Mon Oct 07, 2013 4:43 pm

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

User avatar
Jorgebon
Posts: 502
Joined: Mon Jun 18, 2007 2:25 pm
Location: Mayaguez, PR
Contact:

Re: Please help with this query

Post by Jorgebon » Mon Oct 07, 2013 6:49 pm

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
Jorge Bonilla DMD
Open Dental user since May 2005

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

Re: Please help with this query

Post by Tom Zaccaria » Tue Oct 08, 2013 3:25 am

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

User avatar
Jorgebon
Posts: 502
Joined: Mon Jun 18, 2007 2:25 pm
Location: Mayaguez, PR
Contact:

Re: Please help with this query

Post by Jorgebon » Wed Oct 09, 2013 5:16 pm

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:
Image

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

User avatar
Jorgebon
Posts: 502
Joined: Mon Jun 18, 2007 2:25 pm
Location: Mayaguez, PR
Contact:

Re: Please help with this query

Post by Jorgebon » Thu Oct 10, 2013 10:16 am

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

User avatar
wjstarck
Posts: 935
Joined: Tue Jul 31, 2007 7:18 am
Location: Keller, TX
Contact:

Re: Please help with this query

Post by wjstarck » Thu Oct 10, 2013 3:24 pm

Nice work.

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

drjorgel1
Posts: 20
Joined: Sat Feb 25, 2012 2:45 am

Re: Please help with this query

Post by drjorgel1 » Sat Dec 14, 2013 12:44 pm

I havent used yet, but can you click over the claim and print it out?

User avatar
Jorgebon
Posts: 502
Joined: Mon Jun 18, 2007 2:25 pm
Location: Mayaguez, PR
Contact:

Re: Please help with this query

Post by Jorgebon » Sun Dec 15, 2013 6:48 am

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
Jorge Bonilla DMD
Open Dental user since May 2005

Post Reply