SQL Server – How to Select Preferred Row Based on Condition

azurequerysql server

I'm new to SQL coding and I'm struggling to find a solution to my issue.

I have a database with contractors that have worked at our organisation on and off.

The database contains the contractors employment number and their current/past contracts.

I need to check where the contractor has more than one row, that if there is a contractor_code = '24', to also check if there is a contractor_code = '60'. If true then select the contractor_code = '60 row and data and give the CASE multiple rows count of '1'.

Sample Data:

enter image description here

SQL Code:

SELECT DISTINCT 
       contractor_no,
       CASE WHEN contractor_code = '24' AND contractor_code = '60'
            THEN 1 
            END AS multiple_roles
FROM contractor_header ch
LEFT JOIN service_history sh ON sh.contractor_no = ch.contractor_no
ORDER BY multiple_roles DESC

The table gives all contractor_no multiple_roles value of NULL.

I can't eliminate the rows based on if their active or finished as I want to count a contractor even if they're finished. Some columns don't have data in date_commenced or date_finished.

Any suggestions would be great, even if I'm way off track with a Case statement.

Best Answer

If there is no another contractor_code values except 24 and 60, then 

SELECT /* ch.contractor_no */ sh.contractor_no ,
       CASE WHEN COUNT(DISTINCT sh.contractor_code) = 2
            THEN 1 
            END AS multiple_roles
FROM contractor_header ch
LEFT JOIN service_history sh ON sh.contractor_no = ch.contractor_no
GROUP BY /* ch.contractor_no */ sh.contractor_no
ORDER BY multiple_roles DESC

If there exists another contractor_code values except 24 and 60, then

SELECT /* ch.contractor_no */ sh.contractor_no ,
       CASE WHEN COUNT(DISTINCT CASE WHEN sh.contractor_code IN (24, 60)
                                     THEN sh.contractor_code
                                     END) = 2
            THEN 1 
            END AS multiple_roles
FROM contractor_header ch
LEFT JOIN service_history sh ON sh.contractor_no = ch.contractor_no
GROUP BY /* ch.contractor_no */ sh.contractor_no
ORDER BY multiple_roles DESC
Related Question