topic Re: Insert missing dates in a date range in Application Development and Automation Discussions

Insert missing dates in a date range

siongchao_ng — Mon, 25 Aug 2014 01:24:55 GMT

Hi all,

How do I find out any gap and insert the necessary missing dates in a date range?

Example:

User entered the date range: posting date : 01.02.2014 to 28.02.2014

In the program I need to display all the data from a custom table from the dates above:

Note that 1/02/2014 till 03/02/2014 is missing. I am required to insert/list out each day missing dates from the date range that user entered on the selection screen. Any FM to do that? or any other ideas?

Date Area Location

04/02/2014

05/02/2014

06/02/2014

07/02/2014

08/02/2014

09/02/2014

10/02/2014

11/02/2014

12/02/2014

13/02/2014

14/02/2014

15/02/2014

16/02/2014

17/02/2014

18/02/2014

19/02/2014

20/02/2014

21/02/2014

22/02/2014

23/02/2014

24/02/2014

25/02/2014

26/02/2014

27/02/2014

28/02/2014

Re: Insert missing dates in a date range

Former Member — Mon, 25 Aug 2014 03:07:05 GMT

Hi Siong,

Say s_date is 01.02.2014 to 28.02.2014, and it_itab with those data.

so you can:

data: lv_index type c.

do 28 times. " Or you can get times by FM 'DAYS_BETWEEN_TWO_DATES '

s_date-low+6(2) = sy-index.

wa_date-low = s_date-low.

append wa_date to it_data.

enddo.

sort it_itab by date.

loop at it_date to wa_date.

read it_itab into wa_itab with key date = wa_date-low binary search.

wa_final-date = wa_date-low.

wa_final-area = wa_itab-area.

wa_final-loca = wa_itab-loca.

append wa_final to it_final.

endloop.

regards,

Archer

Re: Insert missing dates in a date range

sivaganesh_krishnan — Mon, 25 Aug 2014 03:59:58 GMT

Hi,

For displaying entries from custom table by using the specified date range you can use the range table.

Declare a range table.

type:BEGIN OF ty_selection,

sign TYPE char01,

option TYPE char02,

low TYPE <dataelement>,

high TYPE <dataelement>,

END OF ty_selection.

DATA: lt_selection TYPE TABLE OF ty_selection,

lx_selection TYPE ty_selection.

lx_selection-sign = 'I'.

lx_selection-option = 'BT'.

lx_selection-low = low_date.

lx_selection-high = high_date.

APPEND lx_selection TO lt_selection.

then in the select statement you can use the

select *

from table

into table ***

where date IN lt_selection.

Regards,

Sivaganesh

Re: Insert missing dates in a date range

Former Member — Mon, 25 Aug 2014 04:26:25 GMT

Hi,

s_date is 01.02.2014 to 28.02.2014

Here lv_days = s_date-high+6(2).

so you can:

data: lv_index type c.

do lv_days times.

s_date-low+6(2) = sy-index.

wa_date-low = s_date-low.

append wa_date to it_data.

enddo.

sort it_itab by date.

loop at it_date to wa_date.

read it_itab into wa_itab with key date = wa_date-low binary search.

wa_final-date = wa_date-low.

wa_final-area = wa_itab-area.

wa_final-loca = wa_itab-loca.

append wa_final to it_final.

endloop.

Re: Insert missing dates in a date range

Former Member — Mon, 25 Aug 2014 04:58:01 GMT

Hi,

if PD-LOW to PD-HIGH is your posting date range, you can do it in a DO loop.

DATA: h_date TYPE d.

h_date = pd-low.

DO.

IF h_date > pd-high.

EXIT.

ENDIF.

SELECT COUNT( * ) FROM my_tab

WHERE zdate EQ h_date.

IF sy-dbcnt LT 1.

WRITE: /1 'Date missing in my_tab', h_date.

ENDIF.

ADD 1 TO h_date.

