Report Design: Needed: SQL Help on a Top N Claims Report (thank y

Needed: SQL Help on a Top N Claims Report (thank y

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15250
Printed Date: 19 Apr 2025 at 9:35pm

Topic: Needed: SQL Help on a Top N Claims Report (thank y

Posted By: britpopfan74
Subject: Needed: SQL Help on a Top N Claims Report (thank y
Date Posted: 28 Dec 2011 at 3:03am

Hello - I'm new to Crystal but managed to get a SQL working report of the highest top ten paid individual claims by vendor using code below...

The issue is, they want the highest top ten SUMMarized claim settlements paid by distinct vendor in descending order (and how many unique member claims are are within this sum)...

I haven't been able to get anywhere doing standard report creation with the wizard to set up "top N" and summary reports, so any help is much appreciated!

--Set up to pull top vendors for the period 1/1/2010 - 12/31/2010
--non-'HOSP' only claims
--Paid claims -- MASTER_CLAIMS table which has all reconciled claims
--VENDOR table contains vendor info

SELECT DISTINCT TOP 10
PopClaim = CASE WHEN mc.DOCUMENT IS NOT NULL Then 1 ELSE 0 END, --increment for counting claims
mc.TOTAL_PAY_AMT,
mc.BILL_AMT,
PercVenPd = ((mc.TOTAL_PAY_AMT/mc.BILL_AMT)*100),
SUM(mc.TOTAL_PAY_AMT) as TotalPaid,
v.Last_Name as VendorName,
...
FROM
MASTER_CLAIM mc
INNER JOIN VENDOR v ON mc.VENDOR_NPI = v.NPI
WHERE
mc.FIRST_DOS BETWEEN '01/01/2010' AND '12/31/2010' --date criteria
AND
mc.VENDOR_TYPE <> 'HOSP' --non-hospital only claims
AND mc.TOTAL_PAY_AMT <> 0.00

AND mc.TOTAL_PAY_AMT IS NOT NULL
AND mc.BILL_AMT <> 0.00
AND mc.BILL_AMT IS NOT NULL
...
AND mc.Document NOT LIKE 'R%' --don't include reversed claims
GROUP BY mc.TOTAL_PAY_AMT,
mc.BILL_AMT,
mc.DOCUMENT,
v.Last_Name,
...
ORDER BY mc.BILL_AMT DESC

Replies:

Posted By: lockwelle
Date Posted: 28 Dec 2011 at 4:27am

does this return the values that you are looking for?

If so, why not make it a stored procedure and replace the date criteria with parameters that a user can enter in CR?

Posted By: britpopfan74
Date Posted: 28 Dec 2011 at 5:21am

It doesn't return what I want exactly...

What it is giving me is the top 10 claims where the 3 important columns are:

Billed_Amt Total_Cost Vendor_Name

- giving me the descending amount of the costliest claims in $.

This is good; however, it is not giving me the SUM of total claims by vendor.

So for example, I am seeing that Hospital A is the vendor responsible for 5 out of the 10 claims on individual rows with a rank. So currently it holds 5 out of 10 slots on the list in terms of distinct claims.

Hospitals B, C, D, E each have 1 claim.

What I would like to see if the SUM of all the 5 claims by Hospital A, which ranks it #1.

Then the other hospitals will fall down in ranking and unless one of them has other cumulative claims, that hospital will just have one row in the report.

So like:

Billed_Amt Total_Cost_Pd Vendor_Name

1. $100,000 $55,000 Hospital A (comprised of 5 claims)

2. $ 99,000 $50,000 Hospital B (comprised of 1 claim)

....

Does that make sense?

I am incorrect in the SQL I posted because I am not getting distinct vendors using the 'SELECT DISTINCT TOP 10...' (they are repeating) and it seems that only the billed and cost amounts are what are distinct.

Many thanks!!!

Posted By: lockwelle
Date Posted: 28 Dec 2011 at 6:38am

ok, so the columns that you need are there, just not the sums

this is what I would do.

Insert the entries you have, all of them, into a temp table, then you can insert distinct values for the vendors into another temp table.

update the last temp values with the summary data by vendor.

finally select the top n, or return the entire table and have CR select the top n for you.

stored procs are definitely more flexible, so if possible, that is the route I would take (though I say that all the time as I only create reports from stored procs).

HTH

Posted By: britpopfan74
Date Posted: 28 Dec 2011 at 6:55am

This is a great idea; the problem with writing Crystal SQL I have found is that you cannot create temp tables (I've read about it on the forums and boy is it frustrating!)

The workarounds if you don't have SQL Server readily available seem to be trying to do subqueries, case statements and using sub-reports...whatever workarounds you may figure...

I'm in the process of trying all of these at the moment since I cannot do a CREATE TABLE or VIEW given my read-only rights.

Posted By: lockwelle
Date Posted: 28 Dec 2011 at 8:22am

oh, you don't have the ability to write a stored proc...

ok, the next idea would be to put your query in a Command Object, but leave off the top 10 part, so that you get all the vendors and their amounts....

oh, here, it won't be pretty, but put your big query as a subquery and group by vendor in teh command object. Like:

select sum(ss.x) as x, sum(ss.y) as y, ss.vendor

from (big select you've already written) as ss

group by ss.vendor

Now you have all the data summarized in a table that CR can then pull the top N from.

Hopefully this is closer to a solution.

Posted By: britpopfan74
Date Posted: 28 Dec 2011 at 8:54am

Great, will try this solution...many thanks!!!