CODEWITHSUNDEEP
mysqlsql
Matthew Barraud
Matthew Barraud

Reputation: 515

Get items that are closed within 1 business day

I have a MYSQL database table of tickets that I need to count where the ticket has been closed within 1 business day of it being opened.

I've been able to get some of the way with my query below, but I'm at a loss in how to allow that to work by business days. So tickets opened on a Friday can be completed on a Monday and still count as being closed within one business day.

SELECT count(*)
FROM helpdesk 
WHERE hd_closeDateTime < hd_openDateTime + interval 1 day  
AND hd_status = 2

I think I need some sort of DAYOFWEEK(timestamp_column) BETWEEN 2 AND 6? But I'm not sure how this fits with my current WHERE part.

The pertinent parts of the table are below:

CREATE TABLE `helpdesk` (
 `hd_id` int(11) NOT NULL,
 `hd_title` varchar(255) NOT NULL,
 `hd_openDateTime` datetime NOT NULL,
 `hd_closeDateTime` datetime DEFAULT NULL,
 `hd_status` int(11) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Thanks to @Akina this is what is now working. The ELT looks up the number of days to add to the ticket using the + Interval function so it adds 3 days on Friday, 3 days on Saturday, and 2 days on Sunday allowing the tickets to be counted if they are completed within a business day.

SELECT *, DAYOFWEEK(hd_openDateTime)
FROM helpdesk 
WHERE hd_closeDateTime < hd_openDateTime + INTERVAL 
ELT(DAYOFWEEK(hd_openDateTime),2,1,1,1,1,3,3) DAY  
AND hd_status = 2

For anyone interested in more info

ELT - https://www.w3resource.com/mysql/string-functions/mysql-elt-function.php

DAYOFWEEK - https://www.w3schools.com/sql/func_mysql_dayofweek.asp

Upvotes: 0

Views: 41

Answers (1)

Akina
Akina

Reputation: 42642

Test, for example, this:

SELECT COUNT(*)
FROM helpdesk 
WHERE hd_closeDateTime < DATE(hd_openDateTime) + INTERVAL ELT(DAYOFWEEK(hd_openDateTime),3,2,2,2,2,2,4) DAY  
  AND hd_status = 2

Only date (not time) is taken into account. If the time must be taken into account too then

SELECT COUNT(*)
FROM helpdesk 
WHERE hd_closeDateTime < hd_openDateTime + INTERVAL ELT(DAYOFWEEK(hd_openDateTime),2,1,1,1,1,1,3) DAY  
  AND hd_status = 2

Upvotes: 2

Related Questions