cancel
Showing results for 
Search instead for 
Did you mean: 

Item Stock Report

Former Member
0 Kudos

Hi All ,

I have posted thread "Item Stock Report "

and got ans with following query

Code {SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<'[%0]' and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<'[%0]' and O1.transtype in (21,19,60,15,67,-2,13)),0)) as 'Opening Stock', (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.transtype in (-2)),0)) as 'OB Quantity', isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.inqty>0 and O1.transtype in (20,18)),0) as 'Purchase Quantity', isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.outqty>0 and O1.transtype in (21,19)),0) as 'Purchase Return Quantity', isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.inqty>0 and O1.transtype in (16,14)),0) as 'Sale Return Quantity', isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.outqty>0 and O1.transtype in (13,15)),0) as 'sale Quatity', (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<='[%1]' and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<='[%1]' and O1.transtype in (21,19,60,15,67,-2,13)),0)) as 'Closing Stock' FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode INNER JOIN OLCT C1 ON W1.Location=C1.Code where ((B1.ItmsGrpNam>='[%2]' and B1.ItmsGrpNam<='[%3]') or ('[%2]'='' and '[%3]'='')) and ((W1.Whscode>='[%4]' and W1.Whscode<='[%5]') or ('[%4]'='' and '[%5]'='')) and ((C1.Location>='[%6]' and C1.Location<='[%7]') or ('[%6]'='' and '[%7]'='')) and T0.itemcode >='[%8]' and T0.itemcode <='[%9]' Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location }

Now after running this query I am getting values correctly.

When we run Inventory status report we get option "hide item without stock "

So in the same query can we put that condition .

What i means is If

ItemOpening Stock = Item Purchase Qty = Item Purchase Return Qty = Item Sales Qty = Item Sales Return Quantity= Closing Stock = 0

Then do not display those Item in report

I am expecting reply from Suraj and Gordan

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Try this !

select * from (
SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<'[%0]' and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<'[%0]' and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock],
 (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.transtype in (-2)),0)) as [OB Quantity], 
isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity],
 isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>='[%0]' and O1.docdate<='[%1]' and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<='[%1]' and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<='[%1]' and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock] FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode INNER JOIN OLCT C1 ON W1.Location=C1.Code where ((B1.ItmsGrpNam>='[%2]' and B1.ItmsGrpNam<='[%3]') or ('[%2]'='' and '[%3]'='')) and ((W1.Whscode>='[%4]' and W1.Whscode<='[%5]') or ('[%4]'='' and '[%5]'='')) and ((C1.Location>='[%6]' and C1.Location<='[%7]') or ('[%6]'='' and '[%7]'='')) and T0.itemcode >='[%8]' and T0.itemcode <='[%9]' Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a
where
(a.[Opening Stock]+a.[OB Quantity]+a.[Purchase Quantity]+a.[Purchase Return Quantity]+a.[Sale Return Quantity]+a.[sale Quatity]) !=0

Try this ! !

Former Member
0 Kudos

Hi!

Satish the above query is not working because of the outer query, i will modify this and put As soon.

Pl Gv me some time !

Former Member
0 Kudos

hi suraj,

I have juist check that its not working

waiting for your reply

Former Member
0 Kudos

Hi!

Sorry for delay !

Check the Below Query !

Former Member
0 Kudos

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @ItmsGrpNam Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @ItmsGrpNam nvarchar(50)

Declare @Whscode nvarchar(8)

Declare @Location nvarchar(50)

Declare @itemcode nvarchar(50)

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

set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]')

set @ItmsGrpNam = (select max(S2.ItmsGrpNam) from OITB S2 where S2.ItmsGrpNam='[%2]')

set @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%3]')

set @Location = (select max(s4.Location) from OLCT s4 where s4.Location ='[%4]')

set @itemcode = (Select max(S5.ItemCode) from OINM S5 Where S5.ItemCode ='[%5]')

select * from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group',

W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and

O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select

sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and

O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select

sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and

O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0))

from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and

O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1

where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and

O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1

where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and

O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from

OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate

