CODEWITHSUNDEEP
mysql
Manikanta Reddy Mekala
Manikanta Reddy Mekala

Reputation: 47

MYSQL- Excluding sundays and holidays between the two days

I am trying to exclude Sundays and holidays between the two days the result should be in hours i am using the below query for working hours and i want query Excluding Sundays and holidays it should match the expected result.

CREATE TABLE `customerevents` (
  `Id` INT NOT NULL,
  `Createddate` DATETIME NULL DEFAULT NULL,
  `Modifiedate` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`Id`));
  INSERT INTO `customereventss` (`Id`, `Createddate`, `Modifiedate`) VALUES ('1', '2020-01-10 23:00:00', '2020-01-13 15:00:00');
INSERT INTO `customereventss` (`Id`, `Createddate`, `Modifiedate`) VALUES ('2', '2020-01-10 23:00:00', '2020-01-13 03:00:00');

CREATE TABLE holidays` (Id INT NOT NULL,Holiday` DATETIME NULL DEFAULT NULL, PRIMARY KEY (Id)); INSERT INTO holidays (Id, Holiday) VALUES ('1', '2020-01-11 00:00:00');

Here is the code using for working hours:

SELECT t.Createddate,t.Modifiedate, 
       SUM(TIMESTAMPDIFF(DAY, start_date, end_date) * 8 +
           TIMESTAMPDIFF(MINUTE, TIME(start_time), TIME(end_time)) / 60 
          ) AS task_time FROM customerevents t
JOIN (SELECT Id,date(Createddate) AS start_date,date(Modifiedate) AS end_date,week(Createddate) AS start_week,week(Modifiedate) AS end_week,
       GREATEST('09:00', LEAST('17:00', TIME(Createddate))) AS start_time,
       GREATEST('09:00', LEAST('17:00', TIME(Modifiedate))) AS end_time
      FROM customerevents) tt on tt.Id=t.Id
GROUP BY t.id;

Result:

    Createddate          Modifiedate        task_time
2020-01-10 23:00:00   2020-01-13 15:00:00    22.00
2020-01-10 23:00:00   2020-01-13 03:00:00    16.00

Expected result excluding Sundays:

        Createddate          Modifiedate         task_time    
    2020-01-10 23:00:00   2020-01-13 15:00:00    14.00
    2020-01-10 23:00:00   2020-01-13 03:00:00     8.00

Upvotes: 1

Views: 120

Answers (1)

Strawberry
Strawberry

Reputation: 33935

Consider the following (still ignoring holidays)... 

DROP TABLE IF EXISTS customer_events;
CREATE TABLE customer_events 
(id SERIAL PRIMARY KEY
,date_created DATETIME NULL DEFAULT NULL
,date_modified DATETIME NULL DEFAULT NULL
);

INSERT INTO customer_events VALUES 
(1, '2020-01-10 23:00:00', '2020-01-13 15:00:00'),
(2, '2020-01-10 23:00:00', '2020-01-13 03:00:00'),
(3, '2020-01-11 23:00:00', '2020-01-13 03:00:00');

DROP TABLE IF EXISTS holidays;
CREATE TABLE holidays
(id SERIAL PRIMARY KEY
,holiday DATETIME NULL DEFAULT NULL
); 

INSERT INTO holidays VALUES 
(1, '2020-01-11 00:00:00');

SELECT a.*
     , a.task_time - b.delta revised_total
  FROM
     ( SELECT t.id
            , t.date_created
            , t.date_modified
            , SUM(TIMESTAMPDIFF(DAY, start_date, end_date) * 8 +
                  TIMESTAMPDIFF(MINUTE, TIME(start_time), TIME(end_time)) / 60 
                 ) task_time 
         FROM customer_events t
         JOIN 
            ( SELECT id
                   , DATE(date_created) start_date
                   , DATE(date_modified) end_date
                   , GREATEST('09:00:00', LEAST('17:00:00', TIME(date_created))) start_time
                   , GREATEST('09:00:00', LEAST('17:00:00', TIME(date_modified))) end_time
                FROM customer_events
            ) tt 
           ON tt.id = t.id
        GROUP 
           BY t.id
     ) a
  JOIN
     ( SELECT x.id
            , 5 * (DATEDIFF(LEAST(x.date_modified,LAST_DAY(x.date_created)),x.date_created) DIV 7) 
                + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(x.date_created) + WEEKDAY(x.date_modified) + 1, 1) * 8 delta
          FROM customer_events x
     ) b
    ON b.id = a.id;
+----+---------------------+---------------------+-----------+---------------+
| id | date_created        | date_modified       | task_time | revised_total |
+----+---------------------+---------------------+-----------+---------------+
|  1 | 2020-01-10 23:00:00 | 2020-01-13 15:00:00 |   22.0000 |            14 |
|  2 | 2020-01-10 23:00:00 | 2020-01-13 03:00:00 |   16.0000 |             8 |
|  3 | 2020-01-11 23:00:00 | 2020-01-13 03:00:00 |    8.0000 |             0 |
+----+---------------------+---------------------+-----------+---------------+

Incidentally, I think it a little odd that date_modified cannot be NULL

Upvotes: 1

Related Questions