Forum Discussion

mike_asplin's avatar
mike_asplin
Helper V
9 months ago
Solved

SQL Query Syntax issue

Hi. In the past I have been handed sql queries to use in Power Query.   The structure of these is to specify the DB at the top of the query and then only reference the table name through out   USE ...
Need Help
  • Zanqueta's avatar
    Zanqueta
    9 months ago

    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%'

     

mike_asplin's avatar
mike_asplin
Helper V
9 months ago

sorry it isnt loading. it doesnt show an error but never loads. Is my code below correct?

 

Thanks

Zanqueta's avatar
Zanqueta
Super User
9 months ago

Hi mike_asplin,

Please provide us with a screenshot of your error.

Perhaps your query is too heavy and is being rejected by the server. Try restricting the data a little using the where command to filter a single day or a single customer, for example.

  • mike_asplin's avatar
    mike_asplin
    Helper V
    9 months ago

    Sorry there is no error just never loads. I was handed this query by a company a do work for that they use on Server manager because they want the same data in their power BI reports. I have very limited SQL knowledge and usually can just take their query and drop it into Power Query by removing the USE and Go and fully qulaifying the table references. I dont know what CTE means so is that also something PQ cant understand?

    • BA_Pete's avatar
      BA_Pete
      Super User
      9 months ago

       

      CTE is 'Common Table Expression' which is basically just a way of building a temporary table in your SQL query that can be used later in the query (think of it like a table variable in DAX) and is perfectly fine to use in a PQ query.

       

      The real question here is why you're so fixed on using the SQL query at all. Assuming your SQL is basically as simple as you've exampled so far, and that you're querying an SQL Server DB, you can fold all this to the server using M code alone.

       

      Pete

      • mike_asplin's avatar
        mike_asplin
        Helper V
        9 months ago

        sorry the SQL is way longer than I sampled, but the rest of it looks conventional .  I have amended what i was given by removing USE and Go, fully defining the tables and putting a ; in front of WITH as suggested by zanqueta001

         

        That fixed the errors, but it doesnt retrun anything after 30 minutes 

         

        Revised SQL