Daywise stock according to month

[former_member573912]

Can Anyone help me in getting daily stock datewise for paticular warehouse for every month day-wise-stock.jpg

[azizelmir]

Hi qwer G,

Try this query:

Declare @Month as int, @year as int
Declare @Warehouse as Nvarchar(10)
Set @Month=12   ---specify the mont
Set @Year=2018  --- specify the year
set @warehouse='07'   --- specify the warehouse code
SELECT	OITM.ItemCode, 
		OITM.ItemName AS 'Description', 
		OITM.CardCode AS 'Vendor',
		SUM(OITW.OnHand) AS 'On Hand', 
		SUM(OITW.OnOrder) AS 'On Order', 
		SUM(OITW.IsCommited) AS 'Committed',
		(SUM(OITW.OnHand)+SUM(OITW.OnOrder)-SUM(OITW.IsCommited)) AS 'Available', OITM.AvgPrice AS 'Unit Cost',
		(SELECT SUM(OINM.TransValue) FROM OINM WHERE OINM.TransType <> '67' AND YEAR(OINM.DocDate)=(YEAR(getdate())-1) AND OINM.ItemCode = OITM.ItemCode GROUP BY OINM.ItemCode) AS 'Prev. Year',
		(SELECT SUM(OINM.TransValue) FROM OINM WHERE OINM.TransType <> '67' AND Day(OINM.DocDate)='1' AND YEAR(OINM.DocDate)=@year AND Month(OINM.DocDate)=@Month AND OINM.ItemCode = OITM.ItemCode GROUP BY OINM.ItemCode) AS 'Day 1'
		,(SELECT SUM(OINM.TransValue) FROM OINM WHERE OINM.TransType <> '67' AND Day(OINM.DocDate)='2' AND YEAR(OINM.DocDate)=@year AND Month(OINM.DocDate)=@Month AND OINM.ItemCode = OITM.ItemCode GROUP BY OINM.ItemCode) AS 'Day 2'
		,(SELECT SUM(OINM.TransValue) FROM OINM WHERE OINM.TransType <> '67' AND Day(OINM.DocDate)='3' AND YEAR(OINM.DocDate)=@year AND Month(OINM.DocDate)=@Month AND OINM.ItemCode = OITM.ItemCode GROUP BY OINM.ItemCode) AS 'Day 3'
		,(SELECT SUM(OINM.TransValue) FROM OINM WHERE OINM.TransType <> '67' AND Day(OINM.DocDate)='4' AND YEAR(OINM.DocDate)=@year AND Month(OINM.DocDate)=@Month AND OINM.ItemCode = OITM.ItemCode GROUP BY OINM.ItemCode) AS 'Day 4'
		,(SELECT SUM(OINM.TransValue) FROM OINM WHERE OINM.TransType <> '67' AND Day(OINM.DocDate)='5' AND YEAR(OINM.DocDate)=@year AND Month(OINM.DocDate)=@Month AND OINM.ItemCode = OITM.ItemCode GROUP BY OINM.ItemCode) AS 'Day 5'
		,(SELECT SUM(OINM.TransValue) FROM OINM WHERE OINM.TransType <> '67' AND Day(OINM.DocDate)='6' AND YEAR(OINM.DocDate)=@year AND Month(OINM.DocDate)=@Month AND OINM.ItemCode = OITM.ItemCode GROUP BY OINM.ItemCode) AS 'Day 6'
		,(SELECT SUM(OINM.TransValue) FROM OINM WHERE OINM.TransType <> '67' AND Day(OINM.DocDate)='7' AND YEAR(OINM.DocDate)=@year AND Month(OINM.DocDate)=@Month AND OINM.ItemCode = OITM.ItemCode GROUP BY OINM.ItemCode) AS 'Day 7'

		---the same for the remaining days

FROM OITM, OITW
WHERE OITM.ItemCode=OITW.ItemCode and OITW.Whscode=@warehouse 
GROUP BY OITM.ItemCode, OITM.ItemName, OITM.CardCode, OITM.AvgPrice 

Thank you,

Aziz