Hello Experts,
I've two views.
One view is pulling purchase related data with respective batch information.
Second view is pulling sale related information with respective batch information.
With the help of this I'm generating batchwise profit report.
In order to achieve this i've joined two views on itemcode and batchnum.
since more than 8 - 9 months it is running well. But now it is giving SQL timeout error.
Please have a look at it and suggest me the correction.
First View
SELECT dbo.PDN1.ItemCode, dbo.PDN1.Dscription, AVG(dbo.PDN1.Price) AS SP, dbo.OPDN.DocDate, AVG(dbo.IPF1.PriceAtWH) AS LP, dbo.IBT1.BatchNum, dbo.OIPF.DocNum,
SUM(dbo.IBT1.Quantity) AS GRN_QTY, I0.QryGroup64
FROM dbo.IBT1 INNER JOIN
dbo.OIBT ON dbo.IBT1.ItemCode = dbo.OIBT.ItemCode AND dbo.IBT1.BatchNum = dbo.OIBT.BatchNum AND dbo.IBT1.WhsCode = dbo.OIBT.WhsCode INNER JOIN
dbo.IPF1 INNER JOIN
dbo.OIPF ON dbo.IPF1.DocEntry = dbo.OIPF.DocEntry INNER JOIN
dbo.OPDN INNER JOIN
dbo.PDN1 ON dbo.OPDN.DocEntry = dbo.PDN1.DocEntry ON dbo.IPF1.BaseType = dbo.OPDN.ObjType AND dbo.IPF1.BaseEntry = dbo.OPDN.DocEntry AND
dbo.IPF1.LineNum = dbo.PDN1.LineNum ON dbo.IBT1.BaseType = dbo.OPDN.ObjType AND dbo.IBT1.BaseEntry = dbo.OPDN.DocEntry AND
dbo.IBT1.BaseNum = dbo.OPDN.DocNum AND dbo.IBT1.BaseLinNum = dbo.PDN1.LineNum INNER JOIN
dbo.OITM AS I0 ON I0.ItemCode = dbo.PDN1.ItemCode AND I0.QryGroup64 = 'Y'
GROUP BY dbo.PDN1.ItemCode, dbo.PDN1.Dscription, dbo.OPDN.DocDate, dbo.IBT1.BatchNum, dbo.OIPF.DocNum, dbo.PDN1.TargetType, I0.QryGroup64
HAVING (dbo.PDN1.TargetType <> 21)
Second View
SELECT T0.DocEntry, T0.CardName AS [Customer Name], N.SeriesName AS [Document Series], T0.DocNum AS [Document Number], T1.ItemCode AS [Item Code],
T1.Dscription AS [Item Description], T1.Quantity AS InvQty, T1.Price AS [Rate Per Unit], IB.Quantity, IB.BatchNum, T0.DocDate
FROM dbo.OINV AS T0 INNER JOIN
dbo.INV1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN
dbo.DLN1 AS D1 ON T1.BaseEntry = D1.DocEntry AND T1.BaseLine = D1.LineNum LEFT OUTER JOIN
dbo.ODLN AS OD ON D1.DocEntry = OD.DocEntry INNER JOIN
dbo.NNM1 AS N ON T0.Series = N.Series INNER JOIN
dbo.NNM1 AS N1 ON OD.Series = N1.Series INNER JOIN
(SELECT ItemCode, BatchNum, WhsCode, LineNum, ItemName, BaseType, BaseEntry, BaseNum, BaseLinNum, DocDate, Quantity, CardCode, CardName,
Direction, CreateDate, BsDocType, BsDocEntry, BsDocLine, DataSource, UserSign
FROM dbo.IBT1
WHERE (BaseType = 15)) AS IB ON IB.ItemCode = D1.ItemCode AND IB.BaseEntry = D1.DocEntry AND IB.BaseLinNum = D1.LineNum AND
IB.WhsCode = D1.WhsCode INNER JOIN
dbo.OIBT AS BT ON BT.ItemCode = IB.ItemCode AND BT.BatchNum = IB.BatchNum AND BT.WhsCode = IB.WhsCode INNER JOIN
(SELECT ItemCode, QryGroup64
FROM dbo.OITM
WHERE (QryGroup64 = 'Y')) AS I0 ON I0.ItemCode = T1.ItemCode
WHERE (T0.DocType = 'I') AND (T0.DocDate >= '01/APR/2012') AND (T1.TargetType <> 14) AND (D1.TargetType <> 16) AND (T0.DocDate <= '31/jul/2012')
Final View
SELECT dbo.SALE.[Customer Name], dbo.SALE.[Item Code], dbo.SALE.InvQty, dbo.SALE.[Rate Per Unit], dbo.GRN_BTCH_LC.BatchNum,
dbo.SALE.BatchNum AS Expr1, dbo.GRN_BTCH_LC.GRN_QTY
FROM dbo.GRN_BTCH_LC INNER JOIN
dbo.SALE ON dbo.GRN_BTCH_LC.ItemCode = dbo.SALE.[Item Code] AND dbo.GRN_BTCH_LC.BatchNum = dbo.SALE.BatchNum
Please guide me.
Thanking You
Malhaar