Delivery and Return Query

[former_member264311]

Hi give me an example of query that will show ItemDescription, Total Quantity Deliver-Total Quantity Return. of particular date and warehouse.

Thanks.

[Former Member · Accepted solution]

Hi Romel,

SELECT T0.[DocDate], T0.[ItemCode], T0.[Dscription], T0.[OutQty], T0.[InQty], T0.[Warehouse] FROM OINM T0 WHERE T0.[TransType]  IN ('15','16','20','21','59','60')

Thanks,

Harshal

[former_member264311 · Accepted solution]

How Can I Select 2 warehouse for example

SELECT T0.[DocDate], T0.[ItemCode], T0.[Dscription], T0.[OutQty], T0.[InQty], T0.[Warehouse] FROM OINM T0 WHERE T0.[TransType]  IN ('15','16','20','21','59','60') and Warehouse ='[%0]' and Warehouse '[%1]'

Can I do that??

thanks.

[frank_wang6 · Accepted solution]

You can use Warehouse IN ('[%0]', '[%1]')

[former_member264311 · Accepted solution]

In this code only 1 warehouse appear

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @Whse nvarchar(10)

select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]'

select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]'

select @Whse = S2.Warehouse from dbo.OINM S2 Where S2.Warehouse IN ('[%2]','[%3]')

Select @Whse as 'Warehouse', a.Itemcode, max(a.Dscription) as ItemName,

sum(a.OpeningBalance) as OpeningBalance,(sum(a.IN2)-sum(a.OUT2)) as Production, (sum(a.OUT)-sum(a.INq)) as Withdrawal,

((sum(a.OpeningBalance) + (sum(a.IN2)-sum(a.OUT2))) - (sum(a.OUT)-sum(a.INq))) as Closing ,

(Select i.InvntryUom from OITM i where i.ItemCode=a.Itemcode) as UOM

from( Select N1.Warehouse, N1.Itemcode, N1.Dscription, (sum(N1.inqty)-sum(n1.outqty))as OpeningBalance, 0 as INq, 0 as OUT, 0 as IN2, 0 as OUT2
From dbo.OINM N1
Where N1.DocDate < @FromDate
and N1.Warehouse = @Whse
Group By N1.Warehouse,N1.ItemCode,N1.Dscription Union All

select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,
sum(N1.inqty) , 0 as OUT, 0 as IN2, 0 as OUT2 
From dbo.OINM N1
Where N1.DocDate >= @FromDate
and N1.DocDate <= @ToDate
and N1.Inqty >0
and N1.Warehouse = @Whse
and N1.TransType='16'
Group By N1.Warehouse,N1.ItemCode,N1.Dscription Union All

select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance, 0 , sum(N1.outqty) as OUT, 0 as IN2, 0 as OUT2
From dbo.OINM N1
Where N1.DocDate >= @FromDate
and N1.DocDate <=@ToDate
and N1.OutQty > 0
and N1.Warehouse = @Whse
and N1.TransType='15'
Group By N1.Warehouse,N1.ItemCode,N1.Dscription Union All

select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,
0 , 0 as OUT, sum(N1.inqty) as IN2, 0 as OUT2 
From dbo.OINM N1
Where N1.DocDate >= @FromDate
and N1.DocDate <= @ToDate
and N1.Inqty >0
and N1.Warehouse = @Whse
and N1.TransType='59'
Group By N1.Warehouse,N1.ItemCode,N1.Dscription Union All

select N1.Warehouse, N1.Itemcode, N1.Dscription, 0 as OpeningBalance,
0 , 0 as OUT, 0 as IN2, sum(N1.outqty) as OUT2 
From dbo.OINM N1
Where N1.DocDate >= @FromDate
and N1.DocDate <= @ToDate
and N1.OutQty >0
and N1.Warehouse = @Whse
and N1.TransType='60'
Group By N1.Warehouse,N1.ItemCode,N1.Dscription) a, dbo.OITM I1

where a.ItemCode=I1.ItemCode

Group By I1.ItmsGrpCod, a.Itemcode Having sum(a.OpeningBalance)>0
Order By a.Itemcode

[frank_wang6]

SELECT * FROM OINM WHERE TransType IN (16, 14)

Try above query, it will give some hint to generate what u want.

Frank

[former_member264311]

Thanks for the reply Frank Wang. Anyways what is TransType? where can I find that number 14 and 16? or what sa meaning of that? thanks.

[frank_wang6]

14 and 16 is delivery and return.

Frank

[frank_wang6]

sorry, it should be 15 and 16.

[former_member264311]

How about good issue and good receipt?