none
Select latest month inventory

    Question

  • Hi,

    I am trying to select inventory value for the last month for each material. The following is the result set from my query and I want to select only the circled rows (inventory value from latest month). The query I am running is attached as well. Any assistance would be of great help. Thanks.

    SELECT MBEW.MATERIAL_MATNR AS MBEW_MATNR,
    
    MBEW.VALUATION_AREA_BWKEY MBEW_BWKEY,
    MBEW.TOTAL_VALUE_SALK3 AS MBEW_SALK3,
    MBEWH.YEAR_CURPERIOD_LFGJA MBEWH_LFGJA ,
    MBEWH.CURRENT_PERIOD_LFMON MBEWH_LFMON,
    MBEWH.TOTAL_VALUE_SALK3 AS MBEWH_SALK3
    FROM [Materials].[MBEW_MATERIAL_VALUATION] MBEW
    LEFT OUTER JOIN [Materials].[MBEWH_MATERIAL_VALUATION_HISTORY] MBEWH ON
    MBEW.[MATERIAL_MATNR]=MBEWH.MATERIAL_MATNR
    AND MBEW.[VALUATION_AREA_BWKEY]=MBEWH.[VALUATION_AREA_BWKEY]
    AND MBEW.[VALUATION_TYPE_BWTAR]=MBEWH.[VALUATION_TYPE_BWTAR]
    WHERE MBEW.VALUATION_CLASS_BKLAS=3030 AND 
    MBEW.VALUATION_AREA_BWKEY ='1510'
    AND MBEWH.TOTAL_VALUE_SALK3<>0.00
    and MBEWH.YEAR_CURPERIOD_LFGJA=2013
    ORDER BY MBEW_MATNR
    

    Query Results

    Friday, December 13, 2013 9:55 PM

Answers

  • What table has the date associated with:

    MBEWH.YEAR_CURPERIOD_LFGJA MBEWH_LFGJA ,
    MBEWH
    .CURRENT_PERIOD_LFMON MBEWH_LFMON,

    Does Dec. 13, 2013 hava a CURRENT_PERIOD_LFMON of 11?


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by SBC_WISC Monday, December 16, 2013 5:51 PM
    Friday, December 13, 2013 10:26 PM
  •  
    SELECT MBEW_MATNR,MBEW_BWKEY,MBEW_SALK3,MBEWH_LFGJA,MBEWH_LFMON,MBEWH_SALK3
    FROM    (SELECT MBEW.MATERIAL_MATNR AS MBEW_MATNR,
    
    MBEW.VALUATION_AREA_BWKEY MBEW_BWKEY,
    MBEW.TOTAL_VALUE_SALK3 AS MBEW_SALK3,
    MBEWH.YEAR_CURPERIOD_LFGJA MBEWH_LFGJA ,
    MBEWH.CURRENT_PERIOD_LFMON MBEWH_LFMON,
    MBEWH.TOTAL_VALUE_SALK3 AS MBEWH_SALK3
    , Row_number() Over(Partition By  MBEW.MATERIAL_MATNR Order By  Cast(Cast(MBEWH.YEAR_CURPERIOD_LFGJA as char(4))+Right('0'+Cast(MBEWH.CURRENT_PERIOD_LFMON as varchar(2)),2) +'01' as Datetime)  DESC) rn
    FROM [Materials].[MBEW_MATERIAL_VALUATION] MBEW
    LEFT OUTER JOIN [Materials].[MBEWH_MATERIAL_VALUATION_HISTORY] MBEWH ON
    MBEW.[MATERIAL_MATNR]=MBEWH.MATERIAL_MATNR
    AND MBEW.[VALUATION_AREA_BWKEY]=MBEWH.[VALUATION_AREA_BWKEY]
    AND MBEW.[VALUATION_TYPE_BWTAR]=MBEWH.[VALUATION_TYPE_BWTAR]
    WHERE MBEW.VALUATION_CLASS_BKLAS=3030 AND 
    MBEW.VALUATION_AREA_BWKEY ='1510'
    AND MBEWH.TOTAL_VALUE_SALK3<>0.00
    and MBEWH.YEAR_CURPERIOD_LFGJA=2013
    
    ) t
    
    WHERE rn=1
    ORDER BY MBEW_MATNR

    • Marked as answer by SBC_WISC Monday, December 16, 2013 5:51 PM
    Friday, December 13, 2013 10:28 PM
    Moderator

