SIMPLE SENARIO

CREATE TABLE [DBO].[PROJECT](
[ENAME] [VARCHAR](MAX) NULL,
[PID] [INT] NULL,
[STATUS] [INT] NULL
) ON [PRIMARY]
GO

INSERT PROJECT VALUES ('bala', 1, NULL)
INSERT PROJECT VALUES ('manoj', 1, 1)
INSERT PROJECT VALUES ('krishna', 1, 1)
INSERT PROJECT VALUES ('a', 1, 1)
INSERT PROJECT VALUES ('b', 2, NULL)
INSERT PROJECT VALUES ('c', 2, 0)
INSERT PROJECT VALUES ('d', 3, NULL)
INSERT PROJECT VALUES ('e', 3, 1)
INSERT PROJECT VALUES ('f', 3, 1)

PROJECT DONE:

;WITH X AS
(
SELECT PID,COUNT(PID) PD,SUM(STATUS) STATU FROM PROJECT
GROUP BY PID,STATUS HAVING STATUS IS NOT NULL
)SELECT P.* FROM  PROJECT P JOIN X B ON B.PID=P.PID
 WHERE B.PD=B.STATU AND STATUS IS NULL



 ;WITH X AS( SELECT PID,COUNT(PID) AS COU_PID,SUM(status) AS STAU FROM project
 GROUP BY pid,status  HAVING status IS  NOT NULL)

 SELECT PID FROM X  WHERE COU_PID=STAU

PROJECT NOT DONE 


;WITH X AS
(
SELECT PID,COUNT(PID) PD,SUM(STATUS) STATU FROM PROJECT
GROUP BY PID,STATUS HAVING STATUS IS NOT NULL
)SELECT P.* FROM  PROJECT P JOIN X B ON B.PID=P.PID
 WHERE B.PD<>B.STATU AND STATUS IS NULL