on 2019 Jan 08 7:39 PM
Hi Team,
Need some help with linking of invoice to their deliveries. I am using below query but seems like its not giving me correct output.
SELECT T0.[DocNum] as 'Del DocNum', T0.[DocDate] as 'Del DocDate', T3.[DocNum] as 'Inv Docnum', T3.[DocDate] as 'Inv Docdate' FROM ODLN T0 INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry] left join INV1 T2 on T2.[BaseEntry] = T1.Docentry and T2.[BaseLine] = T1.[LineNum] INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] WHERE T0.[DocDate] between [%0] and [%1] group by T0.[DocNum], T0.[DocDate],T3.[DocNum], T3.[DocDate]
Request clarification before answering.
Great Krishnam - that explanation helps immensely!!!
Your SQL needs "DATEPART" using MM. Try this code then...and let us know if that works...
Here is the results for October 2018:
Using this SQL:
SELECT DISTINCT
T1.[DocNum] as 'Del DocNum',
T1.[DocDate] as 'Del DocDate',
T3.[DocNum] as 'Inv Docnum',
T3.[DocDate] as 'Inv Docdate'
FROM DLN1 T0
INNER JOIN ODLN T1 ON T0.[DocEntry] = T1.[DocEntry]
LEFT OUTER JOIN INV1 T2 on T2.[BaseEntry] = T1.Docentry and T2.[BaseLine] = T0.[LineNum] AND T2.ItemCode = T0.ItemCode
INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] AND T0.TrgetEntry = T3.DocEntry
WHERE
T1.[DocDate] between '[%0]' and '[%1]'
T0.LineNum = 0
AND DATEPART(MM, T1.DocDate) < > DATEPART(MM, T3.DocDate)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Krishnam - I added a few new restrictions to the code above, see if that helps...
Notice I added AND T0.LineNum = 0 in the WHERE Section and...
T0.TrgetEntry = T3.DocEntry in the INNER JOIN for OINV
Zal
Thanks a lot zal.parchem2 . It works now. Excellent!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Krishnam...
When you say it does not seem to be working correctly, what do you mean? Repeated Lines or Invoice is not found?
Here is a link for you also:
Maybe a screen print of the SQL results might help...with a short description of your problem...
Thanks and Best Regards, Zal
Try this code:
SELECT DISTINCT
T1.[DocNum] as 'Del DocNum',
T1.[DocDate] as 'Del DocDate',
T3.[DocNum] as 'Inv Docnum',
T3.[DocDate] as 'Inv Docdate'
FROM DLN1 T0
INNER JOIN ODLN T1 ON T0.[DocEntry] = T1.[DocEntry]
LEFT OUTER JOIN INV1 T2 on T2.[BaseEntry] = T1.Docentry and T2.[BaseLine] = T0.[LineNum] AND T2.ItemCode = T0.ItemCode
INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry]
WHERE T1.[DocDate] between '[%0]' and '[%1]'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Friend,
Thanks for the reply but my target is to find out the deliveries for which invoices have different posting month. Like Delivery posted in 11/28/2019 and Invoice posted on 12/01/2019. That they have different month.
When I am using below query the delivery document number is not related to invoice document number.
SELECT DISTINCT T1.[DocNum] as 'Del DocNum', T1.[DocDate] as 'Del DocDate', T3.[DocNum] as 'Inv Docnum', T3.[DocDate] as 'Inv Docdate' FROM DLN1 T0 INNER JOIN ODLN T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT OUTER JOIN INV1 T2 on T2.[BaseEntry] = T0.Docentry and T2.[BaseLine] = T0.[LineNum] AND T2.ItemCode = T0.ItemCode INNER JOIN OINV T3 ON T2.[DocEntry] = T3.[DocEntry] where format(t1.Docdate,'MMyyyy')<>format(t3.docdate,'MMyyyy') --WHERE T1.[DocDate] between '[%0]' and '[%1]'
the output gives delivery number 1569939 for which invoice is 4428 which is not correct...User | Count |
---|---|
120 | |
9 | |
8 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.