Insert missing dates in a date range

[siongchao_ng]

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

[Former Member]

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

[sivaganesh_krishnan]

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

[Former Member]

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.

[Former Member]

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

[siongchao_ng]

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.

[Former Member]

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.

1.   DATA: lt_output TYPE TABLE OF ty_output_main,
2.         lv_date TYPE bkpf-budat.
3.   lv_date = s_budat-low.
4.   WHILE lv_date LE s_budat-high.
5.     READ TABLE gt_output_main TRANSPORTING NO FIELDS WITH KEY budat = lv_date.
6.     IF sy-subrc EQ 0.
7.       LOOP AT gt_output_main INTO gw_output_main WHERE budat EQ lv_date.
8.         APPEND gw_output_main TO lt_output.
9.       ENDLOOP.
10.     ELSE.
11.       gw_output_main-budat = lv_date.
12.       gw_output_main-werks = p_werks.
13.       APPEND gw_output_main TO lt_output.
14.     ENDIF.
15.     lv_date = lv_date + 1.
16.   ENDWHILE.

/.