Hour difference based on priority value

Marclem · Feb 14, 2017

Hi Everyone!

I have a priority column which can give only 3 different results (High or Normal or Low).

I have another column named Hour Diff which gives me hours difference between date time difference from two other columns.

Basically I would like to do the following:

Based on Priority:
High 6hrs = 06:00:00
Normal 36hrs = 36:00:00
Low 220hrs = 220:00:00

i.e. If Priority field = high then if Hour Diff is > 06:00:00 then SLA Met column = No otherwise Yes
i.e. If Priority field = Normal then if Hour Diff is > 36:00:00 then SLA Met column = No otherwise Yes
i.e. If Priority field = low then if Hour Diff is > 220:00:00 then SLA Met column = No otherwise Yes

Below my current mysql code:

select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE ,
DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m/%d/%Y %H:%i') as TIME_CLOSED,
TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H') AS HOUR_DIFF,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

Thank you for you support!

Katerine · Feb 15, 2017

Haven't tested this, but I think this may be something like what you need?

SQL:

hdt.ID, 
hdt.TITLE ,
hdt.DUE_DATE ,
DATE_FORMAT(hdt.TIME_CLOSED, '%m/%d/%Y %H:%i') as TIME_CLOSED,
TIME_FORMAT(TIMEDIFF(hdt.DUE_DATE, hdt.TIME_CLOSED),'%H') AS HOUR_DIFF,
hdp.NAME as PRIORITY, 
CASE hdp.NAME 
  WHEN 'High' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(hdt.DUE_DATE, hdt.TIME_CLOSED),'%H') > 6 THEN 'No' ELSE 'Yes' END)
  WHEN 'Normal' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(hdt.DUE_DATE, hdt.TIME_CLOSED),'%H') > 36 THEN 'No' ELSE 'Yes' END)
  WHEN 'Low' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(hdt.DUE_DATE, hdt.TIME_CLOSED),'%H') > 220 THEN 'No' ELSE 'Yes' END)
  ELSE NULL END AS SLA_MET, 
hdc.NAME as CATEGORY, 
hds.NAME as STATUS, 
hdi.NAME as IMPACT,
m.NAME as MACHINE_NAME,
ifnull((select FULL_NAME from USER where hdt.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
(select FULL_NAME from USER where hdt.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET hdt
left join HD_CATEGORY hdc on hdt.HD_CATEGORY_ID = hdc.ID
left join HD_STATUS hds on hdt.HD_STATUS_ID = hds.ID
left join HD_PRIORITY hdp on hdt.HD_PRIORITY_ID = hdp.ID
left join HD_IMPACT hdi on hdt.HD_IMPACT_ID = hdi.ID
left join MACHINE m on hdt.MACHINE_ID = m.ID
where hds.STATE = 'closed' and hdt.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, hdp.ORDINAL, hdc.ORDINAL, hds.ORDINAL, hdi.ORDINAL

(5:35 edit: fixed incorrect CASE... WHEN syntax)

Katie

Marclem · Feb 16, 2017

Hi Katie,

Thank you for your support.

After I added code as you suggested I got the following error:

mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' WHEN 'High' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, H' at line 7] in EXECUTE( "select HD_TICKET.ID, HD_TICKET.TITLE , HD_TICKET.DUE_DATE , DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m/%d/%Y %H:%I:%s') as TIME_CLOSED, TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H:%i') AS HOUR_DIFF, HD_PRIORITY.NAME as PRIORITY, CASE HD_PRIORITY.NAME, WHEN 'High' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H') > 6 THEN 'No' ELSE 'Yes' END), WHEN 'Normal' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H'),'%H') > 36 THEN 'No' ELSE 'Yes' END), WHEN 'Low' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H') > 220 THEN 'No' ELSE 'Yes' END), ELSE NULL END AS SLA_MET, HD_CATEGORY.NAME as CATEGORY, HD_STATUS.NAME as STATUS, HD_IMPACT.NAME as IMPACT, MACHINE.NAME as MACHINE_NAME, ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME, (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME from HD_TICKET left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY) order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL LIMIT 0")

Below the latest code:

Code:

select HD_TICKET.ID, 
       HD_TICKET.TITLE ,
       HD_TICKET.DUE_DATE ,
       DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m/%d/%Y %H:%I:%s') as TIME_CLOSED,
       TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H:%i') AS HOUR_DIFF,
       HD_PRIORITY.NAME as PRIORITY, 
       [b]CASE HD_PRIORITY.NAME, 
       WHEN 'High' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H') > 6 THEN 'No' ELSE 'Yes' END),
       WHEN 'Normal' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H'),'%H') > 36 THEN 'No' ELSE 'Yes' END),
       WHEN 'Low' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H') > 220 THEN 'No' ELSE 'Yes' END),
       ELSE NULL END AS SLA_MET,[/b] 
       HD_CATEGORY.NAME as CATEGORY, 
       HD_STATUS.NAME as STATUS, 
       HD_IMPACT.NAME as IMPACT,
       MACHINE.NAME as MACHINE_NAME,
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

Thank you,

Katerine · Feb 16, 2017

Hi Marclem,
My code should be used as a template. I made a lot of assumptions about how your data is stored, which may be correct.

But the big thing is, there shouldn't be a comma after "CASE HD_PRIORITY.NAME", or after the lines following:

SQL:

       CASE HD_PRIORITY.NAME 
       WHEN 'High' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H') > 6 THEN 'No' ELSE 'Yes' END)
       WHEN 'Normal' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H'),'%H') > 36 THEN 'No' ELSE 'Yes' END)
       WHEN 'Low' THEN (CASE WHEN TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H') > 220 THEN 'No' ELSE 'Yes' END)
       ELSE NULL END AS SLA_MET,

...is all one column, which I split into separate lines of code for the sake of readability. But you indicate to SQL that it's all one column, by NOT separating them with commas.

HTH!

Katie

Marclem · Feb 16, 2017

Hi Katie,

Thank you, I got it working!

I am trying as well to do the following:

If Time Closed date is > the Due Date then SLA Met = No otherwise = Yes

I used your Case statement to try to gather the result but it ends up with all Yes on the SLA Met field. Below my code:

Code:

select HD_TICKET.ID, 
       HD_TICKET.TITLE ,
       HD_TICKET.DUE_DATE ,
       DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m/%d/%Y %H:%I:%s') as TIME_CLOSED,
       [b]CASE HD_TICKET.DUE_DATE
       WHEN TIME_CLOSED > HD_TICKET.DUE_DATE THEN 'No' ELSE 'Yes' END as SLA_Met,[/b]
       TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H:%i') AS HOUR_DIFF,
       HD_PRIORITY.NAME as PRIORITY, 
       HD_CATEGORY.NAME as CATEGORY, 
       HD_STATUS.NAME as STATUS, 
       HD_IMPACT.NAME as IMPACT,
       MACHINE.NAME as MACHINE_NAME,
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

Katerine · Feb 16, 2017

You want the other variant of CASE...WHEN, in that case. Take out the HD_TICKET.DUE_DATE between CASE and WHEN:

SQL:

select HD_TICKET.ID, 
       HD_TICKET.TITLE ,
       HD_TICKET.DUE_DATE ,
       DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m/%d/%Y %H:%I:%s') as TIME_CLOSED,
       CASE WHEN TIME_CLOSED > HD_TICKET.DUE_DATE THEN 'No' ELSE 'Yes' END as SLA_Met,
       TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H:%i') AS HOUR_DIFF,
       HD_PRIORITY.NAME as PRIORITY, 
       HD_CATEGORY.NAME as CATEGORY, 
       HD_STATUS.NAME as STATUS, 
       HD_IMPACT.NAME as IMPACT,
       MACHINE.NAME as MACHINE_NAME,
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

You may also have to replace TIME_CLOSED with the original code that created TIME_CLOSED:

SQL:

...
[indent]DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m/%d/%Y %H:%I:%s') as TIME_CLOSED,[/indent]
       CASE WHEN HD_TICKET.TIME_CLOSED > HD_TICKET.DUE_DATE THEN 'No' ELSE 'Yes' END as SLA_Met,
       ...etc

(Actually, if DUE_DATE is not a string, then you definitely have to replace TIME_CLOSED with HD_TICKET.TIME_CLOSED.)

If the problem persists, make sure that HD_TICKET.TIME_CLOSED and HD_TICKET.DUE_DATE have the same data type.

Katie

Marclem · Feb 17, 2017

Hi Katie,

After your support and suggestions, all is working! Below my final code:

Thank you,

Code:

select HD_TICKET.ID, 
       HD_TICKET.TITLE ,
       DATE_FORMAT(HD_TICKET.DUE_DATE, '%m/%d/%Y %H:%I:%s') as DUE_DATE,
       DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m/%d/%Y %H:%I:%s') as TIME_CLOSED,
       CASE WHEN DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m/%d/%Y %H:%I:%s') > DATE_FORMAT(HD_TICKET.DUE_DATE, '%m/%d/%Y %H:%I:%s') THEN 'No' ELSE 'Yes' END as 'SLA_Met?',
      #TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H:%i') AS HOUR_DIFF,#
       HD_PRIORITY.NAME as PRIORITY, 
       HD_CATEGORY.NAME as CATEGORY, 
       HD_STATUS.NAME as STATUS, 
       HD_IMPACT.NAME as IMPACT,
       MACHINE.NAME as MACHINE_NAME,
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

Katerine · Feb 17, 2017

Good... glad to hear there's progress!

However, looking at your query, one thing leaps out at me, that means it's not going to continue to work, at least not as it is right now.

It's in this line:

SQL:

       CASE WHEN DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m/%d/%Y %H:%I:%s') > DATE_FORMAT(HD_TICKET.DUE_DATE, '%m/%d/%Y %H:%I:%s') THEN 'No' ELSE 'Yes' END as 'SLA_Met?',

I'm 99.99% certain that the purpose of the DATE_FORMAT function is to convert a date (which is really a floating-point number) into a user-friendly string. It's for displaying dates. It's not for comparing whether one date is greater than another.

The way you have it set up, '1/1/2017' is less than '9/1/2016', and '10/1/2017' is less than '9/30/2017', because it's comparing them... alphabetically.

Don't use DATE_FORMAT to compare the display strings for dates. Just compare the dates.

SQL:

       CASE WHEN HD_TICKET.TIME_CLOSED > HD_TICKET.DUE_DATE THEN 'No' ELSE 'Yes' END as 'SLA_Met?',

Katie

Marclem · Feb 22, 2017

Hi Katie,

Thank you for your clarification on DATE_FORMAT function, I went ahead and made the change.

Code:

select HD_TICKET.ID, 
       HD_TICKET.TITLE ,
       DATE_FORMAT(HD_TICKET.DUE_DATE, '%m/%d/%Y %H:%I:%s') as DUE_DATE,
       DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m/%d/%Y %H:%I:%s') as TIME_CLOSED,
       CASE WHEN HD_TICKET.TIME_CLOSED > HD_TICKET.DUE_DATE THEN 'No' ELSE 'Yes' END as 'SLA_Met?', 
     #TIME_FORMAT(TIMEDIFF(HD_TICKET.DUE_DATE, HD_TICKET.TIME_CLOSED),'%H:%i') AS HOUR_DIFF,#
       HD_PRIORITY.NAME as PRIORITY, 
       HD_CATEGORY.NAME as CATEGORY, 
       HD_STATUS.NAME as STATUS, 
       HD_IMPACT.NAME as IMPACT,
       MACHINE.NAME as MACHINE_NAME,
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Hour difference based on priority value

Marclem

Technical User

Katerine

Programmer

Marclem

Technical User

Katerine

Programmer

Marclem

Technical User

Katerine

Programmer

Marclem

Technical User

Katerine

Programmer

Marclem

Technical User

Similar threads

Part and Inventory Search

Sponsor