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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
Aged Yak Warrior

USA
550 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
52326 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  
 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.03 seconds. Powered By: Snitz Forums 2000