Here are some sample, example queries used in SAP Business One to execute data on customers, orders and items.

Queries with Parameters

Sales by Sales employee

SELECT datepart(yyyy, T0.DocDate) as [year], sum(T0.DocTotal) as Total
FROM ORDR T0
WHERE T0.SlpCode = [%0]
group by datepart(yyyy, T0.DocDate)

Sales by BP

SELECT datepart(yyyy, T0.DocDate) as [year], sum(T0.DocTotal) as Total
FROM ORDR T0
WHERE T0.CardCode = '[%0]'
group by datepart(yyyy, T0.DocDate)

Sales by User

SELECT datepart(yyyy, T0.DocDate) as [year], sum(T0.DocTotal) as Total
FROM ORDR T0
WHERE T0.UserSign = [%0]
group by datepart(yyyy, T0.DocDate)

Sales Total by date from / to.

SELECT *
FROM ORDR T0
WHERE T0.DocDate >= [%0] AND T0.DocDate <= [%1]

Simple Queries

Open Sales orders by customer

SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[DocTotal]
FROM ORDR T0
WHERE T0.[DocStatus] ='O'
ORDER BY T0.[CardCode]

--To allow a user to select customer or leave it blank use
SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[DocTotal]
FROM ORDR T0
WHERE T0.[DocStatus] ='O' AND  T0.[CardName] Like '%%[%0]%%'
ORDER BY T0.[CardCode], T0.[CardName]

Open Sales Orders grouped by customer

SELECT T0.[CardCode], T0.[CardName], SUM(T0.[DocTotal])
FROM ORDR T0
WHERE T0.[DocStatus] ='O'
GROUP BY T0.[CardCode], T0.[CardName]

Open Sales Orders by customer group

SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[DocTotal], T2.[GroupName]
FROM ORDR T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode
ORDER BY T2.[GroupName]

Open Sales order lines by customer

SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate], T1.[ItemCode],
T1.[Dscription], T1.[Quantity], T1.[OpenQty], T1.[Price], T1.[LineTotal]
FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[DocStatus] = 'O' AND  T1.[LineStatus] ='O'
AND  T0.[CardName] Like '%%[%0]%%'
AND  T0.[DocDate] >=[%1]
AND  T0.[DocDate] <=[%2]

Open Sales order lines grouped by item

SELECT T0.[ItemCode], T0.[Dscription],
SUM(T0.[OpenQty]) AS 'Total Open Qty', SUM(T0.[LineTotal]) AS 'Total Order Value'
FROM RDR1 T0
GROUP BY T0.[ItemCode], T0.[Dscription]

Closed sales order lines that short shipped or never delivered

SELECT T1.[ItemCode], T1.[Dscription],
T1.[Quantity] AS 'Order Qty',
T2.[Quantity] AS 'Delivered Qty',
T1.[Price], T1.[LineTotal]
FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN DLN1 T2 ON T1.DocEntry = T2.BaseEntry AND T1.LineNum = T2.BaseLine
WHERE T0.[DocStatus] ='C' AND  T1.[LineStatus] ='C'
  AND (T1.[Quantity] -  T2.[Quantity] > 0 OR T2.Quantity IS NULL)
ORDER BY T1.[ItemCode]

Open Purchase Orders by Vendor

SELECT T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate],
T0.[DocDueDate], T0.[DocTotal]
FROM OPOR T0 WHERE T0.[DocStatus] ='O'
ORDER BY T0.[CardName]

Items on Open Purchase Orders

SELECT T1.[ItemCode], T1.[Dscription], T1.[Quantity],
T1.[OpenQty], T0.[DocDueDate], T0.[CardCode],
T0.[CardName]
FROM OPOR T0  INNER JOIN POR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[LineStatus] ='O'
ORDER BY T1.[ItemCode]

Items OnHand sorted by item group

SELECT T0.[ItemCode], T0.[ItemName], T0.[ItmsGrpCod], T1.[ItmsGrpNam],
T0.[OnHand], T0.[OnOrder], T0.[CardCode]
FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
INNER JOIN OITW T2 ON T0.ItemCode = T2.ItemCode
ORDER BY T1.[ItmsGrpNam]

Items OnHand by item warehouse

SELECT T0.[ItemCode], T0.[ItemName], T0.[ItmsGrpCod], T1.[ItmsGrpNam],
T2.[WhsCode], T2.[OnHand], T2.[OnOrder],  T0.[CardCode]
FROM OITM T0  INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
INNER JOIN OITW T2 ON T0.ItemCode = T2.ItemCode
WHERE T2.[WhsCode]  Like '%%[%0]%%'
ORDER BY T1.[ItmsGrpNam], T0.[ItemCode]

Business Partner account balances and billing addresses

SELECT T0.[CardCode], T0.[CardName], T0.[GroupCode], T0.[CntctPrsn],
T0.[Balance], T1.[Street], T1.[Block], T1.[City], T1.[State],
T1.[ZipCode]
FROM OCRD T0  INNER JOIN CRD1 T1 ON T0.CardCode = T1.CardCode
WHERE T1.[AdresType] ='B' AND  T0.[Balance] >=1000
ORDER BY T0.[GroupCode], T0.[CardCode]

Balances by business partner group

SELECT T1.[GroupName], SUM(T0.[Balance])
FROM OCRD T0  INNER JOIN OCRG T1 ON T0.GroupCode = T1.GroupCode
GROUP BY T1.[GroupName]