Available LEAD AND LAG Function in Ms Access
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 FARZAD KHAJEH