3 Replies Latest reply on Feb 20, 2018 10:51 AM by Abdul Shaikh

    is it possible to see who was first assigned a ticket in SQL?

    Sam Scholes

      Hi,

       

      please see the tittle for my question,

       

      I'm looking to see of its possible to see who gets assigned a ticket first, i'm not too concerned about what happened to the ticket after its first creation.

       

      is that possible to do?

       

      Thanks,

       

      Sam

        • 1. Re: is it possible to see who was first assigned a ticket in SQL?
          Ken Soszka

          Hi Sam, before I look into the possibility of doing this in SQL I want to be sure that you realize that you should be able to see it in the Audit Trail of every Work Order. Do you need this for reporting purposes? Thanks!

          • 2. Re: is it possible to see who was first assigned a ticket in SQL?
            Sam Scholes

            Yeah, i'm aware of the Work Audit section, but i'm wanting to see who was first assigned tickets for the month, it would take a long time to do it manually!

             

            Thank you

            • 3. Re: is it possible to see who was first assigned a ticket in SQL?
              Abdul Shaikh

              Hi Sam,

               

              A query such as the following should Return the Expected set of results that you're looking for:-

               

              select t.WOID as [Work Order No.],dbc.columnname as [Attribute Changed],

              case dbc.insertupdatedeletecode

              WHEN 'U' THEN 'Updated to'

              WHEN 'I' THEN 'Entered as'

              END as [Action Performed],

              dbv.StringValueOld as [Old Value],dbv.StringValueNew as [New Value],db.principalname as [Tech who made the change],cast(dateadd(minute,datediff(minute,getutcdate(),getdate()),

              db.createdtime) as varchar(20)) as [Date/time]

              from dbchangelog db

              join dbchangelogvalue dbv

              on db.dbchangelogid=dbv.dbchangelogid

              join dbchangelogcolumn dbc

              on dbv.dbchangelogcolumnid=dbc.dbchangelogcolumnid

              left join tasks t

              on db.Pkvalue=t.WOID

              where db.dbchangelogid in

              (select dbchangelogid

              from dbchangelogvalue

              where dbchangelogcolumnid in

              (select dbchangelogcolumnid from dbchangelogcolumn where tablename='TASKS'))

              and dbc.InsertUpdateDeleteCode IN ('I','U')

              and t.WOID IS NOT NULL

              and dbv.StringValueNew IS NOT NULL

              AND dbc.ColumnName NOT IN ('PARENTWOID','USERID','OPENBY','WorkOrderTypeID','WO_NUM1')

              and dbc.columnname='Respons'

              AND dbc.insertupdatedeletecode = 'I'

              order by t.woid desc, cast(dateadd(minute,datediff(minute,getutcdate(),getdate()),

              db.createdtime) as varchar(20)) desc;

               

              You can directly run it against the Track-It Database using SQL Server Management Studio and Export the Results out to a CSV file.

               

              Hope it helps.

               

               

              -Abdul Shaikh

              1 of 1 people found this helpful