Here is the code using the EXISTS in the WHERE clause:

/*Create the table*/

CREATE TABLE dbo.Apps

(

IDVARCHAR(50)NOT NULL,

StatVARCHAR(50)NOT NULL,

StatDateDATENOT NULL

)

;

/*Putting data in the table to create the test enviroment*/

INSERT INTO dbo.Apps

(

ID,

Stat,

StatDate

)

Values

('1', 'AppRec', '2012-12-01'),

('1', 'AppCom', '2012-12-02'),

('2', 'AppRec', '2012-11-01'),

('2', 'AppCom', '2012-11-01'),

('3', 'AppRec', '2012-11-15'),

('3', 'AppCom', '2012-12-01')

;

go

select

a1.ID,

a1.Stat,

a1.StatDate

from

dbo.Apps a1

where exists(select

1

from

dbo.Apps a2

where

a1.ID = a2.ID and

a2.Stat = 'AppRec' and

a2.StatDate >= '20121201');

go

drop table dbo.Apps;

go