on 02-19-2019 8:00 PM
Can Anyone help me in getting daily stock datewise for paticular warehouse for every month day-wise-stock.jpg
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
11 | |
10 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.