How to get the max date per month using SQL

Question

Something went wrong and I realise that I do not get what I want. I have the following lines in a table:

0000527746  1000    10.06.2017  20170718100757.5010080
0000527746  1000    10.06.2017  20170718100757.5039300
0000527746  1000    11.06.2017  20170718100839.9209480
0000527746  1000    11.06.2017  20170718100906.3337170
0000527746  1000    24.07.2017  20170718095843.3555610
0000527746  1000    24.07.2017  20170718100209.2203570
0000527746  1000    24.07.2017  20170718100757.4970390

and I want to select the last date of each month namely I want the select to bring me the following lines

0000527746  1000    11.06.2017  20170718100906.3337170
0000527746  1000    24.07.2017  20170718100757.4970390

I use the following sql

select bukrs kunnr dat max( time ) as time
    from zcollectoraction into corresponding fields of table it_collectoraction
    where bukrs = p_bukrs and
          kunnr in so_kunnr and
          dat   in so_date
    group by bukrs kunnr dat.

but it displays the following lines

0000527746  1000    11.06.2017  20170718100906.3337170
0000527746  1000    11.06.2017  20170718100906.3337170
0000527746  1000    24.07.2017  20170718100757.4970390

What to do in order to have 1 line per month?

What do `bukrs` and `kunnr` refer to? I presume the first two columns? — Ken Y-N, Jul 20 '17 at 08:37
Does this answer your question? [How to get the max date per month](https://stackoverflow.com/questions/45001280/how-to-get-the-max-date-per-month) — Suncatcher, Apr 09 '20 at 10:32

Oguz · Answer 1 · 2017-07-21T15:29:08.007

I think there are two solutions for this matter. 1) You can add yearmonth field to your db table. And add this field to group by statement.

0000527746  1000    10.06.2017  20170718100757.5010080 201706
0000527746  1000    10.06.2017  20170718100757.5039300 201706
0000527746  1000    11.06.2017  20170718100839.9209480 201706
0000527746  1000    11.06.2017  20170718100906.3337170 201706
0000527746  1000    24.07.2017  20170718095843.3555610 201707
0000527746  1000    24.07.2017  20170718100209.2203570 201707
0000527746  1000    24.07.2017  20170718100757.4970390 201707

select bukrs kunnr dat max( time ) as time
    from zcollectoraction into corresponding fields of table 
    it_collectoraction
    where bukrs = p_bukrs and
          kunnr in so_kunnr and
          dat   in so_date
    group by bukrs kunnr dat yearmonth.

2) You can select all the data and arrange in loop statement. Or you can use your old select query does not matter at all.

select bukrs kunnr dat time
    from zcollectoraction into corresponding fields of table 
    it_collectoraction
    where bukrs = p_bukrs and
          kunnr in so_kunnr and
          dat   in so_date .

loop at it_collectoraction into data(ls_coll).
   delete it_collectoraction[] WHERE dat(6) = ls_coll-dat(6) 
                                 and dat < = ls_coll-dat 
                                 and time < ls_coll-time.
endloop.

score -1 · Answer 2 · answered Jul 20 '17 at 08:40

-1

What you need is to group by not dat, but by month and year - this clause will work:

GROUP BY bukrs, kunnr, MONTH(dat), YEAR(dat)

answered Jul 20 '17 at 08:40

Ken Y-N

14,644
21
71
114

You cannot use it in abap OpenSQL Statements. – Oguz Jul 21 '17 at 07:36

score -1 · Accepted Answer · answered Jul 20 '17 at 12:11

Hello and thanks for your answers. I solved the problem by doing 2 selects. In the 1st I get the last day or days of the month with the following selection

select bukrs kunnr yearmonth max( dat ) as dat
    from zcollectoraction into corresponding fields of table it_collectoraction
    where bukrs = p_bukrs and
          kunnr in so_kunnr and
          dat   in so_date
    group by bukrs kunnr yearmonth.

and then I made a loop to the internal table to fill the remaining data and select the MAX Time for all records and especially when there are more than 1 lines per bukrs, kunnr and date.

select single * from zcollectoraction 
                        into corresponding fields of wa_collectoraction
  where bukrs = wa_collectoraction-bukrs and
        kunnr = wa_collectoraction-kunnr and
        dat   = wa_collectoraction-dat   and
        time  = ( select max( time ) as time
                    from zcollectoraction
                    where bukrs = wa_collectoraction-bukrs and
                          kunnr = wa_collectoraction-kunnr and
                          dat   = wa_collectoraction-dat ).

Again thanks Elias

OpenSQL does not contain a MONTH function. You either have to use Native SQL, or select every possible line at once, and find the monthly maximum on ABAP side — András, Jul 21 '17 at 07:20

How to get the max date per month using SQL

3 Answers3