Table: courier
Table: tracking
| я бы | код отслеживания | положение дел | Время | courier_id |
|---|
| 001 | 1 | доставлен | 2021-01-11 18:30:00 | 2 |
| 002 | 2 | доставлен | 2021-01-14 17:30:00 | 4 |
| 003 | 3 | доставлен | 2021-01-15 11:30:00 | 4 |
| 004 | 4 | доставлен | 2021-01-15 13:30:00 | 2 |
Table: tracking_details
| код отслеживания | положение дел | время |
|---|
| 1 | перед транзитом | 2021-01-09 10:00:00 |
| 1 | В процессе перевозки | 2021-01-10 11:00:00 |
| 1 | В процессе перевозки | 2021-01-10 11:40:00 |
| 1 | В процессе перевозки | 2021-01-11 13:49:00 |
| 1 | В процессе перевозки | 2021-01-11 15:45:00 |
| 1 | доставлен | 2021-01-11 18:30:00 |
| 2 | перед транзитом | 2021-01-10 10:00:00 |
| 2 | В процессе перевозки | 2021-01-12 11:00:00 |
| 2 | В процессе перевозки | 2021-01-12 11:40:00 |
| 2 | В процессе перевозки | 2021-01-13 13:49:00 |
| 2 | В процессе перевозки | 2021-01-13 15:45:00 |
| 2 | доставлен | 2021-01-14 17:30:00 |
| 3 | перед транзитом | 2021-01-11 10:00:00 |
| 3 | В процессе перевозки | 2021-01-13 16:00:00 |
| 3 | В процессе перевозки | 2021-01-14 11:40:00 |
| 3 | доставлен | 2021-01-15 11:30:00 |
| 4 | перед транзитом | 2021-01-13 13:00:00 |
| 4 | В процессе перевозки | 2021-01-13 17:00:00 |
| 4 | В процессе перевозки | 2021-01-14 15:20:00 |
| 4 | доставлен | 2021-01-15 13:30:00 |
/* I would like to calculate the time difference
from the first record of "in-transit" to the "delivered"
for every tracking_code and take the avg for every courier */
/* so, the result for tracking_code="1" should be 31.5 hours (2021-01-10 11:00:00 to 2021-01-11 18:30:00)
and tracking_code="2" should be 54.5 hours
tracking_code="3" = 43.5 hours
tracking_code="4" = 44.5 hours
courier.name="BBB" should be (31.5+44.5)/2= 38 hours
and "DDD" should be (54.5+43.5)/2 = 49 hours */
SELECT courier.name,
Avg(
(
SELECT Max(t2.time)
FROM tracking_details t2
INNER JOIN tracking_details t1
ON t1.tracking_code=t2.tracking_code
WHERE t2.status="in-transit"
AND t2.time>t1.time
GROUP BY t2.tracking_code)-
(
SELECT Max(t3.time)
FROM tracking_code t3
INNER JOIN tracking_code t1
ON t1.tracking_code=t3.tracking_code
WHERE t3.status="delivered"
GROUP BY t3.tracking_code))
FROM tracking
LEFT JOIN tracking_details t1
where courier.id=tracking.courier_id
GROUP BY courier.name;
