In SQL Server we just have 2 function (LEAD & LAG) for calculating between a value in same column at different rows and i know about them.
In one Project i have to working with ms Access and that function i really need to.
I used below query and it works fine but it is very slow , for around 313000 record it will take 45 minutes or more , but in SQL Server with that functions (LEAD & LAG) it will take 1 sec.
My Query :
SELECT MD.WONO, MD.TradeID, MD.StatusID, MD.CreateDate, MD.NextCreateDate, Val(MD.CreateDate-MD.NextCreateDate) AS Dur FROM (SELECT WONO, TradeID, StatusID, CreateDate, Nz((SELECT MIN(st2.CreateDate) FROM WorkOrderFlow_Trade as st2 WHERE st2.WONO = st.WONO AND st2.ID > st.ID),DATE()) AS NextCreateDate FROM WorkOrderFlow_Trade AS st ORDER BY WONO, ID) AS MD;