Subquery for MAX() value from LeftJoin

You can aggregate without a subquery:

SELECT otherCol1
    ,MAX(tu.JoinDate) AS UnsubscribedDate
    ,MAX(trkOp.Clicktime) AS OpenedDate
    ,MAX(trkRes.Clicktime) AS ClickedDate
FROM v_members_email_occurrence vmec
LEFT JOIN tracking_unsubscribed tu WITH (NOLOCK) ON tu.ReportId = vmec.SendingId
LEFT JOIN tracking_opened trkOp WITH (NOLOCK) ON trkOp.ReportId = vmec.SendingId
LEFT JOIN tracking_result trkRes WITH (NOLOCK) ON trkRes.ReportId = vmec.SendingId
WHERE vmec.MemberId = @MemberId
GROUP BY otherCol1

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top