Requirement of "No. of days" in Below Reorder alert query

[anand_singh]

Hi,

I have required "No. of days" field in below alert query which will Show, No. of days from which items are below reorder qty. Days are going to increasing until it's not going above to reorder qty. Items are managed by warehouse wise.

SELECT T0.[ItemCode], T1.[ItemName], T0.[WhsCode], T0.[OnHand], T0.[MinOrder] 
FROM OITW T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode 
WHERE T0.[OnHand] < T0.[MinOrder] AND T0.[WhsCode] = '01'

Appreciate if anyone could help. Thanks in advance.

Regards,

Anand Singh

[Abdul]

SELECT dbo.OINM.CardName, dbo.OITM.ItemCode, dbo.OITM.ItemName, dbo.OINM.TransType, dbo.oinm.TransNum, dbo.OITM.InvntryUom, dbo.OINM.CreateDate, dbo.OINM.InQty, dbo.OINM.OutQty, dbo.OINM.BASE_REF, dbo.OINM.DocDate, dbo.OINM.DocTime, dbo.NNM1.SeriesName, dbo.OINM.CalcPrice, dbo.OINM.TransValue, dbo.OINM.Price,dbo.oinm.OcrCode, dbo.oinm.OcrCode2, dbo.oinm.OcrCode3, dbo.oinm.OcrCode4, dbo.oinm.OcrCode5, dbo.oinm.Warehouse, ISNULL ((SELECT SUM(InQty - OutQty) AS SUM FROM dbo.OINM AS t0 WHERE (DocDate < {?F-Date}) AND (ItemCode = dbo.OINM.ItemCode)), 0) AS ob ,ISNULL ((SELECT avg(price) FROM dbo.Oinm AS t0 where (DocDate <{?F-Date}) AND (t0.ItemCode = dbo.OINM.ItemCode)), 0) AS obPrice FROM dbo.NNM1 RIGHT OUTER JOIN dbo.OWTR ON dbo.NNM1.Series = dbo.OWTR.Series RIGHT OUTER JOIN dbo.OINM INNER JOIN dbo.OITM ON dbo.OINM.ItemCode = dbo.OITM.ItemCode ON dbo.OWTR.DocNum = dbo.OINM.BASE_REF WHERE (dbo.OINM.DocDate BETWEEN {?F-Date} AND {?T-Date}) AND (dbo.OITM.ItemCode BETWEEN '{?A@select * from OITM}' AND '{?B@select * from OITM}')

[Abdul]

You have to calculate quantity in your query with date of transaction then you can calculate no of days by default there is no such field in this table. For inventory detailed transactions table is OIVL and IVL1

[anand_singh]

Dear Mr. Abdul,

I have already check these table including OINM also, but not get satisfactory results. if you can provide me code, it will more helpful for me.

Regards,

Anand Singh

[former_member588507]

Hi Anand,

By using DateDiff function you can calculate no. of days for Ex. DATEDIFF(Day,Docdate,GETDATE()) as 'No. of Days'

Regards,

Satish

[anand_singh]

Hi Satish,

Thanks for your response.

But if you check the mentioned tables which i selected, having no. such field of Docdate.

Regards,

Anand Singh