and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], isnull((Select sum(isnull(outqty,0)) from

OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate

and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1

where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in

(59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0))

from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in

(21,19,60,15,67,-2,13)),0)) as [Closing Stock]

FROM OINM T0 INNER JOIN

OITM T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod

INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode

INNER JOIN OLCT C1 ON W1.Location=C1.Code

where (B1.ItmsGrpNam>=@ItmsGrpNam and B1.ItmsGrpNam<=@ItmsGrpNam) and (W1.Whscode>=@Whscode and

W1.Whscode<=@Whscode) and (C1.Location>=@Location and C1.Location<=@Location) and T0.itemcode >=@itemcode and

T0.itemcode <=@itemcode

Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase

Quantity]a.[Purchase Return Quantity]a.[Sale Return Quantity]+a.[sale Quatity]) !=0

Former Member
0 Kudos

hi suraj,

Dont be sorry!!!!!!!!! , back to the point

Result after running this query ,

some of Report selection parameters cannot be defined in range criteria , means i can select only one item group , one warehouse and one item there is no range selection in this, also after running this query MSSQL error is coming.

Error Defination. "Incorrect Syntax Near Keyword declare , the variable name @ from date has already been defined variable name should be unique for query batch or stored"

Former Member
0 Kudos

K.

I will Modify tht!

Former Member
0 Kudos

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @ItmsGrpNam nvarchar(50)

Declare @ItmsGrpNam1 nvarchar(50)

Declare @Whscode nvarchar(8)

Declare @Whscode1 nvarchar(8)

Declare @Location nvarchar(50)

Declare @Location1 nvarchar(50)

Declare @itemcode nvarchar(50)

Declare @itemcode1 nvarchar(50)

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

set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]')

set @ItmsGrpNam = (select max(S2.ItmsGrpNam) from OITB S2 where S2.ItmsGrpNam ='[%2]')

set @ItmsGrpNam1 = (select max(S22.ItmsGrpNam) from OITB S22 where S22.ItmsGrpNam ='[%3]')

set @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%4]')

set @Whscode1 = (Select max(s33.Whscode) from OWHS s33 where s33.whscode = '[%5]')

set @Location = (select max(s4.Location) from OLCT s4 where s4.Location ='[%6]')

set @Location1 = (select max(s44.Location) from OLCT s44 where s44.Location ='[%7]')

set @itemcode = (Select max(S5.ItemCode) from OINM S5 Where S5.ItemCode ='[%8]')

set @itemcode1 = (Select max(S55.ItemCode) from OINM S55 Where S55.ItemCode ='[%9]')

select * from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0))

from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock]

FROM OINM T0 INNER JOIN

OITM T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod

INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode

INNER JOIN OLCT C1 ON W1.Location=C1.Code

where (B1.ItmsGrpNam>=@ItmsGrpNam and B1.ItmsGrpNam<=@ItmsGrpNam1) and (W1.Whscode>=@Whscode and W1.Whscode<=@Whscode1) and (C1.Location>=@Location and C1.Location<=@Location1) and T0.itemcode >=@itemcode and T0.itemcode <=@itemcode1

Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+

a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0

Answers (3)

Answers (3)

Former Member
0 Kudos

Got the result

Former Member
0 Kudos

hi

In the same query.

Instead of Selecting posting period from this date to other date

can we provide directly month/week wise selection for generation of that query

Former Member
0 Kudos

Hi!

It is possible, but it need UDT,UDF's.

It is quite difficult.

On the above report itself, user can directly type the Date on the Text Box instead selecting from the list.

i mean type it

From Date - 01/07/09

To Date - 31/07/09

Query is wrote in such away it will pick only the existing from and todate from the table within the specified range.

Former Member
0 Kudos

hi Suraj

