Post reply

Need Help with T-SQL Query (Grouping Columns)

  • October 27, 2017 at 4:20 am

    #357989

    Hi All,
    I need a bit of help with my query below.

    select distinct
      vc.Name as Hostname,
      it.Name as Task,
      eti.Result as Return_code,
      eti.Success as Task_Status,
      MAX(eti.EndTime) as Max_End_Time

    from Evt_Task_Instances eti

    join ItemVersions iv
      on eti.TaskVersionGuid = iv.VersionGuid
    join vItem it
      on it.Guid = iv.ItemGuid
    join vComputer vc
      on vc.Guid = eti._ResourceGuid
    join dbo.CollectionMembership cm
      on vc.Guid = cm.ResourceGuid
    where
    (vc.name = 'TDS1477' or vc.name = 'SGCBUGUAVAPP001' or vc.name = 'TDS1476')
    and
    (it.Name like 'List Updates Status')
    GROUP BY
        eti.TaskVersionGuid
        ,iv.VersionGuid
        ,vc.Name
        ,it.Name
      ,eti.Result
        ,eti.Success
        ,eti.EndTime
        

    Below is the output of the query above.

    Basically, I just want to show only the hostname with the MAX end time. In other words, I dont want to show the columns with repeated hostnames. Just the hostname with the max end time.

    I appreciate the help.

    Thank you.

  • October 27, 2017 at 4:48 am

    #1965473

    For the query you shared you need to do the following:
    1. You need to remove the "EndTime" column from your Group by Clause.
    2. Remove the Distinct Key word which i assume you write to remove duplicate from your resultset.
    Something like this:

    select
        --distinct
     vc.Name as Hostname,
     it.Name as Task,
     eti.Result as Return_code,
     eti.Success as Task_Status,
     MAX(eti.EndTime) as Max_End_Time

    from Evt_Task_Instances eti

    join ItemVersions iv
     on eti.TaskVersionGuid = iv.VersionGuid
    join vItem it
     on it.Guid = iv.ItemGuid
    join vComputer vc
     on vc.Guid = eti._ResourceGuid
    join dbo.CollectionMembership cm
     on vc.Guid = cm.ResourceGuid
    where
    (vc.name = 'TDS1477' or vc.name = 'SGCBUGUAVAPP001' or vc.name = 'TDS1476')
    and
    (it.Name like 'List Updates Status')
    GROUP BY
      eti.TaskVersionGuid
      ,iv.VersionGuid
      ,vc.Name
      ,it.Name
    ,eti.Result
      ,eti.Success
      --,eti.EndTime
     

    Regarding the following:

    Basically, I just want to show only the hostname with the MAX end time. In other words, I dont want to show the columns with repeated hostnames. Just the hostname with the max end time.


    Its more easy then the query you have shared earlier


    select     
     vc.Name as Hostname,MAX(eti.EndTime) as Max_End_Time
    from Evt_Task_Instances eti
    join ItemVersions iv
     on eti.TaskVersionGuid = iv.VersionGuid
    join vItem it
     on it.Guid = iv.ItemGuid
    join vComputer vc
     on vc.Guid = eti._ResourceGuid
    join dbo.CollectionMembership cm
     on vc.Guid = cm.ResourceGuid
    where
    (vc.name = 'TDS1477' or vc.name = 'SGCBUGUAVAPP001' or vc.name = 'TDS1476')
    and
    (it.Name like 'List Updates Status')
    GROUP BY
        vc.Name

    hope it helps

  • October 27, 2017 at 8:42 am

    #1965520

    Hi twin.devil,

    Awesome! Thank you very much!

    Best Regards,

  • October 30, 2017 at 12:38 am

    #1965722

    Glad to help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply