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 - SELECT
-
P.PRPR_ID ,
-
P.PRPR_NAME AS "Provider Name",
-
COUNT(C.CLCL_ID) AS "Total Claims",
-
SUM (CL.CDML_CHG_AMT) AS "Total Charges",
-
SUM (CL.CDML_PR_PYMT_AMT) AS "Total Payment",
-
ISNULL(C2.Denied_Count,0) Denied_Count
-
--(SUM(CL.CDML_PR_PYMT_AMT)/SUM(CL.CDML_CHG_AMT))FROM DW.FAC_CMC_CDML_CL_LINE CL AS "% of Billed Charges"
-
FROM DW.FAC_CMC_CLCL_CLAIM C
-
LEFT JOIN DW.FAC_CMC_PRPR_PROV P
-
ON C.PRPR_ID = P.PRPR_ID
-
LEFT JOIN DW.FAC_CMC_CDML_CL_LINE CL
-
ON C.CLCL_ID = CL.CLCL_ID
-
LEFT JOIN (SELECT P.PRPR_ID, COUNT (DISTINCT C.CLCL_ID) AS Denied_Count
-
FROM DW.FAC_CMC_CLCL_CLAIM C
-
JOIN DW.FAC_CMC_PRPR_PROV P
-
ON C.PRPR_ID = P.PRPR_ID
-
WHERE C.CLCL_TOT_PAYABLE = '0.00'
-
AND C.CLCL_LOW_SVC_DT >= '07/01/2015'
-
AND C.PRPR_ID IN ('x', 'y', 'z')
-
GROUP BY P.PRPR_ID
-
) c2 on C.PRPR_ID=c2.PRPR_ID
-
WHERE C.CLCL_LOW_SVC_DT >= '07/01/2015'
-
AND C.PRPR_ID IN ('x', 'y', 'z')
-
AND C.CLCL_CUR_STS IN ('01', '02')
-
GROUP BY
-
P.PRPR_ID ,
-
P.PRPR_NAME,
-
C2.Denied_Count
-
-
ORDER BY [Total Claims] DESC
0 1556 Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Timur |
last post by:
Hi gurus,
I have a problem to convert MS SQL Server application to DB2.
I have a view which combines 7 tables ( table sizes 60millions rows, 3 mill,
1 mill, other small ones)
I use this view to populate OLAP cube and in SQL Server it takes abut 1
hour..
In DB2 it takes forever. Execution plan looks ugly - DB2 sorts !!!!! 50
mil table by field which is key field for 3 mill rows table.
Indexes are in place, I updated statistics.
When I...
|
by: Jamie Pittman via AccessMonster.com |
last post by:
I am having trouble bellow wit this query. I have the total regular hours and the overtime. The problem is that if it is 8 hours and under, I need it to show as regular hours. Any thoughts?
SELECT ., .Date, ., IIf(<=8,,8) AS , Sum((-)) AS Ot
FROM
GROUP BY ., .Date, ., IIf(<=8,,8);
--
Message posted via http://www.accessmonster.com
|
by: Crimsonwingz |
last post by:
Need to calculate a sum based on a number of factors over a period of
years. I can use formula ^x for some of it, but need totals to carry
over in the sum and have only been able to do this thus far with a loop
in a form.
Basically, I have key sums
Current savings
Current Salary
Current deposit amount
|
by: MattPF |
last post by:
I have a table that is --
30 Megabytes
90,000 rows
~65 columns
My query goes
SELECT city FROM table WHERE zip = 90210;
It will then find about 10 matching records.
|
by: jjturon |
last post by:
Can anyone help me??
I am trying to pass a Select Query variable to a table using Dlookup
and return the value to same select query but to another field.
Ex.
SalesManID SalesManName
AT Alan Time
| |
by: anniebai |
last post by:
I have a query named "Tuition Calculation" saved in the database.
In the code, the criteria of the query need to be added/modified and be saved for use of another auto-generate report.
Is DoCmd.OpenQuery the right one to use? it seems not work
Below is the what I tried, please advice.. thanks :-)
DoCmd.OpenQuery "Tuition Calculation", acViewDesign, acAdd
!.Criteria = "Year=" & Year & " and Semester=" & Semester
DoCmd.Save acQuery...
|
by: mfletcher |
last post by:
Hi
I have a query which calculates a large number of fields for each record, i don't want all of the fields present in the datasheet view only those fields selected by the user in a list box(or similar).
Therefore, how can i add a list box (field listing of a table/query) to a form from which the user can select which of the available fields they want to see in the query output (i.e. has the same effect as checking/unchecking the "Show"...
|
by: Jmaes Wang |
last post by:
Attached please find my code. I failed to query the blob column. Note the Paradox driver version is 4.0. The error is:
Error code: -2146232009
Error msg: ERROR Too few parameters. Expected 1.
Source: odbcjt32.dll
I can query non-blob columns of the table with the same code. Can you please tell me what the problem is? Is this the limitation of .Net Framework Data Provider for ODBC? Do I need to use SQLOLEDB instead? Will SQLOLEDB...
|
by: Charles Ndethi |
last post by:
Hi,
I want to query the table below as to return the titles of the column where the intersection of SessionId and Roomx is 0.The type of the Room is TINYINT - which in mysql represents BOOLEAN
Below is the table:
SessionId Room1 Room2 Room3
1 0 1 0
2 1 0 1
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |