Comparing records against multiple rows

Question

Comparing records against multiple rows

hogpen

SSC Eights!

Points: 931
More actions
September 20, 2013 at 10:57 am

#282858

I'm completely lost on how to do this. I basically want to do a CASE statement that looks at multiple rows.
I need look at all the people based on what the main person has (RelCode = 18).
Example for Sue: Case when Joe Smith's MedBen = M and Sue's MedBen = '' then SpBen = D-D
Example for Kate: Case when Jay Evans's MedBen = M and Kate's MedBen = M then SpBen = D
Sample data:
SubsNum | FName | LName | PlanNum | MedBen | RelCode | SpBen
123 ++++| Joe ++++| Smith | FH +++| M ++++| 18 ++++|
123 ++++| Sue ++++| Smith | DH +++| +++++| 19 ++++|
123 ++++| Ale ++++ | Smith | DH +++| +++++| 19 ++++|
245 ++++| Jay ++++| Evans | FH +++| M ++++| 18 ++++|
245 ++++| Kate ++++| Evans | FH +++| M ++++| 19 ++++|
245 ++++| Mike ++++ | Evans | FH +++| M ++++| 19 ++++|
CREATE TABLE [dbo].[tbl_SubscriberTest](
[SubscriberNum] [varchar](50) NULL,
[SSN] [varchar](11) NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[PlanNum] [varchar](50) NULL,
[MedBen] [varchar](1) NULL,
[DenBen] [varchar](1) NULL,
[RelCode] [varchar](3) NULL,
[SpBen] [varchar](4) NULL) ON [PRIMARY]
INSERT [dbo].[tbl_SubscriberTest] ([SubscriberNum],[SSN],[FirstName],[LastName],
[PlanNum],[MedBen],[DenBen],[RelCode],[SpBen])
VALUES( '1234', '1111', 'MILAN','WHITEHURST','DENT','', 'D','01',''),
( '1234', '2222', 'NICOLAS','WHITEHURST','DENT','', 'D','19', ''),
( '1234', '1234', 'MONIQUE','WHITEHURST','DENT','', 'D','18',''),
( '2468', '2468', 'WILLIAM','CARPENTER','FCNB', 'M','D','18',''),
( '2468', '3333', 'ALEXANDRIA','CARPENTER','FCNB', 'M','D','19',''),
( '2468', '4444', 'SHAYLENA', 'CARPENTER','FCNB', 'M','D','19', ''),
( '2468', '7894', 'JOANN','CARPENTER','FCNB', 'M','D','01',''),
( '3692', '3692', 'JOE','WALKER','FHL','M', 'D','18',''),
( '3692', '9632', 'JOHN','WALKER','DENT', '', 'D','19',''),
( '3692', '3574', 'JAMES','WALKER','DENT','', 'D','19',''),
( '3692', '7531', 'JAKE','WALKER','DENT', '', 'D','19', '')
Does that make sense? In the data from the SQL code,anyone with a RelCode of 18 is the main employee. So if the MedBen for the main employee is M but any of the other people in that group of same SubscriberNum have '' for MedBen then I'd need to change the SpBen to D-D. Example: Joe, John, James, and Jake Walker would have D-D for SpBen but the Whitehurst family would just have D since Monique's MedBen field is blank.
I'm desperate!

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

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

Sean Lange SSC Guru Points: 286601 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: 3679 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.

Comparing records against multiple rows

Cookies on SQLServerCentral