SAP HANA: days between two Orders

Question

Here is the table ihave, i was trying days between by joining the same table with left join and group by with min difference. I was not so successful.

 Customer|Order|Date
    1    | 1   |Date1
    1    | 2   |Date2
    1    | 3   |Date3
    1    | 4   |Date4
    2    | 1   |Date1
    2    | 2   |Date3
    2    | 3   |Date6
    3    | 1   |Date3
    3    | 2   |Date5

Required is:

 Customer|Order|Date |diff
    1    | 1   |Date1| 0
    1    | 2   |Date2| days_betwen(Date2, Date1)
    1    | 3   |Date3| days_betwen(Date3, Date2)
    1    | 4   |Date4| days_betwen(Date4, Date3)
    2    | 1   |Date1| 0
    2    | 2   |Date3| days_betwen(Date3, Date1)
    2    | 3   |Date6| days_betwen(Date6, Date3)
    3    | 1   |Date3| 0
    3    | 2   |Date5| days_betwen(Date5, Date3)

I need suggestion with the logic part!

EDIT: What if the order numbers are not sequential?

Answer 1

In first, you need to join the table to itself by Customer and Order fields. Then use DATEDIFF() function to get days number between two dates.

1. If the Order column is numbered sequentially then solution is simplest:

   SELECT 
     cur.`Customer` AS `Customer`,
     cur.`Order` AS `Order`, 
     cur.`Date` AS `Date`, 
     DATEDIFF(cur.`Date`, IFNULL(prv.`Date`, cur.`Date`)) AS `DaysPassed`
   FROM 
     MyTable cur
     LEFT JOIN 
     MyTable prv
     ON cur.`Customer` = prv.`Customer` AND cur.`Order` = prv.`Order`+ 1;
   

2. If the Order column is not numbered sequentially, but next Order value is greater than previous, then you could use greater than or less than operators. Use GROUP BY clause and an aggregate function to return single row for each order. Note, maybe it will be long!

   SELECT 
       comb.`Customer` AS `Customer`,
       comb.`curOrder` AS `Order`, 
       comb.`curDate` AS `Date`, 
       DATEDIFF(comb.`curDate`, IFNULL(pr.`Date`, comb.`curDate`)) AS `DaysPassed`
   FROM
       (SELECT 
         cur.`Customer` AS `Customer`, cur.`Order` AS curOrder, cur.`curDate` AS `Date`, max(prv.`Order`) AS `prvOrder`
       FROM 
         MyTable cur
         LEFT JOIN 
         MyTable prv
         ON cur.`Customer` = prv.`Customer` AND cur.`Order` > prv.`Order`
         GROUP BY cur.`Order`, cur.`Customer`) comb
       LEFT JOIN
       MyTable pr 
       ON pr.`Customer` = comb.`Customer` AND pr.`Order` = comb.prvOrder;
   

3. If you use random order number, then it is possible to use Date column instead of Order in the comb subquery to join records by nearest order dates of same customer.

Good luck!