Comparing data from multiple rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Comparing data from multiple rows

Author

Topic

photond
Starting Member

20 Posts

Posted - 2013-09-20 : 12:30:21

I've been staring at this and trying to figure it out for a while. I'm guessing it might require a PIVOT function or something, but I'm lost and not even sure if that's the right way to attack the problem.

I want to be able to compare PlanNum and MedBen info grouped by the ProviderNum and then update the SpBen field.

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',   '')

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 hope that makes sense. Essentially I just need to compare one row of data based on other rows that are grouped by the same SubscriberNum.

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-21 : 02:28:32

UPDATE tbl_SubscriberTest
SET SpBen = 'D-D'
WHERE MedBen = ''
AND RelCode != 18
AND FirstName IN ('JOE','JOHN','JAMES','JAKE')

veeranjaneyulu

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-21 : 23:25:56

I wasn't sure if/what you wanted to see SpBen to if the both the main employee and the other beneficiaries were 'M', so I left that part commented out -- we will need to set it appropriately for the code to work.

For example, if you don't want to make any changes in that situation, we need to add a HAVING to the inner query to exclude those SubscriberNums from the result set so they don't get updated at all.



UPDATE st
SET SpBen = CASE 
    WHEN main_employee_MedBen = 'M' AND other_MedBen = '' THEN 'D-D'
    --WHEN main_employee_MedBen = 'M' AND other_MedBen = 'M' THEN '???'
    ELSE 'D' END
FROM tbl_SubscriberTest st
INNER JOIN (
    SELECT 
        SubscriberNum,
        MAX(CASE WHEN SubscriberNum = SSN THEN MedBen END) AS main_employee_MedBen,
        MAX(CASE WHEN SubscriberNum <> SSN THEN MedBen END) AS other_MedBen
    FROM tbl_SubscriberTest
    GROUP BY SubscriberNum
) AS st_MedBens ON
    st_MedBens.SubscriberNum = st.SubscriberNum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 03:09:29

[code]
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', '')

UPDATE t
SET SpBen = CASE WHEN MedBenMCnt > 0 THEN 'D-D' WHEN MedBenBlnkCnt > 0 THEN 'D' END
FROM
(
SELECT SUM(CASE WHEN [RelCode] = 18 AND [MedBen] = 'M' THEN 1 ELSE 0 END) OVER (PARTITION BY [SubscriberNum]) AS MedBenMCnt,
SUM(CASE WHEN [RelCode] = 18 AND [MedBen] = '' THEN 1 ELSE 0 END) OVER (PARTITION BY [SubscriberNum]) AS MedBenBlnkCnt,
SpBen
FROM [dbo].[tbl_SubscriberTest]
)t

SELECT * FROM [dbo].[tbl_SubscriberTest]

output
-------------------------------------------------------------------------------------------------
SubscriberNum SSN FirstName LastName PlanNum MedBen DenBen RelCode SpBen
-------------------------------------------------------------------------------------------------
1234 1111 MILAN WHITEHURST DENT D 01 D
1234 2222 NICOLAS WHITEHURST DENT D 19 D
1234 1234 MONIQUE WHITEHURST DENT D 18 D
2468 2468 WILLIAM CARPENTER FCNB M D 18 D-D
2468 3333 ALEXANDRIA CARPENTER FCNB M D 19 D-D
2468 4444 SHAYLENA CARPENTER FCNB M D 19 D-D
2468 7894 JOANN CARPENTER FCNB M D 01 D-D
3692 3692 JOE WALKER FHL M D 18 D-D
3692 9632 JOHN WALKER DENT D 19 D-D
3692 3574 JAMES WALKER DENT D 19 D-D
3692 7531 JAKE WALKER DENT D 19 D-D

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources