/*Create the table*/
CREATE TABLE Apps
(
	ID					VARCHAR(50)			NOT NULL,
	Stat				VARCHAR(50)			NOT NULL,
	StatDate			DATE				NOT NULL
)
;

/*Putting data in the table to create the test enviroment*/
INSERT INTO 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')
;

ID Stat StatDate
1 AppRec 2012-12-01
1 AppCom 2012-12-02

SELECT ID, Stat, StatDate
FROM Apps
WHERE ID IN (SELECT ID FROM Apps WHERE Stat = 'AppRec' AND StatDate >= '2012-12-01')

;WITH x AS 
(	SELECT ID, Stat, StatDate
	FROM #Apps
	WHERE Stat='AppRec' AND StatDate >= '2012-12-01' ) 
SELECT ID, Stat, StatDate 
FROM #Apps
WHERE ID in (SELECT ID FROM x)

WHERE [column] IN ([subquery]) 

WHERE EXISTS ([subquery])

/*Create the table*/
CREATE TABLE dbo.Apps
(
	ID					VARCHAR(50)			NOT NULL,
	Stat				VARCHAR(50)			NOT NULL,
	StatDate			DATE				NOT 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