I have a Payables Query:
SELECT P.VENDORID VendorID,
V.VENDNAME VendorName,
P.VCHRNMBR Voucher,
P.dateEntered EnterDate,
P.DOCDATE DocDate,
P.GPGLpostDate,
P.DUEDATE DueDate,
P.DOCNUMBR DocumentNumber,
Cast(P.DOCAMNT as decimal (10,2)) DocumentAmount,
Cast(P.CURTRXAM as decimal (10,2)) as Balance,
P.TRXDSCRN [Description]
----------\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
----------select * from PM30200
FROM (SELECT VENDORID, VCHRNMBR, DOCTYPE,
-----DATEDIFF(month, 'DEX_ROW_TS','DOCDATE') as Months,
CAST(DEX_ROW_TS as Date) as dateEntered,
CAST(DOCDATE as Date) DocDate, Cast(PSTGDATE as Date) SysPostDate,
CAST(PSTGDATE as Date) GPGLpostDate,
Cast(DUEDATE as Date) DueDate,
DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED
FROM PM20000 where DOCTYPE='1' and VOIDED='0' and DOCDATE > '2021-01-01'
UNION ALL
SELECT VENDORID, VCHRNMBR, DOCTYPE,
-----DATEDIFF(month, 'DEX_ROW_TS','DOCDATE') as Difference,
CAST(DEX_ROW_TS as Date) as dateEntered,
CAST(DOCDATE as Date) DocDate, Cast(PSTGDATE as Date) SysPostDate,
CAST(PSTGDATE as Date) GPDate,
CAST(DUEDATE as Date) DueDate, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED
FROM PM30200 where DOCTYPE='1' and VOIDED='0' and DOCDATE > '2021-01-01') P
INNER JOIN
PM00200 V
ON V.VENDORID = P.VENDORID
Which returns a dataset as follows:
My client is trying to identify Payable Vouchers entered into GP and backdated into a different month. The first record has a document date of September 24th but was actually entered (using the SQL date/timestamp) on Oct 2.....
I tried to construct a Month Date Diff but was unsuccessful - I get a SQL error about converting
How do I restrict my query to just documents where month entered <> document month?
