Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Comparing multiple versions of a field


Comparing multiple versions of a field

Author
Message
daciaturner
daciaturner
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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!
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16594 Visits: 17024
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)
daciaturner
daciaturner
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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!
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4259 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search