Skip to Content
0
Former Member
Jan 13, 2011 at 03:59 PM

Production Daily Report

35 Views

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