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.



Heres the first that shows the deliveries that were late written by John Eduardo Alfaro Vivanco from Cartagena in Bolivia



SELECT A.DocEntry , A.DocNum , C.DocDueDate AS FechaEntrega , A.DocDate
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.