Often times you need to track different activities in SAP Business One, manage KPI's or generate ad-hoc data sets for further analysis. The SAP Business One Query Tools are great to do this but sometimes it can take a while to master the process of building queries and understanding the different concepts of table joins asnd also the data structures in SAP Business One.
This is where the assistance of your peers can be invaluable to help you understand how something is achieved. If you are like me, when you are trying to understand how a technology works it's helpful to have examples to refer to and its even better when those examples can be leveraged in day to day operations of the solution.
Fortunately I found a recent scenario on the Facebook group I moderate ehre a member of the group wanted to find out which suppliers were late with their deliveries and two other members stepped up and provided the necessary queries to assist and they agreed to share them with you here.
SELECT A.DocEntry , A.DocNum , C.DocDueDate AS FechaEntrega , A.DocDate FROM OPDN A INNER JOIN PDN1 B ON A.DocEntry = B.DocEntry INNER JOIN OPOR C ON B.BaseEntry = C.DocEntry WHERE A.DocDate > C.DocDueDate GROUP BY A.DocEntry , A.DocNum , C.DocDueDate , A.DocDate
And another variation that shows deliveries not yet received that are running late written by Joerg Aldinger from SAP Business One partner Consultoria in the Dominican Republic
SELECT T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate", T0."DocDueDate" FROM OPOR T0 WHERE T0."DocDueDate" < CURRENT_DATE AND T0."DocStatus" = 'O'
For MS SQL instead of HANA exchange the string CURRENT_DATE for GETDATE()..
Thanks to both John and Joerg for their willingness to share their work.