How to write SQL based on these conditions ?

[former_member720770]

The conditions for selecting stock valuation data are as blew:

<Table>

MBEWH

MBEW

<Condition>

MBEWH-WEKEY = [Valuation Area] selected from 1.2 (T001W-BEKEY)

MBEWH-MATNR = [Material Number] selected from 3.2 (MARC-MATNR and MARC-MATNR)

MBEWH-LFGJA + MBEWH-LFMON >= [PERIOD](LDF_PERIOD gotten from function 'G_PERIOD_GET') + [YEAR](LDF_YEAR) -1

MBEW-WEKEY = [Valuation Area] selected from 1. 2

MBEW-MATNR = [Material Number] selected from 3. 2

<Field>

MBEW-LBKUM

MBEWH-LBKUM

MBEW-SALK3

MBEWH-SALK3

The details about <MBEWH-LFGJA + MBEWH-LFMON >= [PERIOD](LDF_PERIOD gotten from function 'G_PERIOD_GET') + [YEAR](LDF_YEAR) -1> would be like:

MBEWH~LFGJA + MBEWH~LFMON = Low~(Year+Period)-1

If there is no entry in MBEWH, copy the existed value from MBEWH where MBEWH~LFGJA + MBEWH~LFMON = Low~(Year+Period) + n(n>=0)

If there are no entries in MBEWH for all months, copy the existed value(LBKUM) from MBEW.

omg What should I do?

anyone here, expecting for your kind help.

Thank you all😭😭

[FredericGirod]

Your functional colleague should stop trying to write pseudo code and give you the expected result.

MBEWH is the history of the MBEW table. It contains the previous value by period of the MBEW. But, not all the period are recorded, just when there is a change.

I think your colleague tries to ask you to get the previous value of the Price valuation of the materials

[former_member720770]

How to write the code of this:

MBEWH~LFGJA + MBEWH~LFMON = Low~(Year+Period)-1

If there is no entry in MBEWH, copy the existed value from MBEWH where MBEWH~LFGJA + MBEWH~LFMON = Low~(Year+Period) + n(n>=0)

If there are no entries in MBEWH for all months, copy the existed value(LBKUM) from MBEW.

Really confused...

[Dominik_Tylczynski]

Hello miyuriowo

Consider the note 193554 - Stock/valuation data of previous periods for detailed explanation of stock history tables logic.

If you are already on S/4HANA see the note 2600024 - Create a Material, updates History Tables, MCHBH, MARCH and MARDH, with past Years (LFGJA)... that explains:

In ERP the history tables contain only an entry if there was a change within the related period. In S/4HANA the history tables contain an entry for each period (independent on whether there was a change or not).

Best regards

Dominik Tylczynski

[FredericGirod]

Forget this idea. Just make a selection with the Year & Month GE to the select-option. Make a Sort, and take the first entry.

[former_member720770]

ok. try as you suggested.