Summary
How to modify my SQL statement to remove redundancies
Content
Hi! I've created a report to return all Open Requisitions as well as Filled Requisitions for the past year. The report filters work well, however I have additionally created a field using the following SQL statement in an attempt to display Candidate Name only when they match specific Steps and Status conditions:
CASE WHEN "Submission CSW Status - Current"."Current Step Name" IN ('Offer', 'Pre-Hire') AND "Submission CSW Status - Current"."Current Status Name" IN ('Accepted', 'Extended', 'Approval in Progress', 'Approved', 'Draft', 'Offer to be made', 'Candidate Record to be updated', 'Integration To WorkDay') THEN "Candidate Identification"."Name" ELSE ' ' END
The names are populating as desired, however I am getting an additional row for Requisitions that not only have an Offer/Hired Candidate against them, but also have other Candidate submissions which simply aren't named based off the above SQL statement. Basically the extra row is representing a placeholder for all the other Candidates that have applied but do not meet the conditions of the SQL statement.
Any ideas on how to suppress those extra blank rows? If a Req does not have anyone in the Offer/Hire process, then I would still want the Req. to be displayed. In that case the Candidate Name should be blank.
Below is a sample of what's happening, however the attached doc is a littler easier to read. Thanks in advance for any advice!
| Requisition Number_Position Number |
Name |
| 0044262_P0048293 |
John Smith |
| 0044262_P0048293 |
|
| 0044360_P-0019999 |
Jane Doe |
| 0044360_P-0019999 |
|
| 0044365_P0048452 |
Charlie Johnson |
| 0044366_P0048453 |
Sally Thomas |
| 0044367_P0048454 |
John Doe |
| 0044372_P0048457 |
Jeff Johnson |
| 0044372_P0048457 |
|