ENDDO.

Regards,

Klaus

Re: Insert missing dates in a date range

siongchao_ng — Tue, 26 Aug 2014 01:20:14 GMT

Thanks all.

I used the following to solve the problem:

LOOP AT gt_output_main INTO gw_output_main WHERE budat LE s_budat-high.

gv_budat_difference = gw_output_main-budat - gv_budat_old.

* get the table index

gv_tabix = sy-tabix.

* check for any missing dates gap at beginning the user date selection

gv_budat_early_gap = gw_output_main-budat - s_budat-low.

READ TABLE gt_output_main INTO gw_output_main3 WITH KEY budat = s_budat-low.

IF sy-subrc <> 0.

IF gv_budat_early_gap > 1.

gv_budat_add_from = s_budat-low.

DO gv_budat_early_gap TIMES.

* add from s_budat-low

gw_output_main2-budat = gv_budat_add_from.

gw_output_main2-werks = p_werks.

APPEND gw_output_main2 to gt_output_main.

CLEAR gw_output_main2.

add 1 to gv_budat_add_from.

ENDDO.

CLEAR gv_budat_add_from.

CLEAR gv_budat_early_gap.

ENDIF.

CLEAR gw_output_main3.

ENDIF.

* check for any missing dates gap in between the user date selection

IF gv_tabix > 1 AND gv_budat_difference > 1.

* add missing dates gap

gv_budat_add_from = gv_budat_old.

* add until the date shown from zmm_ul_ps_ullage

gv_budat_difference = gv_budat_difference - 1.

DO gv_budat_difference TIMES.

add 1 to gv_budat_add_from.

gw_output_main2-budat = gv_budat_add_from.

gw_output_main2-werks = p_werks.

APPEND gw_output_main2 to gt_output_main.

CLEAR gw_output_main2.

ENDDO.

CLEAR gv_budat_add_from.

ENDIF.

SORT gt_output_main by budat ASCENDING.

gv_budat_old = gw_output_main-budat.

CLEAR gw_output_main.

CLEAR gv_budat_difference.

ENDLOOP.

* check for any missing dates gap at the end of user date selection

DESCRIBE TABLE gt_output_main LINES gv_budat_end.

READ TABLE gt_output_main INTO gw_output_main INDEX gv_budat_end.

IF gw_output_main-budat <> s_budat-high.

gv_budat_difference = s_budat-high - gw_output_main-budat.

gv_budat_add_from = gw_output_main-budat.

DO gv_budat_difference TIMES.

add 1 to gv_budat_add_from.

gw_output_main2-budat = gv_budat_add_from.

gw_output_main2-werks = p_werks.

APPEND gw_output_main2 to gt_output_main.

CLEAR gw_output_main2.

SORT gt_output_main by budat ASCENDING.

ENDDO.

CLEAR gv_budat_add_from.

ENDIF.

CLEAR gw_output_main.

Re: Insert missing dates in a date range

Former Member — Tue, 26 Aug 2014 09:09:12 GMT

Hi Siong

The code is a bit lengthy in my opinion.

Have a look at this snippet. I think lt_output would have desired content.

  DATA: lt_output TYPE TABLE OF ty_output_main, 
        lv_date TYPE bkpf-budat. 
  lv_date = s_budat-low. 
  WHILE lv_date LE s_budat-high. 
    READ TABLE gt_output_main TRANSPORTING NO FIELDS WITH KEY budat = lv_date. 
    IF sy-subrc EQ 0. 
      LOOP AT gt_output_main INTO gw_output_main WHERE budat EQ lv_date. 
        APPEND gw_output_main TO lt_output. 
      ENDLOOP. 
    ELSE. 
      gw_output_main-budat = lv_date. 
      gw_output_main-werks = p_werks. 
      APPEND gw_output_main TO lt_output. 
    ENDIF. 
    lv_date = lv_date + 1. 
  ENDWHILE. 
 