Find date difference between consecutive rows in Teradata
Contents
Calculate date difference between consecutive rows
Sometime we will get the requirement to calculate the date difference for consecutive rows within single column in Teradata. Since we need to find the date difference within a same table,we have to perform the join operation for the same table itself.
Sample table structure in Teradata
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE MULTISET TABLE Ecommerce_DB.Order_History,FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO, MAP = TD_MAP1 ( Customer_id INTEGER, Customer_Name VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC, Order_Date DATE FORMAT 'YYYY/MM/DD' ) PRIMARY INDEX ( Customer_Id ); |
Query to find the date difference for consecutive rows
|
1 2 3 4 5 6 7 8 9 10 |
SELECT T1.Customer_id, (T2.Order_Date - T1.Order_Date) as Total_Days FROM Ecommerce_DB.Order_History AS T1 JOIN Ecommerce_DB.Order_History AS T2 ON T1.Customer_id = T2.Customer_id AND T2.Order_Date > T1.Order_Date; |
Explanation:
Same customer placing the order in two different dates and it has stored in the table Order_History.The E-commerce website wants to find the date difference between two orders for analytical purpose.
In that case,we can use the join for the Order_History table itself and validate second order date is greater than the first order date.Then it is easy to subtract the dates to find the difference between two order dates.
Output:
