-11

enter image description here

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.....

2
  • 1
    I want my code immediately... What have you tried so far and what is the problem aside from looking for someone who can do it ASAP? Dec 3, 2015 at 11:31
  • Please add your sample data as a table instead of an image (I formatted the expected output as an example) and try to explain your question a bit better. I think I see what you mean but if I edit it into what I think you mean it might deviate from the original intent. Also, asking for a solution ASAP without trying yourself isn't really helping your case
    – Tom V
    Dec 3, 2015 at 11:53

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.