All replies

  • What table has the date associated with:

    MBEWH.YEAR_CURPERIOD_LFGJA MBEWH_LFGJA ,
    MBEWH
    .CURRENT_PERIOD_LFMON MBEWH_LFMON,

    Does Dec. 13, 2013 hava a CURRENT_PERIOD_LFMON of 11?


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Marked as answer by SBC_WISC Monday, December 16, 2013 5:51 PM
    Friday, December 13, 2013 10:26 PM
  •  
    SELECT MBEW_MATNR,MBEW_BWKEY,MBEW_SALK3,MBEWH_LFGJA,MBEWH_LFMON,MBEWH_SALK3
    FROM    (SELECT MBEW.MATERIAL_MATNR AS MBEW_MATNR,
    
    MBEW.VALUATION_AREA_BWKEY MBEW_BWKEY,
    MBEW.TOTAL_VALUE_SALK3 AS MBEW_SALK3,
    MBEWH.YEAR_CURPERIOD_LFGJA MBEWH_LFGJA ,
    MBEWH.CURRENT_PERIOD_LFMON MBEWH_LFMON,
    MBEWH.TOTAL_VALUE_SALK3 AS MBEWH_SALK3
    , Row_number() Over(Partition By  MBEW.MATERIAL_MATNR Order By  Cast(Cast(MBEWH.YEAR_CURPERIOD_LFGJA as char(4))+Right('0'+Cast(MBEWH.CURRENT_PERIOD_LFMON as varchar(2)),2) +'01' as Datetime)  DESC) rn
    FROM [Materials].[MBEW_MATERIAL_VALUATION] MBEW
    LEFT OUTER JOIN [Materials].[MBEWH_MATERIAL_VALUATION_HISTORY] MBEWH ON
    MBEW.[MATERIAL_MATNR]=MBEWH.MATERIAL_MATNR
    AND MBEW.[VALUATION_AREA_BWKEY]=MBEWH.[VALUATION_AREA_BWKEY]
    AND MBEW.[VALUATION_TYPE_BWTAR]=MBEWH.[VALUATION_TYPE_BWTAR]
    WHERE MBEW.VALUATION_CLASS_BKLAS=3030 AND 
    MBEW.VALUATION_AREA_BWKEY ='1510'
    AND MBEWH.TOTAL_VALUE_SALK3<>0.00
    and MBEWH.YEAR_CURPERIOD_LFGJA=2013
    
    ) t
    
    WHERE rn=1
    ORDER BY MBEW_MATNR

    • Marked as answer by SBC_WISC Monday, December 16, 2013 5:51 PM
    Friday, December 13, 2013 10:28 PM
    Moderator
  • Not Clear on your requirement.

    If you want to rank the rows in your result set and get the only rows with month 10.try this.

    ;With CTE
    as
    (
    SELECT MBEW.MATERIAL_MATNR AS MBEW_MATNR,
    MBEW.VALUATION_AREA_BWKEY MBEW_BWKEY,
    MBEW.TOTAL_VALUE_SALK3 AS MBEW_SALK3,
    MBEWH.YEAR_CURPERIOD_LFGJA MBEWH_LFGJA ,
    MBEWH.CURRENT_PERIOD_LFMON MBEWH_LFMON,
    MBEWH.TOTAL_VALUE_SALK3 AS MBEWH_SALK3,
    Row_Number() Over(Partition by MBEW.TOTAL_VALUE_SALK3 Order by MBEWH.CURRENT_PERIOD_LFMON desc) as rnum
    FROM [Materials].[MBEW_MATERIAL_VALUATION] MBEW
    LEFT OUTER JOIN [Materials].[MBEWH_MATERIAL_VALUATION_HISTORY] MBEWH ON
    MBEW.[MATERIAL_MATNR]=MBEWH.MATERIAL_MATNR
    AND MBEW.[VALUATION_AREA_BWKEY]=MBEWH.[VALUATION_AREA_BWKEY]
    AND MBEW.[VALUATION_TYPE_BWTAR]=MBEWH.[VALUATION_TYPE_BWTAR]
    WHERE MBEW.VALUATION_CLASS_BKLAS=3030 AND 
    MBEW.VALUATION_AREA_BWKEY ='1510'
    AND MBEWH.TOTAL_VALUE_SALK3<>0.00
    and MBEWH.YEAR_CURPERIOD_LFGJA=2013
    )
    select * from CTE
    where rnum=1
    ORDER BY MBEW_MATNR
    


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Friday, December 13, 2013 10:29 PM