Trying to create two columns with no NULL value and now duplicates in id col

Question

I am trying to get 2 dates, for 2 different activities PER ONE referral number. The dates for these activities is stored in a CustomerHistory table.

My goal is to get 1 row per referral number, and two separate columns, showing the activity dates.

When of the activities does not have a date --> blank cell, but again I want to have 1 row per referral number.

With the current code I get a duplicated row per referral number, when both activities have dates:

SELECT 
    r.Reference, 
    CASE
        WHEN ch.NoteAction = 'Activity2' 
            THEN ch.zu_creationTimeStamp_um
    END AS 'Action_Activity2',
    CASE
        WHEN ch.NoteAction = 'Activity1' 
            THEN ch.zu_creationTimeStamp_um
    END AS 'Action_Activity1', 
    -- ch.zu_creationTimeStamp_um,
    ROW_NUMBER() OVER(PARTITION BY ch.id_Contact, ch.NoteAction ORDER BY ch.zu_creationTimeStamp_um DESC) AS 'row_num' 
FROM 
    hug2.ReferralUpdates r 
LEFT JOIN 
    hug2.CustomerHistory ch ON r.Reference = ch.id_Contact 
WHERE 
    ch.NoteAction = 'Activity2'
    OR ch.NoteAction = 'Activity1'

This returns:

Ref Num	Activity1	Activity2	rn
107971	2024-01-22	(NULL)	1
107971	(NULL)	2024-01-20	1
155666	(NULL)	2024-02-02	1
155666	2024-01-10	(NULL)	1

When what I need to have is:

Ref Num	Activity1	Activity2	rn
107971	2024-01-22	2024-01-20	1
155666	2024-01-10	2024-02-02	1

Answer 1

Something like :

SELECT r.Reference, 
       ch1.zu_creationTimeStamp_um AS 'Action_Activity1',
       ch2.zu_creationTimeStamp_um AS 'Action_Activity2', 
       ROW_NUMBER() OVER(PARTITION BY r.Reference, ch1.NoteAction ORDER BY ch1.zu_creationTimeStamp_um DESC) AS 'row_num' 
FROM   hug2.ReferralUpdates r 
       LEFT OUTER JOIN hug2.CustomerHistory ch1 
          ON r.Reference = ch1.id_Contact AND  ch1.NoteAction = 'Activity1'
       LEFT OUTER JOIN hug2.CustomerHistory ch2 
          ON r.Reference = ch2.id_Contact AND  ch2.NoteAction = 'Activity2';