Please help with this query

[Jorgebon]

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

[tgriswold]

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

[Tom Zaccaria]

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

[Jorgebon]

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

[Tom Zaccaria]

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

[Jorgebon]

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

[Jorgebon]

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.

[wjstarck]

Nice work.

Thanks for sharing that Jorge.

[drjorgel1]

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

[Jorgebon]

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