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!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Hour difference based on priority value

Status
Not open for further replies.
Marclem

Marclem

Technical User
Aug 5, 2003
87
US
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!
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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,
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
Drop/Truncate tables based on a stored procedures value 1
Replies
6
Views
316
BobThornton
BobThornton
penguinspeaks
  • Locked
  • Question
Update column based on input from another column 3
Replies
6
Views
270
penguinspeaks
penguinspeaks
EdLentz
  • Locked
  • Question
Looking for suggestions on how to do this
Replies
4
Views
242
Chris Miller
Chris Miller
EdLentz
  • Locked
  • Question
Need to compare two tables and list the differences
Replies
3
Views
240
EdLentz
EdLentz
waubain
  • Locked
  • Question
Reset cumulative sum to zero on every year
Replies
8
Views
356
waubain
waubain

Part and Inventory Search

Sponsor

Back
Top