title: MYSQL- Excluding sundays and holidays between the two days
body: <p>I am trying to exclude <strong>Sundays and holidays</strong> 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.</p>
  
  <pre><code>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');
  </code></pre>
  
  <p>Here is the code using for working hours:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>Expected result excluding Sundays:</p>
  
  <pre><code>        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
  </code></pre>