Home› SQL Server

Change Management Assignee or Category

Metro_ITSMetro_ITS Customer IT Monkey ✭
in SQL Server

I have been working through all of the database tables (both ServiceManager and ServiceManagement) to try to query specific data about upcoming Change Requests (CR's).  However, between the two databases, I am stuck.

When querying the ServiceManager database, I am unable to find the right tables to join the Change Category (Normal, Emergency, etc) to my report. I can get the Change Category when I use the ServiceManagement database, but then I lose the ability to get the Assignee information.  I know that I am missing a small crucial piece of information.

I have attached the two SQL queries for review.

Any help would be GREATLY appreciated!

Best Answer

Answers

  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭
    Hi @Metro_ITS

    My advice would be to run at the dbo.workitem table in the Service Management database as you won't need to do half as many joins as you are currently doing. I have tried writing the SQL for you but I do not have any Review Activities with Cab Review as the Title haha so give this a try and let me know what you think:

    SELECT WorkItemId AS Id,
    Description,
    ds1.DisplayString AS Status,
    AssignedUser,
    DATEADD(Hour,-5,Created) AS CreatedDate,
    DATEADD(Hour,-5,ScheduledStartDate) AS ScheduledStartDate,
    DATEADD(Hour,-5,ScheduledEndDate) AS ScheduledEndDate,
    DATEADD(Hour,-5,ScheduledDowntimeStartDate) AS DowntimeStartDate,
    DATEADD(Hour,-5,ScheduledDowntimeEndDate) AS DowntimeEndDate,
    IsDowntime AS Downtime,
    ReASon,
    TestPlan,
    BackoutPlan AS BackOutPlan
    FROM Dbo.WorkItem AS wi
    JOIN Dbo.DisplayString as ds1
    ON wi.StatusId = ds1.ElementID and LocaleID = 'ENU'
    Where ds1.DisplayString NOT IN ('Closed','Cancelled','Failed','Completed')
    AND WorkItemId like '%CR%'
    AND WorkItemId IN (Select ParentWorkItemId
    From dbo.WorkItem as wi
    JOIN Dbo.DisplayString as ds2
    ON wi.StatusId = ds2.ElementID and LocaleID = 'ENU'
    Where WorkItemId LIKE 'RA%' and ds2.DisplayString = 'In Progress' and Title = 'Cab Review');

    Thanks,
    Shane.
  • Metro_ITSMetro_ITS Customer IT Monkey ✭

    Thank you for the quick reply Shane.  The script you have above works, but it is one that I already have that was included in the attached file.  I am challenged with adding the Change Category (Normal, Emergency, etc) into this same query.

    I can find the Category names in the DisplayString table easy enough. However, what I cannot find are the correct primary/foreign keys to join together to associate the Category with the Change Request.

    Does this help expand the actual issue I am experiencing a bit more sir?  Thanks again for any help you can provide.



  • Shane_WhiteShane_White Cireson Support Super IT Monkey ✭✭✭✭✭
    edited April 2019
    @Metro_ITS This query is different to yours and I have added in the category for you as well:

    SELECT WorkItemId AS Id,
    Description,
    ds1.DisplayString AS 'Status',
    AssignedUser,
    ds2.DisplayString AS 'Category',
    DATEADD(Hour,-5,Created) AS CreatedDate,
    DATEADD(Hour,-5,ScheduledStartDate) AS ScheduledStartDate,
    DATEADD(Hour,-5,ScheduledEndDate) AS ScheduledEndDate,
    DATEADD(Hour,-5,ScheduledDowntimeStartDate) AS DowntimeStartDate,
    DATEADD(Hour,-5,ScheduledDowntimeEndDate) AS DowntimeEndDate,
    IsDowntime AS Downtime,
    ReASon,
    TestPlan,
    BackoutPlan AS BackOutPlan
    FROM Dbo.WorkItem AS wi
    JOIN Dbo.DisplayString as ds1
    ON wi.StatusId = ds1.ElementID
    AND ds1.LocaleID = @languageCode
    JOIN Dbo.DisplayString as ds2
    ON wi.CategoryId = ds2.ElementID
    AND ds2.LocaleID = @LanguageCode
    Where ds1.DisplayString NOT IN ('Closed','Cancelled','Failed','Completed')
    AND WorkItemId like '%CR%'
    AND WorkItemId IN (Select ParentWorkItemId
    From dbo.WorkItem as wi
    JOIN Dbo.DisplayString as ds2
    ON wi.StatusId = ds2.ElementID and LocaleID = @LanguageCode
    Where WorkItemId LIKE 'RA%' and ds2.DisplayString = 'In Progress' and Title = 'Cab Review')

    The @LanguageCode will pick up the Local User Language that they are running on the Portal.

    Let me know if this is what you are after.
    Thanks,
    Shane.
Sign In or Register to comment.