SQL Query Syntax issue

Yes, your code is quite extensive and seems somewhat complex in terms of many case when statements, but we can leave that for another time. As a suggestion, you could talk to your IT team about generating a table in the database where you can consult these results directly. In the meantime, to validate the connection, I rewrote the code with a few minor adjustments and filtering only one date.

;WITH CTE_NTT AS (
    -- CTE for selecting notification tracking tasks
    SELECT *
    FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]
    WHERE NotificationTypeLookupID IN (
        '25'  -- TaskRespondWorkflowInactiveNotification
        ,'14'  -- TaskRespondToPermanentlyLockedCase
        ,'13'  -- TaskRespondToSilentNotification
        ,'12'  -- Task (Review Doc Service Document)
        ,'9'   -- TaskRespondToCommunication
        ,'8'   -- TaskCompleteParkedCall
        ,'7'   -- WorkflowLockedCase
    )
    AND CONVERT(DATE, TaskCreatedDateTime) = '2023-01-01' -- Filter for a single day
),
CTE_CASES AS (
    -- CTE to gather information related to cases
    SELECT
        CTR.CaseTrackingRecordID,
        T.TopicID,
        T.TopicTitle,
        CTR.RowStartDate,
        CTR.RowEndDate,
        OWN.Forename + ' ' + OWN.Surname AS CaseOwner,
        OWN.EmailAddress AS CaseOwnerEmailAddress,
        ADV.Forename + ' ' + ADV.Surname AS CaseAdviser,
        ISNULL(ADV.EmailAddress, '[email protected]') AS CaseAdviserEmailAddress,
        W_STAGE.WorkflowStageTitle AS WorkflowStage,
        W_STEP.WorkflowStepTitle AS WorkflowStep
    FROM [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseTrackingRecord] AS CTR
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseStatus] AS CS
        ON CTR.CaseStatusKey = CS.CaseStatusKey
        AND CS.IsValidCase = 1
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS EMP
        ON CTR.EmployeeUserKey = EMP.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN
        ON CTR.OwnerUserKey = OWN.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS ADV
        ON CTR.CurrentAdviserKey = ADV.empowerUserKey
        AND ADV.IsTestAccount = 0
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[dim_empower_topic] AS T
        ON CTR.TopicKey = T.TopicKey
        AND T.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStage] AS W_STAGE
        ON CTR.WorkflowStageKey = W_STAGE.WorkflowStageKey
        AND W_STAGE.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStep] AS W_STEP
        ON CTR.WorkflowStepKey = W_STEP.WorkflowStepKey
        AND W_STEP.RowIsCurrent = 'Y'
)
SELECT DISTINCT
    CASE 
        WHEN NTT.NotificationTypeLookupID = 9 AND CTC.CommunicationSubject = 'Request for Help' THEN 'Help Request'
        WHEN NTT.NotificationTypeLookupID = 9 THEN 'Response Required Case Note'
        WHEN NT.NotificationType = 'TaskRespondToPermanentlyLockedCase' THEN 'Permanently Locked Case'
        WHEN NT.NotificationType = 'TaskRespondToSilentNotification' THEN 'Silent Notification'
        WHEN NT.NotificationType = 'Task (Review Doc Service Document)' THEN 'Doc Review'
        WHEN NT.NotificationType IN ('Task (Complete Parked Call)', 'TaskCompleteParkedCall') THEN 'Parked Call'
        WHEN NT.NotificationType = 'WorkflowLockedCase' THEN 'Workflow Locked Case'
        WHEN NT.NotificationType = 'TaskRespondWorkflowInactiveNotification' THEN 'Inactive Notification'
    END AS [Contact Type],
    CONVERT(DATE, NTT.TaskCreatedDateTime) AS [Contact Date],
    CONVERT(VARCHAR(8), NTT.TaskCreatedDateTime, 108) AS [Contact Time],
    NTTDT.CaseTrackingRecordID AS [Case Number],
    CASES.TopicTitle AS [Topic Title],
    CASES.WorkflowStage AS [Workflow Stage],
    CASES.WorkflowStep AS [Workflow Step],
    NTT.NotificationTrackingTaskID AS [Contact ID],
    CASES.CaseAdviser AS [Adviser],
    CASES.CaseAdviserEmailAddress AS [EMAIL]
FROM CTE_NTT AS NTT WITH (NOLOCK)
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_NotificationType] AS NT WITH (NOLOCK)
    ON NT.NotificationTypeID = NTT.NotificationTypeLookupID
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_Date] AS DD WITH (NOLOCK)
    ON NTT.TaskCreatedDateKey = DD.DateKey
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationDistributions] AS NTTDT
    ON NTTDT.NotificationDistributionID = NTT.NotificationDistributionID
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_CaseTrackingCommunication] AS CTC WITH (NOLOCK)
    ON CTC.CaseTrackingRecordID = NTTDT.CaseTrackingRecordID
    AND ISNULL(CTC.IsDraft, 0) = 0
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN WITH (NOLOCK)
    ON OWN.UserID = CTC.SenderUserID
    AND OWN.RowIsCurrent = 'Y'
INNER JOIN CTE_CASES AS CASES
    ON NTTDT.CaseTrackingRecordID = CASES.CaseTrackingRecordID
    AND NTT.TaskCreatedDateTime BETWEEN CASES.RowStartDate AND CASES.RowEndDate
    AND ISNULL(RIGHT(CASES.CaseAdviserEmailAddress, 15), '') LIKE 'adviserplus.com'
WHERE ISNULL(OWN.EmailAddress, '') NOT LIKE '%adviserplus%'

See if that works.

Your original query:

