Comparing records against multiple rows

Question

Comparing records against multiple rows

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

Excellent job posting ddl and sample data. I am however totally lost on your requirements. Can you try to explain it more clearly?

_______________________________________________________________

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/

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 2

I think I might have a rough idea of how to do this, but I too am a bit lost on the details. From the solution I cobbled up:

SpBen for Whitehurst: D

SpBen for Carpenter and Walker: D-D

However, Carpenter has M for MedBen for all of its entries; is there something special to be done in this case?

- 😀

hogpen SSC Eights! Points: 931 More actions · Answer 3

Thank you both for your feedback. I'll try to explain it more clearly. @hisakimatama, Sorry, I forgot to mention one thing. I can ignore the dependents (records with RelCode != 18) that have an 'M' for MedBen as they will have a different MDX code.

SpBen for Whitehurst: D

SpBen for Walker: D-D

SpBen for Carpenter: MDX

So any employee (RelCode = 18) that has medical/dental (MedBen=M and DenBen=D) but any of their dependents have dental only (MedBen='' and DenBen=D), the benefit class code (SpBen) to use would be be D-D. If an employee (RelCode = 18) has dental only (MedBen='' and DenBen=D) then the SpBen would be D. If an employee (RelCode = 18) has medical/dental (MedBen=M and DenBen=D) and all of their dependents (RelCode!=18) have medical/dental then the SpBen would be MDX.

Here's a case statement mixed with pseudo-code if that helps at all?

update [dbo].[tbl_SubscriberTest]

set SpBen =

CASE

WHEN every family member has MedBen = 'M' and DenBen = 'D' THEN 'MDX'

WHEN MedBen = '' and DenBen = 'D' THEN 'D'

WHEN main employee has MedBen = 'M' and DenBen = 'D' but dependents have DenBen = 'D' and MedBen = '' THEN 'D-D'

I'm thinking some type of subquery or pivot but I'm lost. Please let me know if I can clarify further.

Ed B Hall of Fame Points: 3690 More actions · Answer 4

Does this get what you need?

There is probably a cleaner way and I did modify one data value so that SubscriberNum 1234 would be D-D

SELECT *,

CASE

WHEN EXISTS (SELECT 1 FROM tbl_SubscriberTest WHERE SubscriberNum = a.SubscriberNum AND MedBen = 'M' AND DenBen = 'D')

AND NOT EXISTS (SELECT 1 FROM tbl_SubscriberTest WHERE SubscriberNum = a.SubscriberNum AND (MedBen <> 'M' OR DenBen <> 'D'))

THEN 'MDX'

WHEN EXISTS (SELECT 1 FROM tbl_SubscriberTest WHERE SubscriberNum = a.SubscriberNum AND RelCode = '01' AND MedBen = 'M' AND DenBen = 'D')

AND EXISTS (SELECT 1 FROM tbl_SubscriberTest WHERE SubscriberNum = a.SubscriberNum AND RelCode <> '01' AND MedBen = '' AND DenBen = 'D')

THEN 'D-D'

WHEN MedBen = '' and DenBen = 'D' THEN 'D'

ELSE ''

END

FROM tbl_SubscriberTest AS a

ORDER BY a.SubscriberNum

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 5

Hm, I came up with this in the end. Doubtlessly, there's a cleaner solution, but it seems to work:

WITH CTE(LastName,Code) AS(

SELECT LastName, CASE WHEN MedBen = 'M' AND DenBen = 'D' THEN 'D-D'

WHEN MedBen = '' AND DenBen = 'D' THEN 'D' END

FROM #tbl_SubscriberTest

WHERE RelCode = 18

)

UPDATE #tbl_SubscriberTest

SET SpBen = Code

FROM #tbl_SubscriberTest Test

INNER JOIN CTE Checker

ON Test.LastName = Checker.LastName

WHERE MedBen <> 'M' OR DenBen <> 'D' OR RelCode = 18;

WITH CTE(LastName) AS(

SELECT LastName FROM #tbl_SubscriberTest

WHERE SpBen = '')

UPDATE #tbl_SubscriberTest

SET SpBen = 'MDX'

FROM #tbl_SubscriberTest Test

INNER JOIN CTE Checker

ON Test.LastName = Checker.LastName

With a half-million rows of data, it takes 30 seconds. Probably not ideal, but this could be a springboard of sorts.

- 😀

hogpen SSC Eights! Points: 931 More actions · Answer 6

I can't thank you both enough! They work. @Ed B I just had to change it to 18 instead of 01 but that's exactly what I needed.