PHP get between dates from all row MYSQL

Question

Here is my Work table

    ID  WORK_ID   DATE
1    5       2018-05-10
2    6       2018-05-12
3    5       2018-05-15
5    5       2018-05-16
6    6       2018-05-16
.
.

I have a function that calculate business day between dates name kac_is_gunu($date1,$date2) work just fine.

My Question is; It's a big table have too many rows. I want to find difference between every dates for each work_id

For example in this table for work_id 5 kac_is_gunu(2018-05-15,2018-05-10) kac_is_gunu(2018-05-16,2018-05-15)

How can I get the dates like this from this table to my code ?

Here is the result that I want;

2018-05-16 - 2018-05-15 (Pair 1 of work_id 5)
2018-05-15 - 2018-05-10 (Pair 2 of work_id 5)
2018-05-16 - 2018-05-12 (Pair 1 of work_id 6)
.
.

later I will calculate business days between dates

The reason why I'm doing this is; I only have work end dates as date once a work done it means next started so the start date of a work is the end of previous

Possible duplicate of get mysql data between two dates using php — Loek
– Loek, Commented May 16, 2018 at 14:16
What's the point of getting the difference between each of them? Won't the result be the same as getting the difference between the first and the last? — Patrick Q
– Patrick Q, Commented May 16, 2018 at 14:19
I don't have work start dates, a work start with previous one end I want to calculate how many days each job lasting. — TahaG
– TahaG, Commented May 16, 2018 at 14:59
Your question is unclear... I guess you want/should GROUP BY WORK_ID ORDER BY DATE ASC to get relevant data from DB and then process data with PHP through a loop to get the difference of days between the two dates — Mike Casan Ballester
– Mike Casan Ballester, Commented May 16, 2018 at 15:17

Lajos Arpad · Accepted Answer · 2018-05-18 09:22:41Z

0

This is how you can select all pairs:

select t1.ID as t1ID, t2.ID as t2ID, t1.WORK_ID as, datediff(t2.`DATE`, t1.`DATE) as delta
from Work t1
join Work t2
on t1.WORK_ID = t2.WORK_ID and t1.`ID` < t2.`ID`

But are you sure you want to select all pairs instead of pairs consecutive to each-other?

EDIT

The initial query selected all pairs, now we will take a look at how consecutive items can be selected:

select t1.ID as t1ID, t2.ID as t2ID, t1.WORK_ID as, datediff(t2.`DATE`, t1.`DATE) as delta
from Work t1
join Work t2
on t1.WORK_ID = t2.WORK_ID and t1.`ID` < t2.`ID`
where not exists
    (select 1
     from Work t3
     where t1.`ID` < t3.`ID` and t3.`ID` < t2.`ID` and t1.WORK_ID = t3.WORK_ID)

Note that the query is similar with the initial query in terms of finding pairs with similar WORK_ID where t1.ID < t2.ID but we add an additional condition that a record meeting some criteria does not exist. The criteria is that the ID of the third record is between the ID of t1 and t2 and has similar WORK_ID. Naturally, you can skip the ID comparisons to date comparisons.

edited May 18, 2018 at 9:22

answered May 16, 2018 at 14:29

Lajos Arpad

80.4k42 gold badges123 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Strawberry Over a year ago

Are you sure that the OP doesn't?

Lajos Arpad Over a year ago

@Strawberry no, I am not sure. In my answer I had to guess the actual intent and I am ready to edit or remove it if needed.

TahaG Over a year ago

I think I'm not supposed to use t1 ID < t2 ID because sometimes I enter the work data after job done and by mistake I can enter later data first. Can I use t1.date < t2.date ?

Lajos Arpad Over a year ago

@TahaG yes, you can.

TahaG Over a year ago

Dear Lajos how can I do consecutive ?

Strawberry · Accepted Answer · 2018-05-16 14:34:50Z

0

SELECT x.date start_date
     , MIN(y.date) end_date 
  FROM my_table x 
  JOIN my_table y 
    ON y.work_id = x.work_id 
   AND y.date > x.date 
 WHERE x.work_id = 5
 GROUP 
    BY x.date;

...which can be extended to (untested)...

SELECT x.*
     , 5 * (DATEDIFF(LEAST(x.end_date,LAST_DAY(x.start_date)), x.start_date) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(x.start_date) + WEEKDAY(x.end_date) + 1, 1) total 
  FROM 
     (
SELECT a.date start_date
     , MIN(b.date) end_date 
  FROM my_table a 
  JOIN my_table b 
    ON b.work_id = a.work_id 
   AND b.date > a.date 
 WHERE a.work_id = 5
 GROUP 
    BY a.date
     ) x

answered May 16, 2018 at 14:34

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

PHP get between dates from all row MYSQL

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related