0

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.

2
  • This is more fit on Stack Overflow (programming question) than on this one (DBA).
    – Alexei
    Feb 19, 2020 at 5:32
  • I think contractor_code = '24' AND contractor_code = '60' would be wrong since a value cant be 24 AND 60 at the same time. Try it with an OR. Also contractor_code IN (24,60) will work
    – Mehmet
    Feb 19, 2020 at 6:31

1 Answer 1

1

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
2
  • Hi Akina, thanks for the answer. I'm getting NULL appearing in multiple_roles even when the contractor has both '24' and '60' in their contractor_code. There are other contractor_codes in the database so I'm working off your 2nd answer. Any suggestions?
    – Chris
    Feb 24, 2020 at 0:08
  • @Chris Edited, try in new variant. If the problem persists then provide some sample fiddle which reproduces the problem.
    – Akina
    Feb 24, 2020 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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