Forum Discussion

mike_asplin's avatar
mike_asplin
Icon for Helper V rankHelper 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
Icon for Helper V rankHelper V
9 months ago

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

 

Thanks

tayloramy's avatar
tayloramy
Icon for Super User rankSuper User
9 months ago

Hi mike_asplin, 

 

You can't use SQL in the Power Query editor - only M code will work. 

 

Use the Get Data menu to select whatever type of database you are using, and then when prompted for the details you can use teh SQL query there. 

 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

  • mike_asplin's avatar
    mike_asplin
    Icon for Helper V rankHelper V
    9 months ago

    That's not actually true you just wrap the SQL like this

     

    Source = Sql.Database("PRD-SO-DW1.iia-cloud.com", "master", [Query="

    blah blah

    "])

     

    I believe you can tuse CTE but I have no idea what that means. basically need to translate form code that works in Server manager to the equivalent that works on Power Query.  my SqL is pretty rubbish