Need help on sql report script.
Hi, need help on writing SQL report.
I need to add to the script field of duration of problem and date of last modification.
Somebody help please.
SELECT prob.PROBLEMID as "Code",prob.TITLE as "Title",probdesc.FULL_DESCRIPTION as "Description", ownaaa.FIRST_NAME as "Specialist",case prob.REPORTEDTIME when 0 then 'Not assigned' else DATE_FORMAT(FROM_UNIXTIME(prob.REPORTEDTIME /1000) ,'%d-%m-%Y %k:%i') end as "Reported date", case prob.DUEBYTIME when 0 then 'Not assigned' else DATE_FORMAT(FROM_UNIXTIME(prob.DUEBYTIME/1000) ,'%d-%m-%Y %k:%i') end as "Term of implementation", case prob.CLOSEDTIME when 0 then 'Not assigned' else DATE_FORMAT(FROM_UNIXTIME(prob.CLOSEDTIME/1000) ,'%d-%m-%Y %k:%i') end as "Close date",statdef.STATUSNAME as "Status", count(*) as 'Incident''s Count'
FROM Problem prob
INNER JOIN ProblemToIncidentMapping on ProblemToIncidentMapping.PROBLEMID = Prob.PROBLEMID
LEFT JOIN SDUser ownsd ON prob.OWNERID=ownsd.USERID LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID LEFT JOIN StatusDefinition statdef ON prob.STATUSID=statdef.STATUSID LEFT JOIN ProblemToDescription probdesc ON prob.PROBLEMID=probdesc.PROBLEMID
group by prob.PROBLEMID,prob.TITLE,probdesc.FULL_DESCRIPTION, ownaaa.FIRST_NAME,prob.REPORTEDTIME, prob.DUEBYTIME,statdef.STATUSNAME
order by 'Incident''s Count' desc
I need to add to the script field of duration of problem and date of last modification.
Somebody help please.
SELECT prob.PROBLEMID as "Code",prob.TITLE as "Title",probdesc.FULL_DESCRIPTION as "Description", ownaaa.FIRST_NAME as "Specialist",case prob.REPORTEDTIME when 0 then 'Not assigned' else DATE_FORMAT(FROM_UNIXTIME(prob.REPORTEDTIME /1000) ,'%d-%m-%Y %k:%i') end as "Reported date", case prob.DUEBYTIME when 0 then 'Not assigned' else DATE_FORMAT(FROM_UNIXTIME(prob.DUEBYTIME/1000) ,'%d-%m-%Y %k:%i') end as "Term of implementation", case prob.CLOSEDTIME when 0 then 'Not assigned' else DATE_FORMAT(FROM_UNIXTIME(prob.CLOSEDTIME/1000) ,'%d-%m-%Y %k:%i') end as "Close date",statdef.STATUSNAME as "Status", count(*) as 'Incident''s Count'
FROM Problem prob
INNER JOIN ProblemToIncidentMapping on ProblemToIncidentMapping.PROBLEMID = Prob.PROBLEMID
LEFT JOIN SDUser ownsd ON prob.OWNERID=ownsd.USERID LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID LEFT JOIN StatusDefinition statdef ON prob.STATUSID=statdef.STATUSID LEFT JOIN ProblemToDescription probdesc ON prob.PROBLEMID=probdesc.PROBLEMID
group by prob.PROBLEMID,prob.TITLE,probdesc.FULL_DESCRIPTION, ownaaa.FIRST_NAME,prob.REPORTEDTIME, prob.DUEBYTIME,statdef.STATUSNAME
order by 'Incident''s Count' desc