October 12, 2012 at 7:33 am
I am working with healthcare data. In some cases, a claim may have 3 different versions (based on whether the previous version was adjusted, re-paid, etc). So claim 12300, 12301, and 12302...all the same claim, but different versions as indicated by the suffix.
Here are a couple of scenarios I will see in the data and I need different results for each:
12300 paid $10
12301 paid $0 (would be considered a recoup of 12300)
12302 paid $15
Or
12300 paid $0
12301 paid $10
12302 paid $15
In the first scenario I would not want any of the claim numbers to show up in my results, but in the second scenario I would want it to show 12301 and 12302 (it's also ok to include 12300 but I would prefer to exclude that claim).
Please help with the correct way to obtain this result?
Thank you in advance for your help!
October 12, 2012 at 9:24 am
Hi and welcome to SSC!
Unfortunately you have not provided enough information for anybody to be able to help much. We can't see your data or the results. Without something to work with we are just shooting in the dark.
Please take a few minutes and read the first link in my signature about best practices when posting questions. Once you post the required details you will find lots of people willing and able to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 12, 2012 at 11:16 am
Thank you for the advice. I'm still very new at SQL but hopefully this will help clarify what I am looking to do:
Create Table #ClaimSummary
(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ClaimNumber varchar,)
TotalPaidAmount money)
This would be the first 9 records for table #ClaimSummary
IdClaimNumberTotalPaidAmount
112300 10.00
212301 0.00
312302 15.00
445600 10.00
545601 15.00
645602 0.00
799900 0.00
899901 15.00
999902 20.00
Here is the code I am currently using. This gives me part of what I want, but unfortunately if ANY claim beginning with the same 3 numbers has a $0 totalpaidamount then it will show in the results. And in the case of claim beginning 999, I would want to exclude that from the result set because the last 2 consecutive claims both had a > 0 totalpaidamount.
SELECT DISTINCT SUBSTRING(CS.ClaimNumber,1,3) AS PartialClaimNumber
FROM #ClaimSummary AS CS
WHERE CS.TotalPaidAmount = 0
GROUP BY SUBSTRING(CS.ClaimNumber,1,3)
How can I write the code to prevent SUBSTRING(CS.ClaimNumber,1,3) = 999 from showing in my result set?
Thank you!
October 14, 2012 at 7:46 pm
Not 100% sure but perhaps something like this?
Create Table #ClaimSummary
(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ClaimNumber varchar(5),
TotalPaidAmount money)
INSERT INTO #ClaimSummary
SELECT '12300',10.00
UNION ALL SELECT '12301',0.00
UNION ALL SELECT '12302',15.00
UNION ALL SELECT '45600',10.00
UNION ALL SELECT '45601',15.00
UNION ALL SELECT '45602',0.00
UNION ALL SELECT '99900',0.00
UNION ALL SELECT '99901',15.00
UNION ALL SELECT '99902',20.00
;WITH Claims AS (
SELECT ID, ClaimNumber, TotalPaidAmount
,rn=ROW_NUMBER() OVER (PARTITION BY SUBSTRING(ClaimNumber, 1, 3) ORDER BY ID)
FROM #ClaimSummary)
SELECT PartialClaimNumber=SUBSTRING(ClaimNumber, 1, 3)
FROM Claims
WHERE rn<>1 AND TotalPaidAmount=0
DROP TABLE #ClaimSummary
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply