Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Comparing multiple versions of a field Expand / Collapse
Author
Message
Posted Friday, October 12, 2012 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 12:25 PM
Points: 2, Visits: 11
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!
Post #1372121
Posted Friday, October 12, 2012 9:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 13,007, Visits: 12,421
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1372229
Posted Friday, October 12, 2012 11:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 12:25 PM
Points: 2, Visits: 11
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

Id ClaimNumber TotalPaidAmount
1 12300 10.00
2 12301 0.00
3 12302 15.00
4 45600 10.00
5 45601 15.00
6 45602 0.00
7 99900 0.00
8 99901 15.00
9 99902 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!
Post #1372288
Posted Sunday, October 14, 2012 7:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 3,420, Visits: 5,348
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1372550
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse