topic ABAP SQL query on in Application Development and Automation Discussions

ABAP SQL query on

Former Member — Fri, 14 Mar 2014 13:06:06 GMT

Hi,

Can anyone tell me what is the SELECT statement for getting the latest time records based on date and other parameter.

Table data
DATE	TIME	SOURCE	DEST	PRD	QTY
10.03.2014	13:18:51	UHA	ABDH	DXCC17B	1,000.00
10.03.2014	13:24:56	UHA	ABDH	DXCC17B	3,000.00
10.03.2014	13:24:56	UHA	ABDH	DXCC16B	1,000.00
11.03.2014	13:18:35	UHA	ABDH	DXCC17B	1,000.00
11.03.2014	13:24:20	UHA	ABDH	DXCC17B	3,000.00
11.03.2014	13:24:30	UHA	ABDH	DXCC17B	1,000.00
11.03.2014	13:24:30	UME	CHKH	DXCC17B	1,000.00
11.03.2014	13:24:30	UME	CHKH	DXCC16B	1,000.00

My selection screen input as

Input
Source loc	UHA	UME
Dest loc	ABDH	CHKH
Date	10.03.2014	11.03.2014

Output must be

Source and destination and date wise latest time records shoul be extracted by SELECT Statment.

Expected output as below

Output
DATE	TIME	SOURCE	DEST	PRD	QTY
10.03.2014	13:24:56	UHA	ABDH	DXCC17B	3,000.00
10.03.2014	13:24:56	UHA	ABDH	DXCC16B	1,000.00
11.03.2014	13:24:30	UHA	ABDH	DXCC17B	1,000.00
11.03.2014	13:24:30	UME	CHKH	DXCC17B	1,000.00
11.03.2014	13:24:30	UME	CHKH	DXCC16B	1,000.00

Thanks

Ajit

Re: ABAP SQL query on

vigneshwar_reddy — Fri, 14 Mar 2014 13:23:22 GMT

Hi Ajit,

You can use statement like below.

select * from table up to 5 rows into corresponding fields of it_table

where XXXXXXXXXXX

order by date descending time descending.

Regards,

Vigneshwar

Re: ABAP SQL query on

Former Member — Fri, 14 Mar 2014 21:13:58 GMT

Hi Ajit,

As Vigneshwar puts it:

select date time source dest prd qty

from <table> up to 5 rows

into corresponding fields of <internal table>

where date in sel_date

and source in sel_source

and dest in sel_dest

order by date descending time descending.

Re: ABAP SQL query on

Former Member — Mon, 17 Mar 2014 12:07:15 GMT

Hi,

I don't want records in descending order. If you see my example give above, you can find the no. of records in the database table and my expected results are different.

I want based on the input selection (i.e. Input Date wise) it should fetch the latest time records, then it should fetch all records of latest time.

So In my input i have given date range as 10 & 11. and For the date 10 and 11th, ther are 2 and 3 different time set of records are there. Finally it should getch latest time records for 10th and 11th date.

Regards,

Ajit

Re: ABAP SQL query on

Former Member — Mon, 17 Mar 2014 13:30:22 GMT

Hi,

thanks for attending my query .

I got the answer & it is working. I Will check further with more records and cases.

select * from <database table>

into table <Internal table>

where source in s_source

                   and desti in s_dest
                   and date in s_date
                   and time in ( select max( time ) from <database table> group by date ).

Regards,

Ajit

Re: ABAP SQL query on

RaymondGiuseppi — Mon, 17 Mar 2014 13:43:03 GMT

Did you find my answer(s) in select entire fields with aggregate functions (max,...) and similar threads.

Regards,
Raymond

Re: ABAP SQL query on

asim_isik — Mon, 17 Mar 2014 13:51:44 GMT

SORT itab by DATE SOURCE DEST PRD QTY.

DELETE ADJACENT DUPLICATES FROM itab comparing DATE SOURCE DEST PRD QTY.