The initial request is to change a query that only displays the items which have transactions on that particular given date range.
Declare @FromDate Datetime:
Declare @ToDate Datetime Declare @Group nvarchar(10) Declare @Whse nvarchar(10)
Set @FromDate = (Select min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]')
Set @ToDate = (Select max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]')
Set @Group = (Select Max(s2.ItmsGrpCod) from dbo.OITB S2 Where S2.ItmsGrpNam = '[%2]')
Set @Whse = (Select Max(s3.Warehouse) from dbo.OINM S3 Where S3.Warehouse = '[%3]')
SELECT
@Whse as 'Warehouse', a.Itemcode,
max(a.Dscription),
sum(a.Opening Balance) as Opening Balance, sum(a.IN) as IN,
sum(a.OUT) as OUT,max(a.Price) as 'Price',
((sum(a.Opening Balance) + sum(a.IN)) - Sum(a.OUT)) as Closing from(
Select
N1.Warehouse, N1.Itemcode,
N1.Dscription,N1.Price,
(sum(N1.inqty)-sum(n1.outqty)) as Opening Balance, 0 as IN,
0 as OUT
From dbo.OINM N1 Where
N1.DocDate < @FromDate and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription,N1.Price Union All select N1.Warehouse, N1.Itemcode,
From dbo.OINM N1 Where
N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and N1.Inqty >0
and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription,N1.price Union All select N1.Warehouse, N1.Itemcode, N1.Dscription,N1.price, 0 as Opening Balance, 0 as IN, sum(N1.outqty) as OUT From dbo.OINM N1 Where
N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and N1.OutQty > 0
and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription,N1.price) a, dbo.OITM I1 where a.ItemCode=I1.ItemCode and I1.ItmsGrpCod = @Group Group By a.Itemcode
Having sum(a.Opening Balance) + sum(a.IN) + sum(a.OUT) > 0 Order By a.Itemcode
The solution is:
Declare @FromDate Datetime Declare @ToDate Datetime Declare @Group nvarchar(10) Declare @Whse nvarchar(10)
Set @FromDate = (Select min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]')
Set @ToDate = (Select max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]')
SELECT @Whse as 'Warehouse', a.Itemcode,
a.max(a.Dscription) as 'Item Description', sum(a.[Opening Balance]) as [Opening Balance], sum(a.[IN]) as [IN],
sum(a.OUT) as OUT,
max(a.Price) as 'Price',
((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing FROM dbo.OITM I1
Left JOIN (Select N1.Warehouse, N1.Itemcode, N1.Dscription,N1.Price, (sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],
0 as [IN], 0 as OUT
From dbo.OINM N1
WHERE N1.DocDate < @FromDate and N1.Warehouse = @Whse Group By N1.Warehouse,N1.ItemCode,N1.Dscription,N1.Price Union All SELECT N1.Warehouse, N1.Itemcode, N1.Dscription, N1.price, 0 as [Opening Balance], sum(N1.inqty) as [IN], 0 as OUT From dbo.OINM N1
Where N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and N1.Inqty >0 and N1.Warehouse = @Whse
Group By N1.Warehouse,N1.ItemCode,N1.Dscription,N1.price Union All
SELECT N1.Warehouse, N1.Itemcode,
sum(N1.outqty) as OUT From dbo.OINM N1
Where N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and N1.OutQty > 0 and N1.Warehouse = @Whse
Group By N1.Warehouse,N1.ItemCode,N1.Dscription,N1.price) a ON a.ItemCode=I1.ItemCode
WHERE I1.ItmsGrpCod = @Group Group By a.Itemcode
Order By a.Itemcode
This is one of the longest queries in this book. In general, I am reluctant to create any long queries like this one. That is because the longer the query, the more system resources might be used. It also creates maintenance difficulty for other users. If you compare the solution query to the original one, you will find the difference is only a little. This proves that such a long query is indeed problematic.
If you find this query works for you, good. If not, that is no problem either. This query may only work under 2007 version or below. OINM is changed from a table to a view so the parameter may not work for version 8.8 or higher.
To put this query here is to show what kind of queries should be avoided.
Query for financial transactions
Under this category, all banking related issues are included. There are fewer financial transactions related query requests than other queries. This is probably because:
• System provides more financial reports than other reports
• The need for such reports is less
• It is too difficult to produce a proper report
No matter how many queries can be built, we should still go through some of the queries so that we can find more information related to business operations.
Case 4-F1: Top five customers
Sales ranking is discussed in the previous section. However, there are many questions with some different requests. One example is top N customers during any period.
Since this query only deals with the Journal Entry table, I have put it under the financial transactions section. Here is the query list:
SELECT TOP 5 T0.ShortName 'Customer', Max(T2.CardName) 'Customer Name',
SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) as "Amount(LC)" FROM dbo.JDT1 T0
INNER JOIN dbo.OJDT T1 ON T1.TransID = T0.TransID and T0.TransType IN ('13','14')
INNER JOIN dbo.OCRD T2 ON T2.CardCode = T0.ShortName WHERE T1.RefDate >= [%0] and T1.RefDate <= [%1] GROUP BY T0.ShortName
ORDER BY SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) DESC
This query has just looked up Journal Entry table OJDT and JDT1. It is only for displaying customer names to link with the Business Partner table OCRD.
Max(T2.CardName) is used for convenience to omit the column from grouping.
It will be very useful when your query includes some multiple value documents such as OINV or ORIN. It can just return one customer name in case you have any different names in the documents for the same customer.
T0.TransType IN ('13','14') limits query results to A/R Invoice and A/R Credit
Memo transactions only.
This is similar to the top five items query in the beginning. Something discussed there will probably also apply here.
Case 4-F2: Incoming payment
• Payment No
• Amount Paid
• Payment Date
• Invoice No (that was reconciled against the payment)
• Invoice Date
The user has no idea which tables to query to get the payment and reconciliation information.
Here is the solution:
SELECT T0.[CardCode] AS 'Customer Code', T0.[CardName] AS 'Customer Name',
T0.[DocNum] AS 'Payment Number', T1.[SumApplied] AS 'Paid to Invoice', T0.[DocDate] AS 'Payment Date', T2.[DocNum] AS 'Invoice Number', T2.[DocDate] AS 'Invoice Date' FROM [dbo].[ORCT] T0
INNER JOIN [dbo].[RCT2] T1 ON T0.[DocNum] = T1.[DocNum] INNER JOIN [dbo].[OINV] T2 ON T1.[DocEntry] = T2.[DocEntry] WHERE T0.[DocDate] >= [%0] AND T0.[DocDate] <= [%1]
This query is the first one in which I have used query generator for the solution, so that you can see all brackets.
Incoming payment table links are different with marketing document links. They use DocNum column for the link. I believe this is for the convenience of the table structure. DocEntry columns are used by marketing documents. So you can find that the link between RCT2 and OINV is by the DocEntry column.