Displaying pending sales orders, which have not been shipped completely to the customer? The requirement is to add a filter by notes column from the OCRD table.
The solution is:
SELECT T1.DocNum as 'Sales Order No',
T1.DocDate as 'Sales Order Date',
T1.CardName, T0.Dscription,
T0.Quantity as 'Sales Order Qty',
T0.Quantity-T0.OpenQty as 'Delivered Qty',
T0.OpenQty as 'Balance Qty',
T0.Price as 'Selling Price',
T0.OpenQty*T0.Price as 'Open Amount', T3.Notes
FROM dbo.RDR1 T0
INNER JOIN dbo.ORDR T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN dbo.ITM1 T2 ON T0.ItemCode = T2.ItemCode AND T2.PriceList = 1
INNER JOIN dbo.OCRD T3 ON T3.CardCode = T1.CardCode
WHERE T1.DocDate between [%0] and [%1] AND
T0.LineStatus = 'O' AND T3.Notes LIKE '%[%2]%'
A user would like to add the name of the Sales Employee that created the Sales Order to an existing query.
The solution is:
SELECT T0.DocNum as 'SO No.',
T0.DocDate as 'Date',
DateDiff(DD, T0.DocDate,GetDate()) AS 'DayDiff',
T0.CardCode as 'Customer Code',
T0.CardName as 'Customer Name',
T0.NumAtCard as 'Ref/PO No.',
(T0.DocTotal - T0.VatSum) as 'SO Amt',
T1.SLPName
FROM dbo.ORDR T0
INNER JOIN dbo.OSLP T1 ON T1.SLPCODE=T0.SLPCODE
WHERE DateDiff(DD, T0.DocDate, GetDate()) BETWEEN 1 AND 7
Depending on your localization, sometimes you might need to know which A/R Credit Memos still have not been converted into Incoming Excise Invoices.
Here is the solution:
SELECT distinct T0.[DocNum],
T0.[DocDate],
T0.[CardName]
FROM dbo.ORIN T0
INNER JOIN dbo.RIN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.[DocDate] between '[%0]' and '[%1]' and T1.TargetType != XXX
4. Goods Receipt PO within 10 days
SELECT T0.CardCode,
Max(T0.Cardname) 'Vendor Name',
SUM(T0.DocTotal) 'Total'
FROM dbo.OPDN T0
WHERE T0.DocDate >= '[%0]'
AND T0.DocDate <= '[%0]' + 10
AND T0.CardCode NOT in (SELECT T1.CardCode FROM OPDN T1 WHERE T1.DocDate >= '[%0]' AND T1.DocDate <= '[%0]' + 10
GROUP BY T1.CardCode, T1.DocDate Having COUNT(T1.DocDate) > 10)
GROUP BY T0.CardCode
ORDER BY T0.CardCode
Quantity purchased, received, and returned
SELECT T0.ItemCode, SUM(T0.Quantity) 'PO Qty',
SUM(T1.Quantity) 'GRPO Qty',
SUM(T2.Quantity) 'Return Qty'
FROM dbo.POR1 T0
LEFT JOIN dbo.PDN1 T1
ON T0.DocEntry = T1.BaseEntry AND T0.LineNum = T1.BaseLine
LEFT JOIN dbo.RPD1 T2
ON T1.DocEntry = T2.BaseEntry AND T1.LineNum = T2.BaseLine
WHERE T0.ItemCode is not NULL AND T0.DocDate >= [%0] AND T0.DocDate <= [%1]
GROUP BY T0.ItemCode
ORDER BY T0.ItemCode
Customized sales analysis report
This query should include Invoices and Credit Memos, or the debit and the credit columns in the journal entries details.
The solution is:
SELECT T0.[CardCode],
T0.[CardName],
(SUM(T1.Debit) - sum(T1.Credit)) AS 'July',
(SUM(T2.Debit) - sum(T2.Credit)) AS 'August'
FROM dbo.OCRD T0
LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND Month(T1. Duedate) = 7 AND Year(T1.Duedate) = Year(GetDate()) AND T1.TransType in ('13','14')
LEFT JOIN dbo.JDT1 T2 ON T2.ShortName = T0.CardCode AND Month(T2. Duedate) = 8 AND Year(T2.Duedate) = Year(GetDate()) AND T2.TransType in ('13','14')
WHERE T0.CardType = 'C'
Group By T0.[CardCode], T0.[CardName]
Average sales per month
SELECT T0.CardCode, T0.CardName,
(SUM(T1.Debit) - sum(T1.Credit)) AS '2009',
((SUM(T1.Debit) - sum(T1.Credit))/12) AS '2009 Avg',
(SUM(T2.Debit) - sum(T2.Credit)) AS '2010',
((SUM(T2.Debit) - sum(T2.Credit))/Month(GetDate()))AS '2010 Avg'
FROM dbo.OCRD T0
LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND Year(T1.
Duedate) = 2009 AND T1.TransType in ('13','14')
LEFT JOIN dbo.JDT1 T2 ON T2.ShortName = T0.CardCode AND Year(T2.
Duedate) = 2010 AND T2.TransType in ('13','14')
WHERE T0.CardType = 'C'
Group By T0.CardCode, T0.CardName
show the credit memo, document number, and the change log user name for the credit memo.
SELECT Distinct T0.[DocNum], T1.DocNum, T0.[DocType], T0.[CardCode],
T0.[CardName], T0.[UserSign], T0.[UserSign2], T1.[UserSign],
T1.[UserSign2]
FROM dbo.ADOC T0
INNER JOIN dbo.ORIN T1 ON T0.DocNum = T1.DocNum
WHERE T0.[ObjType] = '14' AND T0.[UserSign2] != T1.[UserSign2]
8. SQL query that identifies when the delivery date on a Sales Order is modified from the original date.
SELECT T0.DocNum,
MAX(T1.UpdateDate) 'Last Update'
From dbo.ADOC T0
JOIN dbo.ADOC T1 on T1.DocNum = T0.DocNum AND T1.ObjType = '17'
WHERE T0.ObjType = '17' and T0.DocDuedate != T1.DocDueDate
Group BY T0.DocNum
to show quantity, sales dollar amount, and tax amount for each warehouse and customer group.
The solution is:
SELECT
T3.WhsCode AS 'WH',
T1.GroupName AS 'Cust Grp',
(Sum(ISNULL(T3.Quantity,0)) - Sum(ISNULL(T5.Quantity,0))) AS 'Quantity',
(Sum(ISNULL(T3.LineTotal,0)) - Sum(ISNULL(T5.LineTotal,0))) AS 'Sls Dlr Amt',
(Sum(ISNULL(T3.LineVat,0)) - Sum(ISNULL(T5.LineVat,0))) AS 'Tax Amt'
FROM dbo.OCRD T0
INNER JOIN dbo.OCRG T1 ON T0.GroupCode = T1.GroupCode
INNER JOIN dbo.OINV T2 ON T0.CardCode = T2.CardCode AND T2.DocType = 'I'
INNER JOIN dbo.INV1 T3 ON T2.DocEntry = T3.DocEntry
LEFT JOIN dbo.ORIN T4 ON T0.CardCode = T4.CardCode AND T4.DocType = 'I'
LEFT JOIN dbo.RIN1 T5 ON T4.DocEntry = T5.DocEntry AND T3.WhsCode = T5.WhsCode
Group By
T3.WhsCode, T1.GroupName
Credit Memo user check
SELECT Distinct T0.[DocNum], T1.DocNum, T0.[DocType], T0.[CardCode],
T0.[CardName], T0.[UserSign], T0.[UserSign2], T1.[UserSign],
T1.[UserSign2]
FROM dbo.ADOC T0
INNER JOIN dbo.ORIN T1 ON T0.DocNum = T1.DocNum
WHERE T0.[ObjType] = '14' AND T0.[UserSign2] != T1.[UserSign2]
SQL query that identifies when the delivery date on a Sales Order is modified from the original date. This query needs to list who made the change and the date of the change.
SELECT T0.DocNum,
MAX(T1.UpdateDate) 'Last Update'
From dbo.ADOC T0
JOIN dbo.ADOC T1 on T1.DocNum = T0.DocNum AND T1.ObjType = '17'
WHERE T0.ObjType = '17' and T0.DocDuedate != T1.DocDueDate
Group BY T0.DocNum
Reducing from two to one line for the sales summary
SELECT
T3.WhsCode AS 'WH',
T1.GroupName AS 'Cust Grp',
(Sum(ISNULL(T3.Quantity,0)) - Sum(ISNULL(T5.Quantity,0))) AS
'Quantity',
(Sum(ISNULL(T3.LineTotal,0)) - Sum(ISNULL(T5.LineTotal,0))) AS 'Sls
Dlr Amt',
(Sum(ISNULL(T3.LineVat,0)) - Sum(ISNULL(T5.LineVat,0))) AS 'Tax Amt'
FROM dbo.OCRD T0
INNER JOIN dbo.OCRG T1 ON T0.GroupCode = T1.GroupCode
INNER JOIN dbo.OINV T2 ON T0.CardCode = T2.CardCode AND T2.DocType =
'I'
INNER JOIN dbo.INV1 T3 ON T2.DocEntry = T3.DocEntry
LEFT JOIN dbo.ORIN T4 ON T0.CardCode = T4.CardCode AND T4.DocType =
'I'
LEFT JOIN dbo.RIN1 T5 ON T4.DocEntry = T5.DocEntry AND T3.WhsCode =
T5.WhsCode
Group By
T3.WhsCode, T1.GroupName
13. Tax code summary
SELECT M.CardCode,M.CardName as 'Vendor Name',
(SELECT Sum(TaxSum) FROM PCH4 where statype=1 and relatetype = 1 and
DocEntry=M.DocEntry) as 'AmountOfTax1',
(SELECT Sum(TaxSum) FROM PCH4 where statype=7 and relatetype = 1 and
DocEntry=M.DocEntry) as 'AmountOfTax2'
FROM dbo.OPCH M Inner JOIN PCH1 L on L.DocEntry=M.DocEntry
Inner JOIN dbo.PCH4 T on T.DocEntry=L.DocEntry
Inner JOIN dbo.PCH12 T0 on T.DocEntry=L.DocEntry
INNER JOIN dbo.OLCT T2 ON L.LocCode = T2.Code
WHERE M.DocDate >= '2006-01-01' and M.DocDate <= '2009-01-01' AND
(t.statype = 1 or t.statype = 7)
GROUP BY
M.CardCode,M.CardName,M.DocEntry, T.stccode
ORDER BY
M.CardName,M.DocEntry
show monthly sales by each state
SELECT T0.State1 AS 'Bill-to State',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 1 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN Amt',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 2 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB Amt',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 3 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR Amt',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 4 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR Amt',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 5 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY Amt',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 6 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN Amt',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 7 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL Amt',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 8 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG Amt',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 9 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP Amt',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 10 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT Amt',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 11 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV Amt',
(SELECT SUM(T1.DocTotal) FROM OINV T1 with (NOLOCK)
INNER JOIN OCRD T2 ON T2.CardCode=T1.CardCode
WHERE MONTH(T1.DOCDATE) = 12 AND T2.State1 = T0.State1
AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'DEC Amt'
FROM dbo.OCRD T0
LEFT JOIN dbo.OINV T1 ON T1.CardCode = T0.CardCode
GROUP BY T0.State1
ORDER BY T0.State1
Many linked tables in one query
SELECT distinct T2.CardCode,
T2.[CardName],
T1.[RefDate],
T0.[BaseRef],
T0.[Debit],
T0.[Credit],
T0.[BalDueDeb], T0.[BalDueCred]
FROM dbo.JDT1 T0
INNER JOIN dbo.OJDT T1 ON T0.TransId = T1.TransId
INNER JOIN dbo.OCRD T2 on T2.CardCode = T0.ShortName
INNER JOIN dbo.OCRG T3 ON T2.GroupCode = T3.GroupCode
INNER JOIN dbo.OINV T4 ON T2.CardCode = T4.CardCode
INNER JOIN dbo.INV1 T5 ON T5.DocEntry = T4.DocEntry
LEFT JOIN dbo.OITM T6 ON T6.ItemCode = T5.ItemCode
LEFT JOIN dbo.OITB T7 ON T7.ItmsGrpCod = T6.ItmsGrpCod
WHERE T0.[RefDate] BETWEEN '[%0]' AND '[%1]'
AND T0.[BalDueDeb] != 0 AND T3.GroupName != 'Vendors'
AND T7.[ItmsGrpNam] = '[%2]
Sales Order with PO
SELECT DISTINCT T0.DocNum as 'Sale Order NO.',
T0.CardName,
T1.Project,
T1.ItemCode,
T1.Quantity,
T1.LineTotal, T0.DocTotal,
T2.CardName as 'BP Name', T2.DocNum as 'PO Number',
T2.DocDate as 'PO Date',
T3.ItemCode as 'Stock Item', T3.Quantity, T3.OpenQty,
T5.DocNum as 'Goods Receipt No',
T5.DocDate as 'GR Date', T4.ItemCode as 'Stock Item',
T4.Quantity,
T4.OpenQty as 'Left to Deliver'
FROM dbo.ORDR T0 INNER JOIN dbo.RDR1 T1 ON T0.DocEntry = T1.DocEntry LEFT JOIN dbo.POR1 T3 ON T1.Docentry = T3.Baseentry and T1.LineNum=T3. Baseline LEFT JOIN dbo.OPOR T2 ON T3.Docentry = T2.Docentry LEFT JOIN dbo.PDN1 T4 ON T4.BaseEntry = T2.DocEntry AND T4.BaseLine = T3.Linenum LEFT JOIN dbo.OPDN T5 ON T5.DocEntry = T4.DocEntry
WHERE T1.Project Like '%[%0]%'
17. Four variables in one query
SELECT T0.DocNum,
T0.DocDate,
T0.CardCode,
T0.CardName,
T0.DocTotal,
T0.DocStatus
FROM dbo.ORDR T0
INNER JOIN dbo.OCRD T1 ON T1.CardCode = T0.CardCode
INNER JOIN dbo.OCRG T2 ON T2.GroupCode = T1.GroupCode
WHERE T0.DocStatus = '[%0]' and
T0.DocDate BETWEEN '[%1]' and '[%2]' AND
T2.GroupName LIKE '[%3]%'
Variables first or last
SELECT DISTINCT
T0.CardCode,
T0.CardName,
T0.DocNum,
T0.DocTotal,
T0.GrosProfit,
T0.DocDate
www.it-ebooks.info
Chapter 4
[ 91 ]
FROM dbo.ADOC T0
WHERE T0.CardCode like 'C%' and
T0.DocDate between [%0] and [%1]
AND T0.CardCode like '%[%2]%'
Balance of production for a month
SELECT SUM(T0.SYSDeb - T0.SYSCred) AS 'Production'
FROM dbo.JDT1 T0
WHERE T0.Account in ('_SYS00000000238','_SYS00000000239', '_ SYS00000000244', '_SYS00000000053') AND
MONTH(T0.RefDate)=Month(Getdate()) AND YEAR(T0. RefDate)=YEAR(Getdate())
How to input a fixed date range
SELECT T0.DocNum as 'SO No.',
T0.DocDate as 'Date',
T0.CardCode as 'Customer Code',
T0.CardName as 'Customer Name',
T0.NumAtCard as 'Ref/PO No.',
T0.DocCur as 'Currency',
(T0.DocTotal - T0.VatSum) as 'SO Amount'
FROM dbo.ORDR T0
WHERE DateDiff(DD, T0.DocDate, GetDate()) > 0 and
DateDiff(DD, T0.DocDate, GetDate()) < 8
Make it simple
SELECT T0.DocNum As 'Invoice number',
T0.DocDate As 'Posting Date',
T0.DocTotal As 'Invoice Total',
T0.GrosProfit As 'Gross Profit',
(T0.GrosProfit/(T0.DocTotal-T0.GrosProfit))*100 As 'Profit %',
T1.CardCode,
T1.CardName,
T0.NumAtCard As 'Customer PO#',
T2.GroupName,
T1.Phone1,
T1.CntctPrsn
FROM dbo.OINV T0
INNER JOIN dbo.OCRD T1 ON T0.CardCode = T1.CardCode
INNER JOIN dbo.OCRG T2 ON T1.GroupCode = T2.GroupCode
WHERE DATEDIFF(DD, T0.DocDate, GetDate()) = 1 OR
(DATEDIFF(DD, T0.DocDate, GetDate()) = 3 AND DATEPART(dw,GetDate()) in (1,2))
Sales order updating alert with drill down
SELECT DISTINCT T1.DocNum,
T0.DocStatus,
T0.DocDate,
T0.DocDueDate,
T0.CardCode,
T0.CardName
FROM dbo.ADOC T0
INNER JOIN dbo.ORDR T1 ON T1.DocNum = T0.DocNum AND T0.ObjType = '17'
WHERE DateDiff(d,T0.UpdateDate,GETDATE()) <= 0
23. Overview of all customers which realized sales greater than 1,000 for the current year. The columns in need are the business partner's name, address, zip code, city, and email address.
SELECT T0.cardname, T0.address, T0.zipcode, T0.city, T0.e_mail
FROM dbo.OCRD T0
WHERE T0.CardCode IN
(SELECT T1.CardCode
FROM dbo.OINV T1 WHERE Year(T1.DocDate) = Year(Getdate())
GROUP BY T1.CardCode
Having SUM(T1.DocTotal) > [%0])
25. top items sold during a certain period of time.
SELECT TOP 5 T1.ItemCode,
MAX(T1.Dscription) as 'Item Description',
SUM(T1.LineTotal) as 'Amount(LC)'
FROM dbo.OINV T0
INNER JOIN dbo.INV1 T1 ON T1.DocENtry = T0.DocENtry
WHERE T0.docdate >= [%0] and T0.docdate <= [%1]
AND T0.doctype = 'I'
GROUP BY T1.ItemCode
ORDER BY SUM(T1.LineTotal) DESC
