SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Comparing data from multiple rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

photond
Starting Member

20 Posts

Posted - 09/20/2013 :  12:30:21  Show Profile  Reply with Quote
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

India
169 Posts

Posted - 09/21/2013 :  02:28:32  Show Profile  Reply with Quote
UPDATE tbl_SubscriberTest
SET SpBen = 'D-D'
WHERE MedBen = ''
AND RelCode != 18
AND FirstName IN ('JOE','JOHN','JAMES','JAKE')

veeranjaneyulu

Edited by - VeeranjaneyuluAnnapureddy on 09/21/2013 02:29:45
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
411 Posts

Posted - 09/21/2013 :  23:25:56  Show Profile  Reply with Quote
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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 09/22/2013 :  03:09:29  Show Profile  Reply with Quote

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000