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
 General SQL Server Forums
 New to SQL Server Programming
 Query help

Author  Topic 

lbeese
Starting Member

24 Posts

Posted - 2009-08-25 : 12:11:37
I have the following table:
memid authno status servicedate servicetype
123 MR12 D 8/20/2009 IH
234 MR13 A 7/19/2009 OU
123 MA23 A 8/20/2009 OU
456 MR56 A 7/10/2009 SR
789 MR89 D 8/2/2009 DM

What I want to do is pull all records with "D" as the status. But if the service type = IH, I also want to pull any records for the same memid with the same service date with a servicetype of OU. Any assistance would be appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-25 : 13:20:22
Is this what you want?

declare @t table (memid int, authno varchar(4), status char(1), servicedate datetime, servicetype varchar(2))
insert @t
select 123, 'MR12', 'D', '8/20/2009', 'IH' union all
select 234, 'MR13', 'A', '7/19/2009', 'OU' union all
select 123, 'MA23', 'A', '8/20/2009', 'OU' union all
select 456, 'MR56', 'A', '7/10/2009', 'SR' union all
select 789, 'MR89', 'D', '8/2/2009 ', 'DM'

;with cte as
(
select memid
,authno
,[status]
,servicedate
,servicetype
from @t
where status = 'd'
union all
select t2.memid
,t2.authno
,t2.[status]
,t2.servicedate
,t2.servicetype
from cte t1
join @t t2
on t2.memid = t1.memid
and t2.servicedate = t1.servicedate
and t1.servicetype = 'IH'
and t2.servicetype = 'OU'
)
select memid
,authno
,[status]
,servicedate
,servicetype
from cte

OUTPUT:

memid authno status servicedate servicetype
----------- ------ ------ ----------------------- -----------
123 MR12 D 2009-08-20 00:00:00.000 IH
789 MR89 D 2009-08-02 00:00:00.000 DM
123 MA23 A 2009-08-20 00:00:00.000 OU



EDIT:
assuming that is correct then it doesn't need to be a CTE. This works too:

select memid
,authno
,[status]
,servicedate
,servicetype
from @t
where status = 'd'
union all
select t2.memid
,t2.authno
,t2.[status]
,t2.servicedate
,t2.servicetype
from @t t1
join @t t2
on t2.memid = t1.memid
and t2.servicedate = t1.servicedate
and t1.servicetype = 'IH'
and t2.servicetype = 'OU'
and t1.status = 'D'


Be One with the Optimizer
TG
Go to Top of Page

lbeese
Starting Member

24 Posts

Posted - 2009-08-26 : 08:58:08
Thank you for the help!
Go to Top of Page
   

- Advertisement -