如何获得每月的最大日期 [英] How to get the max date per month

问题描述

我有一张像

kunnr        date            posnr
30001        28/5/2017       1
30001        25/5/2017       2
30001        15/5/2017       3
30001        25/4/2017       4
30001        20/4/2017       5
30002        15/5/2017       6
30002        25/4/2017       7

我希望每个新的 kunnr 都能获得每月最大日期的记录，即 5 月的最大值和 4 月的最大值等.

I want for every new kunnr to get the record with the max date per month, namely the max for May and max for April etc.

好的，我将对表格循环进行排序，并为每个新的 kunnr .... 我将如何获得每个月的最大日期的记录?

OK, I will sort the table loop at it and for every new kunnr .... how I will get the record for max date for each month?

提前致谢埃利亚斯

PS:某件事出错了，我意识到我没有得到我想要的.我在表格中有以下几行

PS: sth 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

我使用下面的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.

但它显示以下几行

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

要做什么才能每月有 1 条线路?

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

谢谢埃利亚斯

推荐答案

为什么不在 select from DB 期间使用聚合函数?比循环内表效率更高.

Why not to use aggregate functions just during select from DB? It is more efficient that looping internal table.

SELECT k~bukrs_vf, k~kunnr, k~erdat, MAX( p~posnr ) AS T
  FROM vbak AS k
  JOIN vbap AS p
    ON k~vbeln = p~vbeln
  INTO TABLE @DATA(lt_result)
 GROUP BY bukrs_vf, kunnr, k~erdat
 ORDER BY bukrs_vf.

注意，上面的查询只会选择那些在vbap中有对应位置的kunnrs.

Note, that the above query will select only those kunnrs which have corresponding positions in vbap.

这篇关于如何获得每月的最大日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！