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!
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!
1 of 1 people found this helpful
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],
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
join dbchangelogcolumn dbc
left join tasks t
where db.dbchangelogid in
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.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.