Query to show when dates are amended
I would like to create an query that shows me whenever a date on a purchase order is amended. I would then link this to an alert. Ultimately, I would like the query results to be something like:
DocNum/CardName/Previous Date/New Date
Is this possible?
Nagarajan K replied
SELECT Distinct T2.DocNum,T2.[CardName],T2.[NumAtCard],T0.ItemCode,T1.[DocDueDate] 'New Due Date',
T2.[DocDueDate] 'Previous Due date ', T2.UpdateDate, T2.[DocTime], T4.[U_Name] as 'UpdatedBy'
FROM adoc T2
JOIN ado1 T0 ON T2.docentry = T0.docentry AND T2.Objtype = '22'
JOIN Opor T1 ON T1.docentry = T0.docentry
INNER JOIN OUSR T4 ON T2.UserSign2 = T4.INTERNAL_K
WHERE T2.[DocStatus] = 'O' AND (DateDiff(d,T2.UpdateDate,GETDATE()) <= 0) AND (T2.[DocDueDate] != T1.[DocDueDate])