Skip to Content
0
Former Member
Jul 22, 2010 at 10:51 AM

Inventory report

17 Views

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