Skip to Content
avatar image
Former Member

QUERY FOR INVENTORY OPENING AND CLOSING ON HANA

Hi All

Can any one give me a query on HANA for Inventory with  Opening and Closing balance ??

I need these fields

1) Item Code

2) Description

3) Average Cost

3) Opening Balance

4) Purchase

5) Purchase return

6) Closing balance

Parameter should be Date range , Item group, Warehouse, Item wise,

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Oct 27, 2015 at 07:04 AM

    Hi,

    Try this

    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 <= '[%1]'

    group by t1.ItemCode

    union all

    select t1.ItemCode,max(t1.dscription) [Name],0 [OB-Qty],0 [OB-Value],

    0 [Issue],0 [Receipt],

    (sum(isnull(t1.inqty,0)) - sum(isnull(t1.outqty,0)) ) [Cls-Qty],

    sum(isnull(t1.transvalue,0))  [ClsValue]

    from OINM t1

    where t1.DocDate <= '[%1]'

    group by t1.ItemCode)a

    group by a.ItemCode,a.[Name]

    Regards,

    SP Samy

    Add comment
    10|10000 characters needed characters exceeded