I want to find which ProductID belongs to which ShippingID. I have the ProductMade time, so I need to find between which ShippingTime the ProductMade fits in order to find the ShippingID.
I'm using MS Access as data source.
Product
| ProductID | ProductMade |
|---|---|
| 11205 | 2024-05-29 17:27 |
| 11921 | 2024-05-29 17:12 |
| 12324 | 2024-05-29 16:53 |
| 12328 | 2024-05-29 16:37 |
| 11851 | 2024-05-29 15:54 |
| 12354 | 2024-05-29 14:13 |
| 12333 | 2024-05-29 13:51 |
| 12342 | 2024-05-29 13:36 |
| 20014 | 2024-05-29 13:16 |
| 11822 | 2024-05-29 12:52 |
Shipping
| ShippingTime | ShippingID |
|---|---|
| 2024-05-29 12:49 | I0230 |
| 2024-05-28 19:31 | J0130 |
| 2024-05-29 05:04 | J0140 |
| 2024-05-29 13:37 | J0340 |
| 2024-05-29 17:05 | J0410 |
| 2024-05-28 17:07 | J0430 |
| 2024-05-29 11:24 | K0120 |
| 2024-05-29 07:01 | K0310 |
| 2024-05-28 15:01 | K0510 |
| 2024-05-28 23:43 | K0830 |
| 2024-05-29 01:48 | K0940 |
| 2024-05-29 10:12 | K1020 |
| 2024-05-29 15:57 | K1030 |
| 2024-05-28 22:02 | K1220 |
| 2024-05-29 03:41 | K1240 |
| 2024-05-29 15:51 | K1340 |
I have tried different SQL statements, but I never get the result I want. I don't know how to use BETWEEN properly so that one value (time) is at the head of the query.
BETWEEN probably won't be useful because there are not two fields in Shipping for ProductMade to be between. Need Shipping ID that has ShippingTime closest to and >=ProductMade. This involves a correlated subquery. Be aware this can perform slowly with large dataset.
SELECT Product.ProductID, Product.ProductMade, (
SELECT TOP 1 ShippingID FROM Shipping
WHERE Shipping.ShippingTime >= Product.ProductMade
ORDER BY ShippingTime) AS ShipID
FROM Product;
| ProductID | ProductMade | ShipID |
|---|---|---|
| 11205 | 5/29/2024 5:27:00 PM | |
| 11921 | 5/29/2024 5:12:00 PM | |
| 12324 | 5/29/2024 4:53:00 PM | J0410 |
| 12328 | 5/29/2024 4:37:00 PM | J0410 |
| 11851 | 5/29/2024 3:54:00 PM | K1030 |
| 12354 | 5/29/2024 2:13:00 PM | K1340 |
| 12333 | 5/29/2024 1:51:00 PM | K1340 |
| 12342 | 5/29/2024 1:36:00 PM | J0340 |
| 20014 | 5/29/2024 1:16:00 PM | J0340 |
| 11822 | 5/29/2024 12:52:00 PM | J0340 |