CODEWITHSUNDEEP
sql
Rachsherry
Rachsherry

Reputation: 145

Return one record from multiple records

I have two tables, which when combined will give me the following information 

JobID     Task ID     Task Status
1           1            1
1           2            3
1           3            1
1           4            1
1           5            3
2           1            1
2           3            1 
2           5            1

What I would like to display is one line for each job ID and the status based on the following
When all task statues are 1 then the job status is "Started"
If one task status is set to 3 then the job status is "Issued"
so the following will be true for the table above 

Job ID   Job Status
 1        Issued
 2        Started

Upvotes: 1

Views: 66

Answers (3)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726499

Use GROUP BY to combine items with identical job IDs. Use MIN(TaskStatus) and MAX(TaskStatus) to see if all items are the same or not:

SELECT
    JobId
,   CASE
        WHEN MIN(TaskStatus) = 1 AND MAX(TaskStatus) = 1 THEN 'Started'
        WHEN MAX(TaskStatus) = 3 THEN 'Issued'
        ELSE NULL
    END AS JobStatus
FROM MyTable
GROUP BY JobId

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

This answer uses full conditional aggregation, under the assumption that down the road your data might change.

SELECT
    [Job ID],
    CASE WHEN SUM(CASE WHEN [Task Status] = 3 THEN 1 ELSE 0 END) > 0
         THEN 'Issued'
         WHEN SUM(CASE WHEN [Task Status] = 1 THEN 1 ELSE 0 END) = COUNT(*)
         THEN 'Started'
         ELSE 'Other' END AS [Job Status]
FROM yourTable
GROUP BY
    [Job ID];

For more concise versions of this query assuming the only statuses are 1 and 3, see the other answers.

Upvotes: 3

kchason
kchason

Reputation: 2885

You can use the MAX function to determine if there is a 3 in the list.

SELECT
    JobID,
    CASE
        WHEN MAX(TaskStatus) = 1 THEN 'Started',
        WHEN MAX(TaskStatus) = 3 THEN 'Issued'
        ELSE 'No Status'
    END AS 'JobStatus'
FROM
    table
GROUP BY
    JobID

Upvotes: 3

Related Questions