CODEWITHSUNDEEP
mysqlsqldatetimetimestamp
Mark Watney
Mark Watney

Reputation: 337

MySQL query to know schedule compliance

I have a table Works where I got the columns start_date and end_date which are datetime types and qtty tinyint. For covering those works I got another table named Turns which has start_turn and end_turn both datetime types too.

The works needs to be covered for a number of people (who are assigned the turns) greater than or equal to qtty, but the turns not necessarily need to be from works.start_date to works.end_date.

What I need to know is if the works are covered properly, I mean if the works are always covered for at least qtty people from start_date to end_date.

For example: CASE 1

Work => start_date = 2018-07-01 10:00 | end_date = 2018-07-01 22:00 | qtty = 2
Turns =>
Person A: start_turn = 2018-07-01 10:00 | end_turn = 2018-07-01 22:00
Person B: start_turn = 2018-07-01 10:00 | end_turn = 2018-07-01 16:00
Person C: start_turn = 2018-07-01 16:00 | end_turn = 2018-07-01 22:00

CASE 2

Work => start_date = 2018-07-01 10:00 | end_date = 2018-07-01 22:00 | qtty = 2
Turns =>
Person A: start_turn = 2018-07-01 10:00 | end_turn = 2018-07-01 22:00
Person B: start_turn = 2018-07-01 16:00 | end_turn = 2018-07-01 22:00
Person C: start_turn = 2018-07-01 16:00 | end_turn = 2018-07-01 22:00

Seeing the first case we could resolve it just extracting the minutes of the work multiply it by qtty and then compare that value with the sum of the minutes of each turn, and then the answer is YES, the work is covered properly. But applying this method to the second case the answer is YES too, but it's incorrect 'cause the work is not covered for at least two persons from start_date to end_date.

How could I make it? Any help would be appreciated.

EDIT

Here is the correct SQL Fiddle: http://sqlfiddle.com/#!9/c53d1b

Upvotes: 1

Views: 124

Answers (2)

IVO GELOV
IVO GELOV

Reputation: 14269

Not a complete answer, just a rough idea.

In order to solve this problem you have to think out of the box and formulate the problem in a slightly different way.

  1. First you need to make a list of all the points in time regarding your working interval and its corresponding turns/shifts
  2. Then you have to remove the duplicates - i.e. leave only the distinct points in time
  3. Then you have to build a list with all the time intervals between these points in time - you will have N-1 intervals if you have N points in time
  4. Then for each of the above intervals you need to count how many of the turns/shifts contain this interval completely
  5. If for each of the above intervals you have at least qtty covering turns - then this work is fully covered by the shifts, otherwise it is not

Let's take your two example cases. For case 1 you have 3 points in time - 10, 16, 22. This means 2 intervals - from 10 to 16 and from 16 to 22. For the first interval you have 2 turns which completely contain the interval - A and B. For the second interval you have 2 turns which completely contain the interval - A and C.

For case 2 you have the same points in time and the same sub-intervals. However, this time for interval 10-16 you have only 1 turn which completely containts the interval - turn A. So for this case your work is not fully covered by the required number of turns.

I am still not sure how this can be implemented in a single SQL query - perhaps you will have to start with a stored procedure doing the things in procedural way with loop(s) and then refine it when you get a better understanding of the idea.

UPDATE

Here is some SQL to give you the initial approximation, you will tweak it according to your taste (http://sqlfiddle.com/#!9/72e841/3):

select * from
  (select p_start,p_end,qty -
    (select count(*) from turns 
      where start_turn <= p_start 
        and end_turn >= p_end
    ) as non_covered
   from
   (select pt, @start:=@end AS p_start, @end:=pt AS p_end 
     from
     (select start_date as pt from work where id=1
      union 
      select end_date as pt from work where id=1
      union 
      select start_turn from turns, work 
        where work.id=1 
          and start_turn between start_date and end_date
      union 
      select end_turn from turns, work 
        where work.id=1 
          and start_turn between start_date and end_date
     ) as tmp
     order by pt
   ) as period
   join work on work.id = 1
   where p_start <> p_end
  ) as cover
  where non_covered > 0

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

Not an answer; too long for a comment:

Case 1 : Qtty = 2


   10 11 12 13 14 15 16 17 18 19 20 21 22
A  |------------------------------------|
B  |------------------|
C                     |-----------------|

Case 2 : Qtty = 2

   10 11 12 13 14 15 16 17 18 19 20 21 22
A  |------------------------------------|
B                     |-----------------|
C                     |-----------------|

Case 2 seems to satisfy the requirement that 'those works not necessarily need to be covered from works.start_date to works.end_date'

Upvotes: 1

Related Questions