Hello Experts,
I need a query that will be able to show me opening balance , Completed Items from Production, Cumulative Completed Items from Production, stock transfer from a a warehouse T01 to G04 and cumulative stock transfers to G04.
I have this cose below but I have had problems playing around with it.
Please help.
Declare @FromDate Datetime
Declare @ToDate Datetime
Set @FromDate = (Select min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >={?StartDate})
Set @ToDate = (Select max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <={?EndDate})
Select
a.Itemcode,
max(a.[Dscription]) as 'Description',
sum(a.[OpeningBalance]) as 'OpeningBalance',
min(w1.[CmpltQty] ) as 'No. of J.Cans Filled',
(sum(a.[OpeningBalance]) + min(w1.[CmpltQty] ) ) as 'Cumm.No.Of J.Cans',
sum(a.[OUT]) as 'Issues to Despatch',
sum(X1.Quantity) as 'No.Of J.cans to Dsptch',
( sum(a.[OpeningBalance]) + sum(a.[IN]) - Sum(a.[OUT])) as 'ClosingBalance'
FROM(
Select
N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty)) [OpeningBalance], 0 [IN], 0 [OUT]
From
dbo.OINM N1
Where
N1.DocDate < @FromDate
Group By
N1.ItemCode,N1.Dscription
Union All
select N1.Itemcode, N1.Dscription, 0 [OpeningBalance], sum(N1.inqty) [IN], 0 as OUT
From dbo.OINM N1
Where N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and N1.Inqty >0
Group By N1.ItemCode,N1.Dscription
Union All
select N1.Itemcode, N1.Dscription, 0 [OpeningBalance], 0 [IN], sum(N1.outqty) [OUT]
From dbo.OINM N1
Where N1.DocDate >= @FromDate
and N1.DocDate <=@ToDate
and N1.OutQty > 0
Group By N1.ItemCode,N1.Dscription) a, OITM I1, OWOR W1, WTR1 X1
WHERE a.ItemCode=I1.ItemCode AND a.ItemCode = W1.ItemCode
AND W1.[PostDate] >={?StartDate} AND W1.[PostDate] <={?EndDate}
AND X1.WhsCode = 'D01' AND X1.DocDate >= {?StartDate}
AND X1.DocDate <={?EndDate}
Group By a.Itemcode, X1.Quantity
Order By a.Itemcode