display all conversations query
hello,
just to let you know that i made a query to list all conversations made by one technician for a given period.
hope it'll be useful to someone.
select
F.FIRST_NAME 'Technician',
F.USER_ID,
C.WORKORDERID 'Ticket',
A.NOTIFICATIONDESCRIPTION,
DATE_FORMAT(FROM_UNIXTIME(notificationdate/1000) ,'%d-%m-%Y') 'notificationdate'
FROM notification A
INNER JOIN notify_workorder B ON a.notificationid=B.notificationid
INNER JOIN WorkOrder C ON B.WORKORDERID=C.WORKORDERID
LEFT JOIN WorkOrderStates D ON C.WORKORDERID=D.WORKORDERID
LEFT JOIN SDUser E ON D.OWNERID=E.USERID
LEFT JOIN AaaUser F ON E.USERID=F.USER_ID
where A.notificationdate >=(UNIX_TIMESTAMP(DATE('2007-07-30 00:00:00')) * 1000)
and A.notificationdate <=(UNIX_TIMESTAMP(DATE('2007-07-31 00:00:00')) * 1000)
AND F.USER_ID = "insert your technician id here or remove the line to see all technicians"
don't forget to adjust timestamps to your needs.
Cheers
Massimo TRENTO
just to let you know that i made a query to list all conversations made by one technician for a given period.
hope it'll be useful to someone.
select
F.FIRST_NAME 'Technician',
F.USER_ID,
C.WORKORDERID 'Ticket',
A.NOTIFICATIONDESCRIPTION,
DATE_FORMAT(FROM_UNIXTIME(notificationdate/1000) ,'%d-%m-%Y') 'notificationdate'
FROM notification A
INNER JOIN notify_workorder B ON a.notificationid=B.notificationid
INNER JOIN WorkOrder C ON B.WORKORDERID=C.WORKORDERID
LEFT JOIN WorkOrderStates D ON C.WORKORDERID=D.WORKORDERID
LEFT JOIN SDUser E ON D.OWNERID=E.USERID
LEFT JOIN AaaUser F ON E.USERID=F.USER_ID
where A.notificationdate >=(UNIX_TIMESTAMP(DATE('2007-07-30 00:00:00')) * 1000)
and A.notificationdate <=(UNIX_TIMESTAMP(DATE('2007-07-31 00:00:00')) * 1000)
AND F.USER_ID = "insert your technician id here or remove the line to see all technicians"
don't forget to adjust timestamps to your needs.
Cheers
Massimo TRENTO