September 20, 2013 at 10:57 am
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!
September 20, 2013 at 11:57 am
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/
September 20, 2013 at 12:03 pm
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?
- 😀
September 20, 2013 at 12:45 pm
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.
September 20, 2013 at 2:20 pm
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
September 20, 2013 at 2:34 pm
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.
- 😀
September 20, 2013 at 3:59 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply