MySQL Query Help
I have created the following query which shows me the detail of any calls during the last month where the SLA was breached. This query is used to populate a web front end whereby the data is represented in a graphical format and the end user can see this. I am just seeking some help with the query as if the request is still open then the completed time will show as 1970-01-01 which is the UTC time for 0. This looks poor when displayed on the front end so I'd like to change this ideally to blank or something more aesthetically pleasing for the user.
Can anyone help with this?
SELECT
wo.WORKORDERID "Request ID",
case pd.PRIORITYNAME when '1. Critical' then 'Critical'
when '2. High' then 'High'
when '3. Medium' then 'Medium'
when '4. Low' then 'Low'
when 'Change' then 'Change'
End "Priority",
ld.levelname "Level",
from_unixtime(wo.createdtime/1000,'%Y-%m-%d') "Created Date",
from_unixtime(wo.duebytime/1000,'%Y-%m-%d') "DueBy Date",
from_unixtime(wo.completedtime/1000,'%Y-%m-%d') "Completed Date",
s.statusname as 'Current Status',
wo.TITLE "Subject"
FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
left join leveldefinition ld on ld.levelid = wos.levelid
left join statusdefinition s on wos.statusid = s.statusid
WHERE wos.ISOVERDUE = 1
and wo.duebytime BETWEEN (UNIX_TIMESTAMP(date_format(curdate() - interval 1 month,'%Y-%m-01'))*1000)
AND (UNIX_TIMESTAMP(date_format(curdate() - interval 0 month,'%Y-%m-01'))*1000)
AND wo.SITEID IN (6003,6903,604,2104,606,6607,608,610,612,614,616,2402,618,620,2404,622,5103,3902,624,1502,626,628,6608,4206,1801,634,905,636,638,640,642,6609,1202,644,646,648,2702,4506,650,3602,652,3904,673,4803,5104,654,5102,5101,5105,3302,5703,656,6604,2701,630,658,660,664,4806,4203,666,4503,668,670,672,6303,4815)
order by 1
We run SDPlus - MSP v8.1 Build 8105 on a MySQL DB.
Can anyone help with this?
SELECT
wo.WORKORDERID "Request ID",
case pd.PRIORITYNAME when '1. Critical' then 'Critical'
when '2. High' then 'High'
when '3. Medium' then 'Medium'
when '4. Low' then 'Low'
when 'Change' then 'Change'
End "Priority",
ld.levelname "Level",
from_unixtime(wo.createdtime/1000,'%Y-%m-%d') "Created Date",
from_unixtime(wo.duebytime/1000,'%Y-%m-%d') "DueBy Date",
from_unixtime(wo.completedtime/1000,'%Y-%m-%d') "Completed Date",
s.statusname as 'Current Status',
wo.TITLE "Subject"
FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
left join leveldefinition ld on ld.levelid = wos.levelid
left join statusdefinition s on wos.statusid = s.statusid
WHERE wos.ISOVERDUE = 1
and wo.duebytime BETWEEN (UNIX_TIMESTAMP(date_format(curdate() - interval 1 month,'%Y-%m-01'))*1000)
AND (UNIX_TIMESTAMP(date_format(curdate() - interval 0 month,'%Y-%m-01'))*1000)
AND wo.SITEID IN (6003,6903,604,2104,606,6607,608,610,612,614,616,2402,618,620,2404,622,5103,3902,624,1502,626,628,6608,4206,1801,634,905,636,638,640,642,6609,1202,644,646,648,2702,4506,650,3602,652,3904,673,4803,5104,654,5102,5101,5105,3302,5703,656,6604,2701,630,658,660,664,4806,4203,666,4503,668,670,672,6303,4815)
order by 1
We run SDPlus - MSP v8.1 Build 8105 on a MySQL DB.