Skip to Content
0
Aug 13, 2012 at 05:48 AM

Need help in query

223 Views

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