topic Fetching data for las 12 Months in Application Development and Automation Discussions

Fetching data for las 12 Months

Former Member — Thu, 21 Dec 2006 13:07:44 GMT

Dear All,

We are having one custom table where fields are customer no, material no, year, month, quantity1.

Now I want to create a report which on entering customer no & material no in input should add up the values for last 12 months from the current date and display as one value.

Example of entries in database:

custmr no matrl no. year month qty1

1 1 2006 01 45.00

1 1 2006 02 25.00

1 2 2006 01 10.00

After executing the report I want the result as

custmr no matrl no. year qty1

1 1 2006 70.00

1 2 2006 10.00

and this addition should be done for last 12 months from the current date.

Warm Regards,

Nishu

Re: Fetching data for las 12 Months

Former Member — Thu, 21 Dec 2006 13:16:41 GMT

SELECT cust matno year month qty1

into table itab

from ztable

where year in r_year.

ldt = sy-datum - 365.

concatenate ldt(6) '01' into ld2.

ld1 = sy-datum.

Loop at itab.

concatenate itab-year itab-month '01' into ldate.

check ldate <= ld1 and ldate >= ld2.

move-corresponding itab to itot.

collect itot.

endloop.

Re: Fetching data for las 12 Months

Former Member — Thu, 21 Dec 2006 13:23:46 GMT

DATA: PERIOD(6) TYPE N,
           PERIOD_FROM(6) TYPE N,
           PERIOD_TO(6)       TYPE N.

PERIOD_FROM(6) = SY-DATUM(4).
PERIOD_TO(4)       = SY-DATUM(4).
PERIOD_TO+4(2)   = '12'.

LOOP AT ITAB.
  PERIOD(4)     = ITAB-YEAR.
  PERIOD+4(2) = ITAB-MONTH. 
  IF PERIOD => PERIOD_FROM AND PERIOD <= PERIOD_TO.
    COLLECT ITAB INTO ITAB2.
  ENDIF.
ENDLOOP.

Max

Re: Fetching data for las 12 Months

Former Member — Thu, 21 Dec 2006 13:43:44 GMT

DATA: V1(4) TYPE N,

V2(2) TYPE N,

V3(2) TYPE N.

V1 = SY-DATUM+0(4) - 1.

V2 = SY-DATUM+4(2).

V3 = SY-DATUM+6(2).

contatenate V1 V2 V3 into date1.

select * from z-table into itab where date <= sy-datum

and date >= date1.

Re: Fetching data for las 12 Months

andreas_mann3 — Thu, 21 Dec 2006 13:58:50 GMT

hi,

) calculate previous_year = sy-datum(4) - 1.
2) calcualate period_from  = sy-datum+4(2) + 1.
   if period_from > 12.
      period_from = 1.
      prvious_year = sy-datum(4).
   endif.

3) select yout table with where clause:

where ( ( year = previous year 
   and month between previous_month and 12 )
     or year = sy-datum(4)  
   and month between 1 and sy-datum+4(2) ) )
...