Pls check the query {Declare @FromDate Datetime Declare @ToDate Datetime Declare @ItmsGrpNam nvarchar(50) Declare @ItmsGrpNam1 nvarchar(50) Declare @Whscode nvarchar(8) Declare @Whscode1 nvarchar(8) Declare @Location nvarchar(50) Declare @Location1 nvarchar(50) Declare @itemcode nvarchar(50) Declare @itemcode1 nvarchar(50) set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]') set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]') set @ItmsGrpNam = (select max(S2.ItmsGrpNam) from OITB S2 where S2.ItmsGrpNam ='[%2]') set @ItmsGrpNam1 = (select max(S22.ItmsGrpNam) from OITB S22 where S22.ItmsGrpNam ='[%3]') set @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%4]') set @Whscode1 = (Select max(s33.Whscode) from OWHS s33 where s33.whscode = '[%5]') set @Location = (select max(s4.Location) from OLCT s4 where s4.Location ='[%6]') set @Location1 = (select max(s44.Location) from OLCT s44 where s44.Location ='[%7]') set @itemcode = (Select max(S5.ItemCode) from OINM S5 Where S5.ItemCode ='[%8]') set @itemcode1 = (Select max(S55.ItemCode) from OINM S55 Where S55.ItemCode ='[%9]') select * from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock] FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode INNER JOIN OLCT C1 ON W1.Location=C1.Code where (B1.ItmsGrpNam>=@ItmsGrpNam and B1.ItmsGrpNam<=@ItmsGrpNam1) and (W1.Whscode>=@Whscode and W1.Whscode<=@Whscode1) and (C1.Location>=@Location and C1.Location<=@Location1) and T0.itemcode >=@itemcode and T0.itemcode <=@itemcode1 Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0}

                    • There are lot of selection criteria which user has to select while generating the report.For the same result I need two queries*** 1) With only Selection of date range and warehouse . ****2) With only selection of date range only **************************Pls do not show item without stock that is keep this (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0) condition as it is

Former Member
0 Kudos

Hi!

I will do it.

Former Member
0 Kudos

Hi!

Below query is with selection

1. Date

2. Warehouse

Former Member
0 Kudos

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @Whscode nvarchar(8)

Declare @Whscode1 nvarchar(8)

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

set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]')

set @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%4]')

set @Whscode1 = (Select max(s33.Whscode) from OWHS s33 where s33.whscode = '[%5]')

select * from

( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location,

(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock],

(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity],

isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity],

isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity],

isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock]

FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod

INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode

INNER JOIN OLCT C1 ON W1.Location=C1.Code

where

(W1.Whscode>=@Whscode and W1.Whscode<=@Whscode1)

Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0

Former Member
0 Kudos

Hi!

Below query with selection

1. Date

Former Member
0 Kudos

Declare @FromDate Datetime

Declare @ToDate Datetime

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

set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]')

select * from

( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location,

(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock],

(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity],

isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity],

isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity],

isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock]

FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod

INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode

INNER JOIN OLCT C1 ON W1.Location=C1.Code

Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0

Former Member
0 Kudos

hi suraj,

I have posted a new thread can you check

whether you can designed some query for that

thread name is "Report Linking Delivery , Receipts , Purchase Return & Sales Return entry"

Former Member
0 Kudos

hi Suraj,

For the same queries can you add one more column in end in which holds the value

Reorder Value = (Closing Stock- Opening Stock).

Add One More Field (Column ) in the last where do the subtraction of opening balance and closing stock the field name will be Reorder Value.

Former Member
0 Kudos

Fine, will do.

Former Member
0 Kudos
Declare @FromDate Datetime 
Declare @ToDate Datetime 
set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]') 
set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]') 
select *,a.[Closing Stock]-a.[Opening Stock] from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', 
min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, 
(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], 
(isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity], 
isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], 
isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], 
isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], 
isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock] 
FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode INNER JOIN OLCT C1 ON W1.Location=C1.Code Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a 
where (a.[Opening Stock]+a.[OB Quantity]+a.[Purchase Quantity]+ a.[Purchase Return Quantity]+a.[Sale Return Quantity]+a.[sale Quatity]) !=0
Former Member
0 Kudos

Hi Suraj ,

Thankx you have not Named the last coloumn of this report Last Column(which is Opening Stock- Closing Stock ) should be name as Reorder Qty.

