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 allselect 234, 'MR13', 'A', '7/19/2009', 'OU' union allselect 123, 'MA23', 'A', '8/20/2009', 'OU' union allselect 456, 'MR56', 'A', '7/10/2009', 'SR' union allselect 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 ,servicetypefrom cteOUTPUT:memid authno status servicedate servicetype----------- ------ ------ ----------------------- -----------123 MR12 D 2009-08-20 00:00:00.000 IH789 MR89 D 2009-08-02 00:00:00.000 DM123 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 ,servicetypefrom @twhere status = 'd'union allselect t2.memid ,t2.authno ,t2.[status] ,t2.servicedate ,t2.servicetypefrom @t t1join @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 OptimizerTG