Abap group rows by year and month

[scottcarricknwn]

Is there a way in Abap via Select or Loop to roll up rows in a table by year/mo? I need to extract year and month from date column (BUDAT_MKPF) and sum quantity (ERFMG) by year month. See screen shot.

[tom_wan]

Hi Scott

You can use SELECT...GROUP BY.

Example:

SELECT BUDAT_MKPF

SUM( ERFMG ) AS ERFMG

FROM <DB TABLE>

INTO TABLE <Internal Table>

GROUP BY BUDAT_MKPF.

Regards

Tom

[scottcarricknwn]

Hi Tom

Thanks for your reply. That's pretty much the query that i am currently using. However, since BUDAT_MKPF is yyyymmdd, the GROUP BY doesn't group it by yyyymm like i want it to. This is where I'm at:

SELECT lifnr matnr werks budat_mkpf bwart SUM( erfmg ) AS erfmg
INTO TABLE lt_mseg
FROM mseg
WHERE lifnr IN so_lifnr
AND matnr IN so_matnr
AND werks IN so_werks
AND bwart = 101
AND budat_mkpf GE gv_fromdate
AND budat_mkpf LE gv_todate
GROUP BY lifnr matnr werks budat_mkpf bwart
ORDER BY matnr budat_mkpf.

[pokrakam]

What version are you on? From somewhere in 7.4 SPnn onwards you have SQL expressions to make it easy:

SELECT lifnr, matnr, substring( budat_mkpf,1,6 ) AS month, SUM( erfmg ) AS erfmg FROM ...

[scottcarricknwn]

We're on 7.4 sp15 but maybe not on the SP we need to be as the substring expression is not available to use in my query.

[pokrakam]

That’s odd, it seems newer. I just checked and sql expressions have been around since SP05:

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abennews-740_sp05-open_sql.htm

[scottcarricknwn]

Hi Mike

That link is for 7.5. Appreciate the help though.

[pokrakam]

Yes, but it’s the same information, listing changes introduced in 740 SP05.

You can edit the link to 740 if it makes you feel better 🙂

[scottcarricknwn]

There is no reference to substring functions in 7.4 sp05. That only becomes available in 7.5. I guess i should have been more clear. Sorry.

[pokrakam]

Ok found it, sql expressions were added in 74sp05, substring and others came along in 7.5

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abennews-750-open_sql.htm

Sorry if it got your hopes up...

[scottcarricknwn]

No worries. Appreciate the effort.

[roberto_forti]

Hi Scott,

Try this way

1. Regarding ABAP performance tuning is recommended creating internal tables "A" and "B".

2. Perform SELECT (target fields) FROM database (target table) INTO TABLE (internal table "A") WHERE (key field EQ ... etc.)

3. If sy-subrc eq 0. Sort the (internal table "A") by LIFNR, MATNR, WERKS, BUDAT_MKPF, BWART.

4. Perform below ABAP code reading (internal table "A") filled by SELECT above and COLLECT into (internal table "B")

5. Declare work areas to internal tables "A" and "B" but the field BUDAT_MKPF from work_area "B" should be type char length 6.

LOOP AT (internal table "A" filled) into work_area "A".

MOVE-CORRESPONDING work_area "A" to work_area "B".

COLLECT work_area "B" INTO internal table "B"

ENDLOOP.

[matt]

Never select, then sort. Use ORDER BY. And into a SORTED internal table for preference.

[scottcarricknwn]

I ended up using field symbols and the following code did the trick . . .

FIELD-SYMBOLS: <fs_mseg> TYPE ty_mseg

SELECT lifnr matnr werks budat_mkpf bwart SUM( erfmg ) AS erfmg
INTO CORRESPONDING FIELDS OF ls_mseg
FROM mseg
WHERE lifnr IN so_lifnr
AND matnr IN so_matnr
AND werks IN so_werks
AND bwart = 101
AND budat_mkpf GE gv_fromdate
AND budat_mkpf LE gv_todate
GROUP BY lifnr matnr werks budat_mkpf bwart
ORDER BY matnr budat_mkpf.
ls_mseg-budat_mkpf = ls_mseg-budat_mkpf(6).
READ TABLE lt_mseg ASSIGNING <fs_mseg> WITH KEY lifnr = ls_mseg-lifnr matnr = ls_mseg-matnr budat_mkpf = ls_mseg-budat_mkpf.
IF sy-subrc = 0.
<fs_mseg>-erfmg = <fs_mseg>-erfmg + ls_mseg-erfmg.
ELSE.
APPEND ls_mseg TO lt_mseg.
ENDIF.
ENDSELECT.

[matt]

If you make LT_MSEG a HASHED table with appropriate key and use READ ... WITH TABLE KEY, you'll get a perfromance gain, especially if volumes are high.

(I'm still amazed that 18 years after their introduction, so many programmers still use STANDARD tables!).

[kiran_k8]

Matt,

I agree with you.That could be due to lack of working examples using HASHED.

I searched for a few times but didn't find an exact working example which clearly demonstrates its advantages and disadvantages.When I say disadvantages,I am referring to the flexibility a STANADARD itab offers when compared to HASHED itab when the same itab is used for different operations within the program.

Thanks,

K.Kiran.

[matt]

Over the years I have had to address poorly performing programs. One of the quick wins is to switch from STANDARD tables to HASHED or SORTED. Use HASHED when looking up data in an internal table, use SORTED similarly, but when you need the sort order for some purposes or don't have a unique key.

If you need to address the data in different ways, you can use secondary keys. I've even written a blog about them.

Tables that are used for more than one purpose within a program are, in my experience, not very common. In fact, I'd say they're rare.

Essentially, any table the you read or loop through should by keyed. Only use STANDARD where absolutely necessary - e.g. where standard FM and methods require it. Even then, if you can, you should decant into/from a keyed table, and make the STANDARD table just temporary.

The idea that it's best to stick with STANDARD just in case I need to do something else with it in the future is just poor programming practice. And who needs an example - just use it. It's well documented.

[horst_keller]

Backing you up:

https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abenselect_table_t...

https://help.sap.com/http.svc/rc/abapdocu_752_index_htm/7.52/en-US/index.htm?file=abensecondary_key_...

[kiran_k8]

Thanks Horst/Matt.

K.Kiran.