Need Immediate Help To Develope T-SQL

Hi DBAs,

I want to Develop a T-SQL For Fetch data from following Table.

But Condition is : I want to select only those data which status has all three status.

suppose for id 1 : It has Only two status temp and delete, then it should not be populate. And for id 2 it has 3 statuses Temp,Delete and Schedule. So, id 2 should be populate but a row with temp status should not be get populate.

output should be as follow:

+----------+----------+----------+----------+
| p_id     | id       | name     | status   |
+----------+----------+----------+----------+
| 4        | 2        | Raju     | Schedule |
| 5        | 2        | Raju     | Deleted  |
| 7        | 3        | Viru     | Schedule |
| 8        | 3        | Viru     | Delete   |
+----------+----------+----------+----------+

Please help me…..

The query below should return the desired results if the only 3 possible status values are those listed in your image.

SELECT  p_id
      , id
      , name
      , status
FROM    dbo.Foo
WHERE   status <> 'temp'
        AND id IN ( SELECT  id
                    FROM    dbo.Foo
                    GROUP BY id
                    HAVING  COUNT(DISTINCT status) = 3 );

Help us help you by providing CREATE TABLE, INSERT statements in your question, along with the query you’ve tried. That will help expedite an answer.