| Author |
 Topic  |
|
|
photond
Starting Member
20 Posts |
 Posted - 09/20/2013 : 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
India
169 Posts |
Posted - 09/21/2013 : 02:28:32
|
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 |
 |
|
|
ScottPletcher
Aged Yak Warrior
USA
550 Posts |
Posted - 09/21/2013 : 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
India
52326 Posts |
Posted - 09/22/2013 : 03:09:29
|
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
|
|
|
| |
Topic |
|
|
|
Comparing data from multiple rows