I have a report to generate listing distinct nurse visits, however, we went through a transition of the way we ID our nurse types. I need to display the new type, but for the nurses we haven't transition, they may have several ID types. This is my current output:
| Visit ID | Visit Date | Nurse Name | Nurse ID Type |
|---|---|---|---|
| 5002 | 6/5/2022 | Betty Jones | 349 |
| 5002 | 6/5/2022 | Betty Jones | 292919 |
| 5014 | 6/15/2022 | Bill Clark | 349 |
| 5014 | 6/15/2022 | Bill Clark | 292919 |
| 5014 | 6/15/2022 | Bill Clark | 292929 |
| 5033 | 6/22/2022 | Sue Smith | 292919 |
This is the output display I need to get:
| Visit ID | Visit Date | Nurse Name | Nurse ID Type |
|---|---|---|---|
| 5002 | 6/5/2022 | Betty Jones | 349 |
| 5014 | 6/15/2022 | Bill Clark | 349 |
| 5033 | 6/22/2022 | Sue Smith | 292919 |
Here is my code so far, but this still gets me the duplicates.
SELECT
[Visit ID]
,[Visit Date]
,[Nurse Name]
,CASE WHEN [Nurse ID Type] = 349 THEN [Nurse ID Type] ELSE MIN([Nurse ID Type] END 'Nurse ID Type'
FROM Visits
GROUP BY
[Visit ID],[Visit Date],[Nurse Name],[Nurse ID Type]