Problem and Change worklog details

Change Work Log Entry:

Change ID Subject Change Description Created Time Time Spent in Hrs Cost Entry Description Technician Cost Entry date Category Requester Resolution

MY-SQL:

SELECT cd.changeid 'Change ID',cd.title 'Subject',cd.description 'Change Description',DATE_FORMAT(FROM_UNIXTIME(cd.createdtime/1000),'%d-%m-%Y %k:%i')'Created Time',ct.timespent/3600000 'Time Spent in Hrs',ct.description 'Cost Entry Description',au.first_name 'Technician',DATE_FORMAT(FROM_UNIXTIME(ct.RECORDEDTIME/1000),'%d-%m-%Y %k:%i') 'Cost Entry date',cat.categoryname 'Category', aaauser.first_name 'Requester',cr.resolution'Resolution' FROM chargestable ct inner join changetocharge ctc ON ct.chargeid=ctc.chargeid LEFT JOIN changedetails cd ON ctc.changeid=cd.changeid LEFT JOIN aaauser au on ct.technicianid=au.user_id LEFT JOIN CategoryDefinition cat on cat.categoryid=cd.categoryid left join sduser sd on cd.initiatorid=sd.userid left join aaauser on aaauser.user_id=sd.userid left join changeresolution cr on cr.changeid=cd.changeid order by 1

MS-SQL

SELECT cd.changeid 'Change ID',cd.title 'Subject',cd.description 'Change Description',dateadd(s,datediff(s,getutcdate(),getdate())+cd.createdtime/1000,'1970-01-01 00:00:00')'Created Time',ct.timespent/3600000 'Time Spent in Hrs',ct.description 'Cost Entry Description',au.first_name 'Technician',dateadd(s,datediff(s,getutcdate(),getdate())+ct.RECORDEDTIME/1000,'1970-01-01 00:00:00') 'Cost Entry date',cat.categoryname 'Category', aaauser.first_name 'Requester',cr.resolution'Resolution' FROM chargestable ct inner join changetocharge ctc ON ct.chargeid=ctc.chargeid LEFT JOIN changedetails cd ON ctc.changeid=cd.changeid LEFT JOIN aaauser au on ct.technicianid=au.user_id LEFT JOIN CategoryDefinition cat on cat.categoryid=cd.categoryid left join sduser sd on cd.initiatorid=sd.userid left join aaauser on aaauser.user_id=sd.userid left join changeresolution cr on cr.changeid=cd.changeid order by 1

Problem WorkLog Entry:

Problem ID Subject Problem Description Created Time Time Spent in Hrs Cost Entry Description Technician Cost Entry date Category Reported By Resolution

MY-SQL:

SELECT pr.problemid 'Problem ID',pr.title 'Subject' ,pr.description 'Problem Description',DATE_FORMAT(FROM_UNIXTIME(pr.reportedtime/1000),'%d-%m-%Y %k:%i')'Created Time',ct.timespent/3600000 'Time Spent in Hrs',ct.description 'Cost Entry Description' ,au.first_name 'Technician',DATE_FORMAT(FROM_UNIXTIME(ct.RECORDEDTIME/1000),'%d-%m-%Y %k:%i') 'Cost Entry date',cat.Categoryname 'Category',aaauser.first_name 'Reported By',prs.resolution 'Resolution' FROM chargestable ct inner join problemtocharge ptc ON ct.chargeid=ptc.chargeid LEFT JOIN problem pr ON ptc.problemid=pr.problemid LEFT JOIN aaauser au on ct.technicianid=au.user_id left join problemresolution prs on prs.problemid=pr.problemid LEFT JOIN CategoryDefinition cat on cat.categoryid=pr.categoryid left join sduser sd on pr.originatorid=sd.userid left join aaauser on aaauser.user_id=sd.userid order by 1

MS-SQL:

SELECT pr.problemid 'Problem ID',pr.title 'Subject' ,pr.description 'Problem Description', dateadd(s,datediff(s,getutcdate(),getdate())+pr.reportedtime/1000,'1970-01-01 00:00:00')'Created Time',ct.timespent/3600000 'Time Spent in Hrs',ct.description 'Cost Entry Description' ,au.first_name 'Technician', dateadd(s,datediff(s,getutcdate(),getdate())+ct.RECORDEDTIME/1000,'1970-01-01 00:00:00') 'Cost Entry date',cat.Categoryname 'Category',aaauser.first_name 'Reported By',prs.resolution 'Resolution' FROM chargestable ct inner join problemtocharge ptc ON ct.chargeid=ptc.chargeid LEFT JOIN problem pr ON ptc.problemid=pr.problemid LEFT JOIN aaauser au on ct.technicianid=au.user_id left join problemresolution prs on prs.problemid=pr.problemid LEFT JOIN CategoryDefinition cat on cat.categoryid=pr.categoryid left join sduser sd on pr.originatorid=sd.userid left join aaauser on aaauser.user_id=sd.userid order by 1

regards,

Rachana