Determine closest date to another date value teradata

Question

My dataset looks like this. For every combination of customerid,orderid and ship date, i would like to retrieve 1 process date that is less than or equal to the ship date. If the process date is greater than the ship date and no lower process date exist, then use the ship date as the process date

+-------------+----------+------------+--------------+--+
| Customer ID | Order ID | Ship Date  | Process Date |  |
+-------------+----------+------------+--------------+--+
|        1000 |      100 | 9/17/2020  | 9/17/2020    |  |
|        1000 |      100 | 9/17/2020  | 10/16/2020   |  |
|        1000 |      100 | 9/17/2020  | 9/16/2020    |  |
|        2000 |      200 | 8/15/2020  | 8/13/2020    |  |
|        2000 |      300 | 10/14/2020 | 10/13/2020   |  |
|        3000 |      400 | 3/4/2020   | 4/2/2020     |  |
|        3000 |      400 | 3/4/2020   | 3/3/2020     |  |
|        3000 |      400 | 3/4/2020   | 3/5/2020     |  |
|        4000 |      500 | 5/1/2020   | 5/3/2020     |  |
|        5000 |      600 | 6/1/2020   | 7/1/2020     |  |
|        5000 |      600 | 6/1/2020   | 7/2/2020  
|        6000 |      700 | 7/14/2020  | 7/13/2020    |  |
|        6000 |      700 | 7/14/2020  | 6/10/2020    |  |
+-------------+----------+------------+--------------+--+   |  |
    +-------------+----------+------------+--------------+--+

Desired Output

+-------------+----------+------------+--------------+--+
| Customer ID | Order ID | Ship Date  | Process Date |  |
+-------------+----------+------------+--------------+--+
|        1000 |      100 | 9/17/2020  | 9/17/2020    |  |
|        2000 |      200 | 8/15/2020  | 8/13/2020    |  |
|        2000 |      300 | 10/14/2020 | 10/13/2020   |  |
|        3000 |      400 | 3/4/2020   | 3/3/2020     |  |
|        4000 |      500 | 5/1/2020   | 5/1/2020     |  |
|        5000 |      600 | 6/1/2020   | 6/1/2020     |  |
|        6000 |      700 | 7/14/2020  | 7/13/2020    |  |
+-------------+----------+------------+--------------+--+

I tried using ROWNUM and date difference, but I'm stuck after getting the row number in ascending order.Not sure how to proceed ahead.

jarlh · Accepted Answer · 2020-11-15T20:45:47.577

1

"If the process date is greater than the ship date and no lower process date exist, then use the ship date as the process date."

Do a GROUP BY. You can use MAX() to return the latest ProcessDate <= ShipDate. If no such ProcessDate exists, return ShipDate.

select CustomerID, orderID, ShipDate,
       coalesce(MAX(case when ProcessDate <= ShipDate then ProcessDate end), ShipDate)
from tablename
group by CustomerID, orderID, ShipDate

edited Nov 15 '20 at 20:45

answered Nov 15 '20 at 19:29

jarlh

35,821
8
33
49

1

Thank you, Jarlh. This was immensely helpful! – Vinay Nov 16 '20 at 18:56

score 0 · Answer 2 · answered Nov 15 '20 at 19:29

0

I think you want filtering and row_number():

select t.*
from (select t.*,
             row_number() over (partition by customer_id, order_id, ship_date order by process_date desc) as seqnum
      from t
      where process_date <= ship_date
     ) t
where seqnum = 1;

I'm not sure if customer_id and ship_date are really needed in the partition by clause. order_id seems sufficient.

answered Nov 15 '20 at 19:29

Gordon Linoff

1,122,135
50
484
624

Thank you Gordon. I just realized I forgot to add another condition. Just edited the original question to reflect it. Appreciate your help. – Vinay Nov 15 '20 at 20:11

dnoeth · Answer 3 · 2020-11-16T18:55:01.470

0

This should return the expected result:

select CustomerID, orderID, ShipDate, 
   -- If the process date is greater than the ship date and no lower 
   -- process date exist, then use the ship date as the process date
   least(ProcessDate, ShipDate)
from tablename
qualify
   -- retrieve 1 process date that is less than or equal to the ship date
   row_number() 
   over (partition by CustomerID, orderI
         order by case when ProcessDate <= ShipDate then ProcessDate end desc nulls last) = 1

edited Nov 16 '20 at 18:55

answered Nov 16 '20 at 10:00

dnoeth

54,996
3
29
45

Thank you Dnoeth. However, this retrieves 9/16 for the customer 1000 but I was hoping to get 9/17. And if a customer has multiple same ship dates, and if there are process dates lower than ship dates (no process dates greater than or equal to ship date, then the one with most closest to ship date should be picked. Example customer 6000 above) – Vinay Nov 16 '20 at 17:14
Changed the Order By to match your neew description. – dnoeth Nov 16 '20 at 18:55

Determine closest date to another date value teradata

3 Answers3