In SQLite, my goal is to combine Table 1 and Table 2 to create Table 3 (see Desired Table 3 below). For context, delivery date/time is always before arrival date/time, and the arrival date must be 7 days after the delivery date. If delivery and arrival are the same dates, the delivery time must be before arrival time (see pacakge_ID 90 & 56 for reference). I have tried the code below, but it did not give the desired Table 3. The result gave me package_ID 90, 56, and 20, instead of the desired Table 3. However, my code successfully removed ID 67. What other statements do I need to add for the code? I hope this is clear as I am new here.

Table 1:

Table 2:

Desired Table 3 (after coding):

Code:

CREATE TABLE table3 AS
SELECT *
FROM table1 JOIN table2 ON table1.package_ID=table2.package_ID AND delivery_DATE BETWEEN DATE(arrival_DATE,'-7 day') AND arrival_DATE 
AND TIME(delivery_TIME) < TIME(arrival_TIME) 
ORDER BY package_ID DESC;

CodePudding user response：

If all the dates have the format YYYY-MM-DD and all the times have the format hh:mm:ss (your sample data contains times that don't have this format), then you must concatenate date and time in the conditions of the WHERE clause:

SELECT t1.*, t2.arrival_DATE, t2.arrival_TIME
FROM table1 t1 JOIN table2 t2 
ON t2.package_ID = t1.package_ID 
AND t1.delivery_DATE || ' ' || t1.delivery_TIME 
    BETWEEN 
      date(t2.arrival_DATE, '-7 day')  || ' ' || t2.arrival_TIME
      AND 
      t2.arrival_DATE  || ' ' || t2.arrival_TIME
ORDER BY t1.package_ID DESC;

See the demo.