November 18, 2005 at 7:13 am
Greetings gurus...
I have the following sql in a union (but I'm not sure that a union does what I want to do... and the only difference between the two sections of the union is one thing in the where clause...
SELECT
A.EMPLID,
A.FIRST_NAME,
A.BENEFIT_PLAN as MedicalPlan,
'' as DentalPlan
FROM dbo.PS_BENEFITS_DATA A INNER JOIN dbo.PS_EMPLOYMENT B ON
A.EMPLID = B.EMPLID
AND A.EMPL_RCD = B.EMPL_RCD
WHERE (A.EFFDT = (SELECT MAX(A_ED.EFFDT)
FROM PS_BENEFITS_DATA A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A.COBRA_EVENT_ID = A_ED.COBRA_EVENT_ID
AND A.DEPENDENT_BENEF = A_ED.DEPENDENT_BENEF
AND A.PLAN_TYPE = A_ED.PLAN_TYPE
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE(), 121), 1, 10)))
AND (A.EMPLID = '00849')
AND A.PLAN_TYPE ='10'
union
SELECT
A.EMPLID,
A.FIRST_NAME,
'' as MedicalPlan,
A.BENEFIT_PLAN as DentalPlan
FROM dbo.PS_BENEFITS_DATA A INNER JOIN dbo.PS_EMPLOYMENT B ON
A.EMPLID = B.EMPLID
AND A.EMPL_RCD = B.EMPL_RCD
WHERE (A.EFFDT =(SELECT MAX(A_ED.EFFDT)
FROM PS_BENEFITS_DATA A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A.COBRA_EVENT_ID = A_ED.COBRA_EVENT_ID
AND A.DEPENDENT_BENEF = A_ED.DEPENDENT_BENEF
AND A.PLAN_TYPE = A_ED.PLAN_TYPE
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE(), 121), 1, 10)))
AND (A.EMPLID = '00849')
AND (A.PLAN_TYPE = '11')
What gets returned is this... (two rows per person)...
The big question... how do I set this up to only have one row per person?
EMPLID FIRST_NAME MedicalPlan DentalPlan
----------- ------------------------------ ----------- ----------
00849 David DENDED
00849 David MEDPPO
00849 Elizabeth DENDED
00849 Elizabeth MEDPPO
00849 Emily DENDED
00849 Emily MEDPPO
00849 Matt DENDED
00849 Matt MEDPPO
00849 Stacey DENDED
00849 Stacey MEDPPO
Thanks for any help or direction you can give me!
Bob
November 18, 2005 at 8:14 am
This is a simplified version but should give you the necessary pointers...
create table #medPlan(empID char(5), Plan_Type char(2), First_Name varchar(20), MedicalPlan char(6), DentalPlan char(6))
insert into #medPlan values('00849', '10', 'remi', 'MEDPPO', '')
insert into #medPlan values('00849', '11', 'remi', '', 'DENDED')
insert into #medPlan values('00849', '10', 'noel', 'MEDPPO', '')
insert into #medPlan values('00849', '11', 'noel', '', 'DENDED')
insert into #medPlan values('00849', '10', 'david', 'MEDPPO', '')
insert into #medPlan values('00849', '11', 'david', '', 'DENDED')
insert into #medPlan values('00850', '10', 'farrell', 'MEDPPO', '')
insert into #medPlan values('00850', '11', 'farrell', '', 'DENDED')
insert into #medPlan values('00851', '10', 'yelena', 'MEDPPO', '')
insert into #medPlan values('00851', '11', 'yelena', '', 'DENDED')
select * from #medPlan
select A.empID,
A.First_Name,
Max(A.MedicalPlan) as MedicalPlan,
Max(A.DentalPlan) as DentalPlan
from
(select empID, First_Name, MedicalPlan, DentalPlan from #medPlan
where empID = '00849' and Plan_Type in (10, 11))A
group by A.empID, A.First_Name
order by A.First_Name
drop table #medPlan
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply