INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Hour difference based on priority value

Hour difference based on priority value

(OP)
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!

RE: Hour difference based on priority value

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

CODE --> 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

RE: Hour difference based on priority value

(OP)
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, 
       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 


Thank you,

RE: Hour difference based on priority value

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:

CODE --> 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! smile

Katie

RE: Hour difference based on priority value

(OP)
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,
       CASE HD_TICKET.DUE_DATE
       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 

RE: Hour difference based on priority value

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

CODE --> 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:

CODE --> sql

...
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, ...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

RE: Hour difference based on priority value

(OP)
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 

RE: Hour difference based on priority value

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

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:

CODE --> 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. smile

CODE --> sql

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

Katie

RE: Hour difference based on priority value

(OP)
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 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close