SQL: usage time of item between dates combining two tables-CodePudding

Trying to create query that will give me usage time of each car part between dates when that part is used. Etc. let say part id 1 is installed on 2018-03-01 and on 2018-04-01 runs for 50min and then on 2018-05-10 runs 30min total usage of this part shoud be 1:20min as result.

These are examples of my tables.

Table1
| id | part_id | car_id | part_date  |
|----|-------- |--------|------------|
| 1  | 1       | 3      | 2018-03-01 |
| 2  | 1       | 1      | 2018-03-28 |
| 3  | 1       | 3      | 2018-05-10 |

Table2
| id | car_id | run_date   | puton_time          | putoff_time         |
|----|--------|------------|---------------------|---------------------|
| 1  | 3      | 2018-04-01 | 2018-04-01 12:00:00 | 2018-04-01 12:50:00 |
| 2  | 2      | 2018-04-10 | 2018-04-10 15:10:00 | 2018-04-10 15:20:00 |
| 3  | 3      | 2018-05-10 | 2018-05-10 10:00:00 | 2018-05-10 10:30:00 |
| 4  | 1      | 2018-05-11 | 2018-05-11 12:00:00 | 2018-04-01 12:50:00 |

Table1 contains dates when each part is installed, table2 contains usage time of each part and they are joined on car_id, I have try to write query but it does not work well if somebody can figure out my mistake in this query that would be healpful.

My SQL query

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(t1.puton_time, t1.putoff_time)))) AS total_time
FROM table2 t1 
LEFT JOIN table1 t2 ON t1.car_id=t2.car_id
WHERE t2.id=1 AND t1.run_date BETWEEN t2.datum AND 
(SELECT COALESCE(MIN(datum), '2100-01-01') AS NextDate FROM table1 WHERE 
id=1 AND t2.part_date > part_date);

Expected result
| part_id | total_time |
|---------|------------|
| 1       | 1:20:00    |

Hope that this problem make sence because in my search I found nothing like this, so I need help.

Solution, thanks to Kota Mori

SELECT t1.id, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(t2.puton_time, t2.putoff_time)))) AS total_time
FROM table1 t1
LEFT JOIN table2 t2 ON t1.car_id = t2.car_id
AND t1.part_date >= t2.run_date
GROUP BY t1.id

CodePudding user response：

You first need to join the two tables by the car_id and also a condition that part_date should be no greater than run_date. Then compute the total minutes for each part_id separately.

The following is a query example for SQLite (The only SQL engine that I have access to right now). Since SQLite does not have datetime type, I convert strings into unix timestamp by strftime function. This part should be changed in accordance with the SQL engine you are using. Apart from that, this is fairly a standard sql and mostly valid for other SQL dialect.

SELECT
  t1.id,
  sum(
    cast(strftime('%s', t2.putoff_time) as integer) -
    cast(strftime('%s', t2.puton_time) as integer)
  ) / 60 AS total_minutes
FROM
  table1 t1
LEFT JOIN
  table2 t2
ON
  t1.car_id = t2.car_id
  AND t1.part_date <= t2.run_date
GROUP BY
  t1.id

The result is something like the below. Note that ID 1 gets 80 minutes (1:20) as expected.

   id  time_in_minutes
0   1               80
1   2               80
2   3               30