Hi Dear All
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;
is any idea i appreciate that
thanks and Regards