Also if you do remember about our report generation criteria

i) Date range - Which can be met wtih the above report

ii) Date range with warehouse selection (pls post query for this with Last Column(which is Opening Stock- Closing Stock ))

iii) Details selection from Item group to warehouse to date range to item level.

i have got one query which satisfy first criteria and pls post queries in seperate reply

also do not forgot two condition .

i) hiding item with no stock i.e opening stock = purchaseqty= sales delivery stock= closingstock=0(zero)

ii)last column which is name as Reorder Value which is subtraction of closing stock - opening stock

Former Member
0 Kudos

Hi!

Below query is with date selection alone

Former Member
0 Kudos

Declare @FromDate Datetime Declare @ToDate Datetime set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]') set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]') select *,a.[Closing Stock]-a.[Opening Stock] as [Reorder Qty] from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock] FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode INNER JOIN OLCT C1 ON W1.Location=C1.Code Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0

Former Member
0 Kudos

Hi!

Below is with Date and warehouse selection

Former Member
0 Kudos

Declare @FromDate Datetime Declare @ToDate Datetime Declare @Whscode nvarchar(8) Declare @Whscode1 nvarchar(8) set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]') set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]') set @Whscode = (Select max(s3.Whscode) from OWHS s3 where s3.whscode = '[%4]') set @Whscode1 = (Select max(s33.Whscode) from OWHS s33 where s33.whscode = '[%5]') select *,a.[Opening Stock]-a.[Closing Stock] as [Reorder Qty] from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quatity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock] FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode INNER JOIN OLCT C1 ON W1.Location=C1.Code where (W1.Whscode>=@Whscode and W1.Whscode<=@Whscode1) Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale Quatity]) !=0

former_member196081
Active Contributor
0 Kudos

Hi Thanga,

Realy It is the rarest query.I were searching this type query since a long time.My client wants report in below formet at specific date ....

itemgroup item name SalesOrderQuantity DeliverdQty OpeningStock MinInventryLevel

ActualProduction

do this for me buddy...

Thanks

Deepak Tyagi

Former Member
0 Kudos

hi,

This query is not fetching data from some warehouse i.e its excluding some warehouse.

Also does we consider inventory transfer in this query so if we then we need to add one more column called inventory transfer so that we can tally the closing stock quantity

or else sale quantity , sale return quantity and purchase return quantity and purchase quantity should be shown warehouse wise

For the same query if we do want to know what is consumption from that warehouse

how do we get it also can u add one field which is from OITM.Minlevel to the following query with consumption quantity

Code {

Declare @FromDate Datetime Declare @ToDate Datetime set @FromDate = (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]') set @ToDate = (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]') select *,([Opening Stock]+ [OB Quantity] +[Purchase Quantity] + [Sale Return Quantity] - [Closing Stock]) as[Consumption Quantity] from ( SELECT T0.itemcode, min(T0.Dscription) as 'Item Description', min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (59,20,16,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<@FromDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Opening Stock], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)- isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.transtype in (-2)),0)) as [OB Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (20,18)),0) as [Purchase Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (21,19)),0) as [Purchase Return Quantity], isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.inqty>0 and O1.transtype in (16,14)),0) as [Sale Return Quantity], isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0 and O1.transtype in (13,15)),0) as [sale Quantity], (isnull((Select sum(isnull(inqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (59,20,16,14,19,67,-2)),0) - isnull((Select sum(isnull(outqty,0)) from OINM O1 where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode and O1.docdate<=@ToDate and O1.transtype in (21,19,60,15,67,-2,13)),0)) as [Closing Stock] FROM OINM T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode INNER JOIN OLCT C1 ON W1.Location=C1.Code Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location) a where (a.[Opening Stock]a.[OB Quantity]a.[Purchase Quantity]+ a.[Purchase Return Quantity]a.[Sale Return Quantity]a.[sale quantity]) !=0 and whscode <> 'ACER'}

Former Member
0 Kudos

Hi Suraj,

Its Running successfully now

Former Member
0 Kudos

Please ensure only 0 values are hided ?