Inventory report

[Former Member]

I have bleow report i want to add two column 1st by wharehouse ,2nd is total value

i

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @Group nvarchar(10)

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

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

Set @Group = (Select Max(s2.ItmsGrpCod) from dbo.OITB S2 Where S2.ItmsGrpNam = '[%2]')

Select

a.Itemcode,

max(a.Dscription),

sum(a.[Opening Balance]) as [Opening Balance],

sum(a.[IN]) as [IN],

sum(a.OUT) as OUT,

((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing

from(

Select

N1.Itemcode,

N1.Dscription,

(sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],

0 as [IN],

0 as OUT

From dbo.OINM N1

Where

N1.DocDate < @FromDate

Group By

N1.ItemCode,N1.Dscription

Union All

select

N1.Itemcode,

N1.Dscription,

0 as [Opening Balance],

sum(N1.inqty) as [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 as [Opening Balance],

0 as [IN],

sum(N1.outqty) as OUT

From dbo.OINM N1

Where

N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and

N1.OutQty > 0

Group By

N1.ItemCode,N1.Dscription) a, dbo.OITM I1

where

a.ItemCode=I1.ItemCode and

I1.ItmsGrpCod = @Group

Group By

a.Itemcode

Having sum(a.[Opening Balance]) + sum(a.[IN]) + sum(a.OUT) > 0

Order By a.Itemcode

[Former Member]

To add a warehouse column, you may try this:

Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Group nvarchar(10)
 
Set @FromDate = (Select min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]')
Set @ToDate = (Select max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]')
Set @Group = (Select Max(s2.ItmsGrpCod) from dbo.OITB S2 Where S2.ItmsGrpNam = '[%2]')
 
Select 
a.Itemcode, Max(I1.ItemName) as 'Item Description', MAX(a.Warehouse) as 'Warehouse',
sum(a.[Opening Balance]) as [Opening Balance],
sum(a.[IN]) as [IN],
sum(a.OUT) as OUT,
((sum(a.[Opening Balance]) + sum(a.[IN])) - Sum(a.OUT)) as Closing
from(
Select
N1.Itemcode,
N1.Warehouse,
(sum(N1.inqty)-sum(n1.outqty)) as [Opening Balance],
0 as [IN],
0 as OUT
 
From dbo.OINM N1
Where
N1.DocDate < @FromDate
Group By
N1.ItemCode,N1.Warehouse
 
Union All
select
N1.Itemcode,
N1.Warehouse,
0 as [Opening Balance],
sum(N1.inqty) as [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.Warehouse
 
Union All
select
N1.Itemcode,
N1.Warehouse,
0 as [Opening Balance],
0 as [IN],
sum(N1.outqty) as OUT
 
From dbo.OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
N1.OutQty > 0
Group By
N1.ItemCode,N1.Warehouse) a, dbo.OITM I1
where
a.ItemCode=I1.ItemCode and
I1.ItmsGrpCod = @Group 
Group By
a.Itemcode
Having sum(a.[Opening Balance]) + sum(a.[IN]) + sum(a.OUT) > 0
Order By a.Itemcode

Thanks,

Gordon

[Former Member]

It is giving this error

1). [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near the keyword 'Having'. 2). [Microsoft][SQL Native Client][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared.

[Former Member]

Updated. You may copy again to test.

[Former Member]

Boss Thanx

it is working fine but there is one more column that i want total vale of stock my client want total value of stock

how much value of stock that i have in stock

example closing stock =20qty value =2560000