SQL Case Statement to display main ID Type Else give Minimum ID Type

Viewed 11

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]
0 Answers
Related