By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,471 Members | 722 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,471 IT Pros & Developers. It's quick & easy.

Comples Query to Include % of two columns calculation

P: 1
I am building a query that gives the following information
Total Claims, Total Charges, Total Payment, Denial Count, % of Billed Charges and % of Claims Denied

This is all grouped by provider id, provider name and denied count then ordered by Total Claims.

I have been successful with my query until it comes to getting the % of Billed Charges and % OF Claims denied. You will see below my attempt to build a query in the select portion of the statement which of course did not work at all. When I -- the statement the query works so I know the mistake is specifically on that statement. I'm thinking it would as well need to be in one of the joins.....

I have no clue how to perform this type of aggregate. I need to have the % grouped by the provider as the count and sums are in the select statement and joins

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. P.PRPR_ID , 
  3. P.PRPR_NAME AS "Provider Name", 
  4. COUNT(C.CLCL_ID) AS "Total Claims",
  5. SUM (CL.CDML_CHG_AMT) AS "Total Charges",
  6. SUM (CL.CDML_PR_PYMT_AMT) AS "Total Payment",
  7. ISNULL(C2.Denied_Count,0) Denied_Count
  8. --(SUM(CL.CDML_PR_PYMT_AMT)/SUM(CL.CDML_CHG_AMT))FROM DW.FAC_CMC_CDML_CL_LINE CL AS "% of Billed Charges"
  9. FROM DW.FAC_CMC_CLCL_CLAIM C
  10. LEFT JOIN DW.FAC_CMC_PRPR_PROV P
  11. ON C.PRPR_ID = P.PRPR_ID
  12. LEFT JOIN DW.FAC_CMC_CDML_CL_LINE CL
  13. ON C.CLCL_ID = CL.CLCL_ID
  14. LEFT JOIN (SELECT P.PRPR_ID, COUNT (DISTINCT C.CLCL_ID) AS Denied_Count
  15. FROM DW.FAC_CMC_CLCL_CLAIM C 
  16. JOIN DW.FAC_CMC_PRPR_PROV P
  17. ON C.PRPR_ID = P.PRPR_ID
  18. WHERE C.CLCL_TOT_PAYABLE = '0.00'
  19. AND C.CLCL_LOW_SVC_DT >= '07/01/2015'
  20. AND C.PRPR_ID IN ('x', 'y', 'z')
  21. GROUP BY P.PRPR_ID
  22. ) c2 on C.PRPR_ID=c2.PRPR_ID
  23. WHERE C.CLCL_LOW_SVC_DT >= '07/01/2015'
  24. AND C.PRPR_ID IN ('x', 'y', 'z')
  25. AND C.CLCL_CUR_STS IN ('01', '02')
  26. GROUP BY 
  27. P.PRPR_ID , 
  28. P.PRPR_NAME, 
  29. C2.Denied_Count
  30.  
  31. ORDER BY [Total Claims] DESC
Oct 6 '15 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.