none
Sql query to connect IDs of service requests and incidents converted to service requests RRS feed

  • Question

  • Dear all,

    I need to write an sql query to connect incidents (converted to service requests) and service requests. This way I will get the Classification of the service request.

    How can I write this script?

    Thanks in advance!

    UPDATED: My version is below but I thinks it's somewhere wrong.

    WITH temp
    AS (SELECT
      [Id],
      SUBSTRING([Id], 3, LEN([Id])) AS myId,
      CASE
        WHEN id LIKE 'IR%' THEN CAST(SUBSTRING([Id], 3, LEN([Id])) AS int) + 1
        ELSE CAST(SUBSTRING([Id], 3, LEN([Id])) AS int)
      END AS mySr,
      [Title],
      [CreatedDate],
      [IsDeleted]
    FROM [DWDataMart].[dbo].[WorkItemDim]
    WHERE id LIKE 'IR%'
    OR id LIKE 'SR%'),
    
    temp1
    AS (SELECT
      mySr
    FROM temp
    GROUP BY mySr
    HAVING COUNT(myId) > 1)
    
    SELECT
      temp.*,
      i.Title AS IR_Title,
      i.Description AS IR_Descr,
      i.Classification AS IR_Classification,
      i.ResolutionCategory AS IR_ResolutionCategory,
      i.ResolutionDescription AS IR_ResolutionDescription,
      sr.Title AS SR_Title,
      sr.Description AS SR_Descr
    FROM temp
    JOIN temp1
      ON temp.mySr = temp1.mySr
    
    LEFT JOIN [ServiceRequestDimvw] sr
      ON temp.Id = sr.Id
    
    LEFT JOIN IncidentDimvw i
      ON temp.Id = i.Id
    
    ORDER BY temp.CreatedDate DESC

    Tuesday, April 2, 2019 9:27 AM