Selecting date range between or greater

Question

Data

CREATE TABLE `employees_salaries` (
  `PK_salary` bigint(20) NOT NULL,
  `PK_worker` bigint(20) NOT NULL,
  `contract_start` date NOT NULL,
  `contract_end` date NOT NULL,
  `gross` decimal(10,2) NOT NULL,
  `insurence_cost1` decimal(10,2) NOT NULL,
  `insurence_cost2` decimal(10,2) NOT NULL,
  `to_payout` decimal(10,2) NOT NULL,
  `due` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `employees_salaries` (`PK_salary`, `PK_worker`, `contract_start`, `contract_end`, `gross`, `insurence_cost1`, `insurence_cost2`, `to_payout`, `due`) VALUES
(1, 4, '2017-10-01', '2017-10-31', '1000.00', '137.10', '171.60', '768.24', '1171.60'),
(2, 4, '2017-10-01', '2017-10-31', '2000.00', '274.20', '392.20', '1459.48', '2392.20'),
(3, 15, '2017-08-01', '2017-09-08', '2000.00', '274.20', '392.20', '1418.88', '2392.20'),
(4, 17, '2017-08-01', '2017-09-08', '2000.00', '274.20', '392.20', '1459.48', '2392.20'),
(5, 9, '2017-08-01', '2017-09-08', '2826.12', '303.39', '433.95', '2058.08', '3260.07'),
(6, 11, '2017-08-01', '2017-09-08', '2000.00', '274.20', '392.20', '1459.48', '2392.20'),
(7, 5, '2017-08-01', '2017-09-08', '2000.00', '274.20', '392.20', '1418.88', '2392.20'),
(8, 6, '2017-08-01', '2017-09-08', '2000.00', '274.20', '392.20', '1459.48', '2392.20');

Query

set @dstart = "2017-10-01",@dend = "2017-10-30";

select employees_salaries.PK_worker , coalesce(sum(employees_salaries.gross),0) as p_am, coalesce(sum(insurence_cost1 + insurence_cost2),0) as incost
        from   employees_salaries 
        /* This example won't return reliable data
        WHERE    employees_salaries.contract_start      >= @dstart
        and    employees_salaries.contract_end <= @dend
        */
     GROUP BY employees_salaries.PK_worker

I am trying to accomplish following result :

Scenario

We set the time range for the query, show the salaries for @dstart = "2017-10-01",@dend = "2017-10-30";

Logically it should be contract_start should be between our @dstart @dend,

contract_end should be also between @dstart @dend, but what if contract_end is for example 2017-12-30? It will produce such logic

is 2017-12-30 between 2017-10-01 and 2017-10-30? Answer is no, because its greater.

It possibly applies to contract_start as well : is 2017-09-01 is between 2017-10-01 and 2017-10-30? No, but the contract_end is in range of thous.

The question is how to implement such scenario? any hints appreciated

MCVE : https://www.db-fiddle.com/f/wAQ2eGfp2kCAdQ7QUBRYKp/3

I'm not sure I understand the problem. You mean both *mmonth* and *payout_date* should be in the date range? — Ángela, Dec 02 '17 at 12:05
I assume for the logic at least one should, but walk through the scenario it's not as simple as it looks, also cleaned up the logic a bit — Kavvson Empcraft, Dec 02 '17 at 12:06
I gave you the select employees_salaries.PK_worker , coalesce(sum(employees_salaries.gross),0) as p_am, coalesce(sum(insurence_cost1 + insurence_cost2),0) as incost :) just look on the query example — Kavvson Empcraft, Dec 02 '17 at 12:18
The question is how to implement the where clause described in the scenario — Kavvson Empcraft, Dec 02 '17 at 12:20
The problem is we do not understand the scenario. Give us examples to help us understand — Ángela, Dec 02 '17 at 12:21
https://stackoverflow.com/questions/2545947/check-overlap-of-date-ranges-in-mysql — Paul Spiegel, Dec 02 '17 at 12:21
Then it should be something like `WHERE employees_salaries.contract_start <= @dend and employees_salaries.contract_end >= @dstart` — Paul Spiegel, Dec 02 '17 at 12:27

score 0 · Accepted Answer · answered Dec 02 '17 at 12:25

set @dstart = "2017-10-01",@dend = "2017-10-30";

    select employees_salaries.PK_worker , coalesce(sum(employees_salaries.gross),0) as p_am, coalesce(sum(insurence_cost1 + insurence_cost2),0) as incost
            from   employees_salaries 

            WHERE
            @dstart BETWEEN employees_salaries.contract_start AND employees_salaries.contract_end 
   OR  @dend BETWEEN employees_salaries.contract_start AND employees_salaries.contract_end 
   OR @dstart >= employees_salaries.contract_start AND @dend <= employees_salaries.contract_end 


         GROUP BY employees_salaries.PK_worker

Selecting date range between or greater

1 Answers1