modification in the query

[Former Member]

hi Rich,

I need a small modification in first half of the query. In addition to getting the data based on the custoemr id also need to get the data between a date range and i.e -from start date - first day of the month and enddate last day of the month.

both these are not the fields in the table.

as of now I am getting a lot of records for this customer number but want the records between a certain date range

I hope you got it... How to insert that part in the query.

select * into corresponding fields of table ijha

from jhak

inner join jhaga

on jhak_{avm_nr = jhaga}avm_nr

where jhaga~inserent = p_custid.

thanks

santhosh

[RichHeilman]

How do you want to filter out these records, does the jhak or Jhaga have any date fields that you can work with. Again, these tables do not exists in my system.

If there is a date field in one of the those tables that you can work with, then its real easy.

Create a range.

ranges: r_datum for sy-datum.

r_datum-sign   = 'I'.
r_datum-option = 'BT'.
r_datum-low    = '20050701'.
r_datum-high   = '20050731'.
append r_datum.

Now in your where clause....

where jhaga~inserent = p_custid
  and SOME_DATE_FIELD in r_datum.

Regards,

Rich Heilman

[Former Member]

there is a date field (planned billing date) -fakdatum in jhaga table which can be any date in the middle of the month like '07/05/02005' from this can I generate the startdate and enddate for that month just not for july month.

what I am try to achieve here is run the records on a monthly basis from startof the month to the end of the month so that I can grab the records of that month

thanks

santhosh

[RichHeilman]

You need to know that range before the select statement.

data: sdate type sy-datum.
data: edate type sy-datum.

ranges: r_datum for sy-datum.

* First day of month will always be 01. 
sdate+0(6) = sy-datum+0(6).
sdate+6(2) = '01'. 

* Get the end of the month date
call function 'RP_LAST_DAY_OF_MONTHS'
  exporting
    day_in                  = sdate
 IMPORTING
   LAST_DAY_OF_MONTH        = edate.

* Set up your range with the start and end dates
r_datum-sign   = 'I'.
r_datum-option = 'BT'.
r_datum-low    = sdate.
r_datum-high   = edate.
append r_datum.
 
* Now do your select with this WHERE clause.

where jhaga~inserent = p_custid
  and jhaga~fakdatum in r_datum.

Regards,

Rich Heilman

[Former Member]

Thanks Rich, It really helped a lot. I will need your help further too. because i am working an a complex query to get the data from the condition types I will get back.

I have awarded 6 points because my question is not fully answered i am working on joining th puzzle is small pieces.

santhosh

[Former Member]

continuing with it...

Hi Rich,

The code goes like this...

select * into corresponding fields of table ijha

from jhak

inner join jhaga

on jhak_{avm_nr = jhaga}avm_nr

where jhaga~regulierer = p_custid and

jhaga~fakdatum in r_datum.

• I having four records in ijha table

select kposn kschl krech kawrt kbetr kwert

from konv

into table cdata

for all entries in ijha

where konv~knumv = ijha-knumv.

• It is fetching the records of various condition types from konv for each record from ijha table.

loop at cdata into wac.

case wac-kschl.

when 'ZRAT'.

MOVE wac-kbetr to w_accitab-zrate.

MOVE wac-kwert to w_accitab-zsubtotal.

when 'ZRTC'.

MOVE wac-kbetr to w_accitab-zrate.

MOVE wac-kwert to w_accitab-zsubtotal.

when 'ZAC%'.

MOVE wac-kbetr to w_accitab-zrate.

MOVE wac-kwert to w_accitab-zsubtotal.

when 'ZCOM'.

MOVE wac-kbetr to w_accitab-zrate.

MOVE wac-kwert to w_accitab-zsubtotal.

when 'ZBET'.

MOVE wac-kbetr to w_accitab-zrate.

MOVE wac-kwert to w_accitab-zamount.

endcase.

if sy-subrc = 0.

append w_accitab to accitab.

write: 20 accitab-zrate, 40 accitab-zsubtotal, 60 accitab-zamount.

