on 05-27-2010 7:42 AM
Hi,
I want a daily stock report for a trading industry. In this i want date wise opening stock, closing stock, outward quantity and inward quantity. How to get it?
Thanks,
Niranjan
hi,
please run the INVENTORY AUDIT REPORT.in that give everydays date in " from ......to" fields.so that you can get everyday opening balance,inventory movements,price,associated documents...everything...
hope this will meet your requirments
regards
Ravindran
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Pls Chck this code:
Declare @FromDate Datetime
Declare @ToDate Datetime
Declare @Whse 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 @Whse = (Select Max(s2.Warehouse) from dbo.OINM S2 Where S2.Warehouse = '[%2]')
Select
@Whse as 'Warehouse',
a.Itemcode,
max(a.Dscription) as ItemName,
sum(a.OpeningBalance) as OpeningBalance,
sum(a.INq) as 'IN',
sum(a.OUT) as OUT,
((sum(a.OpeningBalance) + sum(a.INq)) - Sum(a.OUT)) 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
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
From dbo.OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <= @ToDate and
N1.Inqty >0
and N1.Warehouse = @Whse
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
From dbo.OINM N1
Where
N1.DocDate >= @FromDate and N1.DocDate <=@ToDate and
N1.OutQty > 0
and N1.Warehouse = @Whse
Group By
N1.Warehouse,N1.ItemCode,N1.Dscription) a, dbo.OITM I1
where
a.ItemCode=I1.ItemCode
Group By
a.Itemcode
Having sum(a.OpeningBalance) + sum(a.INq) + sum(a.OUT) > 0
Order By a.Itemcode
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.