Skip to Content
0
Jun 19, 2020 at 05:54 PM

Finding orders created within 15 minutes of their related quote

41 Views

I'm trying to get the lag time between when a quote is created and when its related order is created, but when I compare the two DocTime fields I'm getting some strange results. For instance quote created on 8:33, order created on 8:37 it correctly shows a 4 minute lag time. On another results, though, the quote was created on 5:56, the order on 6:00 and the lag time shown is 44 minutes. It's unclear to me why the math sometimes works and sometimes doesn't.

SELECT T0."DocNum", T3."DocNum", T0."DocDate", T0."DocTime", T3."DocDate", T3."DocTime", Case when T0."DocDate" = T3."DocDate" then T3."DocTime"-T0."DocTime" else 999 end "Lag Time", T0."Printed" FROM OQUT T0 INNER JOIN QUT1 T1 ON T0."DocEntry" = T1."DocEntry" Left Join RDR1 T2 on T1."TrgetEntry" = T2."DocEntry" INNER JOIN ORDR T3 ON T2."DocEntry" = T3."DocEntry" INNER JOIN OHEM T4 ON T0."UserSign" = T4."userId" WHERE T3."DocDate" >= Add_Days(Current_Date,-7) GROUP BY T0."DocNum", T3."DocNum", T0."DocDate", T0."DocTime", T3."DocDate", T3."DocTime", T0."Printed"