Need closing balance of inventory in warehouse

[Former Member]

Hi experts,

I need modification in this query, need to add closing balance of inventory stock in all warehouse, and also need from date and to date. In this query it showing IN qty and OUT qty in same table need to separate and also add closing balance of stock.

Declare @DocDate DateTime

Select @DocDate = Max(AA.DocDate) from OINM AA Where AA.DocDate<=[%0]

Set @DocDate = '[%0]'

SELECT T0.[ItemCode], T1.[ItemName], T0.[Warehouse], T3.WhsName

,T2.FormatCode,T2.AcctName--, T0.InvntAct

,@DocDate[Stock As on Date]

,Sum(T0.[InQty]-T0.[OutQty])[Qty]

,Sum(T0.[TransValue]) [Value]

FROM OINM T0

  INNER JOIN OITM T1 ON T0.[ItemCode] = T1.[ItemCode]

  Left Outer Join OACT T2 on T0.InvntAct = T2.AcctCode

  Inner join OWHS T3 on T0.Warehouse = T3.WhsCode

WHERE T0.[DocDate] <=@DocDate

GROUP BY T0.[ItemCode], T1.[ItemName], T0.[Warehouse], T3.WhsName--, T0.InvntAct

,T2.FormatCode ,T2.AcctName

Having ABS(Sum(T0.[InQty]-T0.[OutQty]))+ABS(Sum(T0.[TransValue]))>0

Regards,

J.vigneshwaran

[Former Member · Accepted solution]

Hi Vignesh,

Try this and modify based on your requirement.

select a.ItemCode,a.[Name],SUM(a.[OB-Qty]) [OB-Qty],SUM(a.[OB-Value]) [OB-Value], sum(a.[Issue]) [Issue] ,sum(a.[Receipt]) [Receipt], SUM(a.[Cls-Qty]) [Cls-Qty], SUM([ClsValue]) [ClsValue]  from( select t1.ItemCode,max(t1.dscription) [Name], (sum(isnull(t1.inqty,0)) - sum(isnull(t1.outqty,0)) ) [OB-Qty], sum(isnull(t1.transvalue,0)) [OB-Value],0 [Issue], 0 [Receipt], 0 [Cls-Qty],0 [ClsValue] from OINM t1 where t1.docdate < '[%0]'  Group by t1.ItemCode union all select t1.ItemCode,max(t1.dscription) [Name],0 [OB-Qty],0 [OB-Value], (sum(isnull(t1.outqty,0))) [Issue], (sum(isnull(t1.inqty,0))) [Receipt], 0 [Cls-Qty],0 [ClsValue] from OINM t1 where t1.docdate >= '[%0]' and  t1.DocDate

[KennedyT21]

Hi Vigneshwaran...

Try This

/************************************************************

* Code formatted by SoftTree SQL Assistant © v5.1.7

* Time: 8/6/2015 1:10:22 PM

************************************************************/

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 = 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

Regards

Kennedy

[former_member188586]

hi

please check bellow thread my replay

and update the status...

[Former Member]

Hi,

I have checked but its not showing closing balance.

[former_member212181]

Hi Vigneshwaran,

Please try below query

Declare @FDate DateTime, @TDate DateTime, @Whs Varchar (10)

Select @FDate = Max(AA.DocDate) from OINM AA Where AA.DocDate>=[%0]

Select @TDate = Max(AB.DocDate) from OINM AB Where AB.DocDate<=[%1]

Select @Whs = Max(AC.Warehouse) from OINM AC Where AC.Warehouse Like '%[%2]%'

Set @FDate = '[%0]'

Set @TDate = '[%1]'

;WITH STKLOG AS

(

Select A.ItemCode[Item_Code], B.ItemName[Item_Name],A.Warehouse[Warehouse_Code]

  ,0[OB_Qty]

  ,0[OB_Value]

  ,Sum(A.InQty)[ReceiptQty]

  ,Case When A.TransValue>0 then Sum(A.TransValue) else 0 end [ReceiptValue]

  ,Sum(A.OutQty)[OutQty]

  ,Case When A.TransValue<=0 then Sum(A.TransValue) else 0 end [OutValue]

  ,0[Cls_Qty]

  ,0[Cls_Value]

from dbo.OINM A

  Inner Join OITM B on A.ItemCode = B.ItemCode

Where A.DocDate>=@FDate and A.DocDate<=@TDate and A.Warehouse Like '%[%2]%'

Group By A.ItemCode, B.ItemName,A.Warehouse, A.TransValue

Union All

SELECT T0.ItemCode[Item_Code], T1.ItemName[Item_Name], T0.Warehouse[Warehouse_Code]

  ,Case When T0.DocDate <@FDate then Sum(T0.InQty-T0.OutQty) else 0 end [OB_Qty]

  ,Case When T0.DocDate <@FDate then Sum(T0.TransValue) else 0 end [OB_Value]

  ,0[ReceiptQty]

  ,0[ReceiptValue]

  ,0[OutQty]

  ,0[OutValue]

  ,Case When T0.DocDate <=@TDate then Sum(T0.InQty-T0.OutQty) else 0 end [Cls_Qty]

  ,Case When T0.DocDate <=@TDate then Sum(T0.TransValue) else 0 end [Cls_Value]

FROM OINM T0

  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T0.DocDate <=@TDate and T0.Warehouse Like '%[%2]%'

GROUP BY T0.[ItemCode], T1.ItemName, T0.Warehouse, T0.DocDate

)

----

Select @FDate[From Date], @TDate [To Date]

  , STKLOG.Item_Code

  , STKLOG.Item_Name, STKLOG.Warehouse_Code

  ,Sum(STKLOG.OB_Qty)[OBQTY]

  ,Sum(STKLOG.OB_Value)[OBValue]

  ,Sum(STKLoG.ReceiptQty)[RecQty]

  , Sum(STKLOG.ReceiptValue)[RecValue]

  ,Sum(STKLoG.OutQty)[OutQty]

  ,Sum(STKLOG.OutValue)[OutValue]

  ,Sum(STKLOG.Cls_Qty)[ClsQty], Sum(STKLOG.Cls_Value)[ClsValue]

From STKLOG

group By STKLOG.Item_Code, STKLOG.Item_Name, STKLOG.Warehouse_Code

Thanks

Unnikrishnan

[Former Member]

Hi unnikrishnan sir,

Previous query you only gave to me, in that item code, item name, warehouse code, whs name, format code(account code), account name, stock on date(31/03/2015), qty, value.

In this i need to add closing value and closing qty and from and to date. its possible please make it.

Regards,

J.vigneshwaran

[former_member188586]

hi

check bellow thread

--Ramudu