;WITH CTE_NTT AS (
    -- CTE for selecting notification tracking tasks
    SELECT *
    FROM [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationTrackingTasks]
    WHERE NotificationTypeLookupID IN (
        '25'  -- TaskRespondWorkflowInactiveNotification
        ,'14'  -- TaskRespondToPermanentlyLockedCase
        ,'13'  -- TaskRespondToSilentNotification
        ,'12'  -- Task (Review Doc Service Document)
        ,'9'   -- TaskRespondToCommunication
        ,'8'   -- TaskCompleteParkedCall
        ,'7'   -- WorkflowLockedCase
    )
    AND CONVERT(DATE, TaskCreatedDateTime) BETWEEN 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(MONTH, -25, CONVERT(DATE, GETDATE() - 1))), 0) 
        AND CONVERT(DATE, GETDATE() - 1)
),
CTE_CASES AS (
    -- CTE to gather information related to cases
    SELECT
        CTR.CaseTrackingRecordID,
        T.TopicID,
        T.TopicTitle,
        CTR.RowStartDate,
        CTR.RowEndDate,
        OWN.Forename + ' ' + OWN.Surname AS CaseOwner,
        OWN.EmailAddress AS CaseOwnerEmailAddress,
        ADV.Forename + ' ' + ADV.Surname AS CaseAdviser,
        ISNULL(ADV.EmailAddress, '[email protected]') AS CaseAdviserEmailAddress,
        W_STAGE.WorkflowStageTitle AS WorkflowStage,
        W_STEP.WorkflowStepTitle AS WorkflowStep
    FROM [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseTrackingRecord] AS CTR
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_CaseStatus] AS CS
        ON CTR.CaseStatusKey = CS.CaseStatusKey
        AND CS.IsValidCase = 1
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS EMP
        ON CTR.EmployeeUserKey = EMP.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN
        ON CTR.OwnerUserKey = OWN.empowerUserKey
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS ADV
        ON CTR.CurrentAdviserKey = ADV.empowerUserKey
        AND ADV.IsTestAccount = 0
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[dim_empower_topic] AS T
        ON CTR.TopicKey = T.TopicKey
        AND T.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStage] AS W_STAGE
        ON CTR.WorkflowStageKey = W_STAGE.WorkflowStageKey
        AND W_STAGE.RowIsCurrent = 'Y'
    INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_WorkflowStep] AS W_STEP
        ON CTR.WorkflowStepKey = W_STEP.WorkflowStepKey
        AND W_STEP.RowIsCurrent = 'Y'
)
SELECT DISTINCT
    CASE 
        WHEN NTT.NotificationTypeLookupID = 9 AND CTC.CommunicationSubject = 'Request for Help' THEN 'Help Request'
        WHEN NTT.NotificationTypeLookupID = 9 THEN 'Response Required Case Note'
        WHEN NT.NotificationType = 'TaskRespondToPermanentlyLockedCase' THEN 'Permanently Locked Case'
        WHEN NT.NotificationType = 'TaskRespondToSilentNotification' THEN 'Silent Notification'
        WHEN NT.NotificationType = 'Task (Review Doc Service Document)' THEN 'Doc Review'
        WHEN NT.NotificationType IN ('Task (Complete Parked Call)', 'TaskCompleteParkedCall') THEN 'Parked Call'
        WHEN NT.NotificationType = 'WorkflowLockedCase' THEN 'Workflow Locked Case'
        WHEN NT.NotificationType = 'TaskRespondWorkflowInactiveNotification' THEN 'Inactive Notification'
    END AS [Contact Type],
    CONVERT(DATE, NTT.TaskCreatedDateTime) AS [Contact Date],
    CONVERT(VARCHAR(8), NTT.TaskCreatedDateTime, 108) AS [Contact Time],
    NTTDT.CaseTrackingRecordID AS [Case Number],
    CASES.TopicTitle AS [Topic Title],
    CASES.WorkflowStage AS [Workflow Stage],
    CASES.WorkflowStep AS [Workflow Step],
    NTT.NotificationTrackingTaskID AS [Contact ID],
    CASES.CaseAdviser AS [Adviser],
    CASES.CaseAdviserEmailAddress AS [EMAIL]
    --,'Test McTestface' AS [Client]
FROM CTE_NTT AS NTT WITH (NOLOCK)
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_NotificationType] AS NT WITH (NOLOCK)
    ON NT.NotificationTypeID = NTT.NotificationTypeLookupID
INNER JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_Date] AS DD WITH (NOLOCK)
    ON NTT.TaskCreatedDateKey = DD.DateKey
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_NotificationDistributions] AS NTTDT
    ON NTTDT.NotificationDistributionID = NTT.NotificationDistributionID
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Fact_empower_CaseTrackingCommunication] AS CTC WITH (NOLOCK)
    ON CTC.CaseTrackingRecordID = NTTDT.CaseTrackingRecordID
    AND ISNULL(CTC.IsDraft, 0) = 0
LEFT JOIN [CURRYS_DW_REPORTING].[dbo].[Dim_empower_User] AS OWN WITH (NOLOCK)
    ON OWN.UserID = CTC.SenderUserID
    AND OWN.RowIsCurrent = 'Y'
INNER JOIN CTE_CASES AS CASES
    ON NTTDT.CaseTrackingRecordID = CASES.CaseTrackingRecordID
    AND NTT.TaskCreatedDateTime BETWEEN CASES.RowStartDate AND CASES.RowEndDate
    AND ISNULL(RIGHT(CASES.CaseAdviserEmailAddress, 15), '') LIKE 'adviserplus.com'
    -- This case wasn't assigned to a Network Rail_Empower adviser when note/request was sent
WHERE ISNULL(OWN.EmailAddress, '') NOT LIKE '%adviserplus%'