endif.

clear w_accitab.

endloop.

First question.

It is checking through all the condition types and populating with the approriate data, but my question is, some condtion types for some records give all zero values and I do not want those records updated to the internal table because from the internal table i loop the data and display the data into the form.

Is there a way I can eliminate only the zero value records at the internal table level so I do not get to display in the form by using any 'IF' condition some thing like that...

Second question.

As per the requirement I do not need check for each condition type (in the case-- endcase... there are few more but I am not using them) but in this instance when I loop the itab, data checks for that condition types that I have not included and displays a line of record '0.00' in the internal table.

How can I eliminate that?

The final result of this is, taking a lot of space in the form that gives a lot of zeros appearing in the form that can be eliminated.

I hope you caught my point. How to tackle this probelm?

thanks

santhosh

[RichHeilman]

1) You can probably do this at the select statement in your where clause.

where konv~knumv = ijha-knumv.
  and konv-kbetr > 0.
  and konv-kwert > 0.

If you don't want to do it there, you can do it at your loop.

loop at cdata into wac where konv-kbetr > 0.
                         and konv-kwert > 0.

Regards,

Rich Heilman

[RichHeilman]

2) If you do not want to include any other types in your table you can check for "other" and then kick out of the loop and continue to the next record.

loop at cdata into wac where konv-kbetr > 0.
                         and konv-kwert > 0.


  case wac-kschl.
    when 'ZRAT' 
      or 'ZRTC'
      or 'ZAC%'
      or 'ZCOM'
      or 'ZBET'.
      move wac-kbetr to w_accitab-zrate.
      move wac-kwert to w_accitab-zsubtotal.
<b>
    when others.
      continue.
</b>
  endcase.

  append w_accitab to accitab.
  write: 20 accitab-zrate, 40 accitab-zsubtotal, 60 accitab-zamount.
  clear w_accitab.

endloop.

Regards,

Rich Heilman

Message was edited by: Rich Heilman

Message was edited by: Rich Heilman

[Former Member]

Thanks rich,

The first part is not working. when I extend the where clause it is getting me some different data, i am still working on it.

The second one worked well and helped to eliminate the unwanted records. It looks good now.

Just a question popped up for me here, As I told you that I am generating the monthly statements and hence I had to create the date range s_date and e-date

What if I have to generate the monthly statements for suppose month June then I cannot start with sy-datum right...

How can I modify in such a way that the user can enter any startdate irrespective of the month and go on calculate the s_date and e-date and pull the data of that month?

Thanks alot of helping me here.I really appreciate your help. I shall be rewarding you full points here.

If I have any questions I will get back to you on that.

Santhosh

[RichHeilman]

If you need the user's input then you will need a selection screen. We will modify our code like so....

data: sdate type sy-datum.
data: edate type sy-datum.

ranges: r_datum for sy-datum.

<b>parameters: p_datum type sy-datum.</b>

start-of-selection.

* First day of month will always be 01.
sdate+0(6) = <b>p_datum</b>+0(6).
sdate+6(2) = '01'.

* Get the end of the month date
call function 'RP_LAST_DAY_OF_MONTHS'
     exporting
          day_in            = sdate
     importing
          last_day_of_month = edate.

* Set up your range with the start and end dates
r_datum-sign   = 'I'.
r_datum-option = 'BT'.
r_datum-low    = sdate.
r_datum-high   = edate.
append r_datum.

Regards,

Rich Heilman

[Former Member]

WHERE ARE THE DATE FIELDS?? FROM AND TO DATE?? TABLE A OR TABLE B??

[eddy_declercq]

Hi,

If I understand it well, you need something like this:

data: startdate type dats, enddate type dats.

• initialize you data here.

select * into corresponding fields of table ijha

from jhak

inner join jhaga

on jhak_{avm_nr = jhaga}avm_nr

where jhaga~inserent = p_custid

and jhak~date between startdate and enddate

and jhaga~date between startdate and enddate.

Eddy