TimeToCode TimeToCode - 2 months ago 6
SQL Question

Select only the values ​that have one or more specific value in SQL Server

I have the following table called

CampaingControl
:

+----------------+-----+-------------+--------------+-----------+--------+
| id_vehicleCamp | vin | id_campaign | id_workorder | id_client | status |
+----------------+-----+-------------+--------------+-----------+--------+
| | | | | | |
+----------------+-----+-------------+--------------+-----------+--------+
| | | | | | |
+----------------+-----+-------------+--------------+-----------+--------+


The
status
column have only two values APPLIED or PENDING.

What i need to do is show all the
distinct
values ​​in the column
id_campaign
having one or more
status
values ​​equals to PENDING.

For example, i have this data in the table:

+----------------+-------+-------------+--------------+-----------+---------+
| id_vehicleCamp | vin | id_campaign | id_workorder | id_client | status |
+----------------+-------+-------------+--------------+-----------+---------+
| 1 | 43006 | 1 | OT-001-16 | NULL | PENDING |
+----------------+-------+-------------+--------------+-----------+---------+
| 2 | 43010 | 1 | OT-002-16 | 2 | PENDING |
+----------------+-------+-------------+--------------+-----------+---------+
| 3 | 43009 | 1 | OT-002-16 | NULL | APPLIED |
+----------------+-------+-------------+--------------+-----------+---------+
| 4 | 43008 | 2 | OT-002-16 | NULL | APPLIED |
+----------------+-------+-------------+--------------+-----------+---------+
| 5 | 43002 | 3 | OT-005-16 | 4 | PENDING |
+----------------+-------+-------------+--------------+-----------+---------+


The final output needs to be:

+-------------------+
| Pending Campaings |
+-------------------+
| 1 |
+-------------------+
| 3 |
+-------------------+


The campaing with value
1
have one APPLIED
status
, but have
2
with PENDING
status
, then isn't completed.

The campaing with value
3
only have one record and have PENDING
status
, then isn't completed.

I hope I explained correctly. Any question post on comments.

Answer

Use conditional aggregation:

SELECT id_campaign
FROM CampaingControl
GROUP BY id_campaign
HAVING SUM(CASE WHEN status = 'PENDING' THEN 1 ELSE 0 END) >= 1

Note that this query will generalize to other questions you might have, such as how many campaigns have two pending entries but only one applied entry.