1. Categories >> Software >> Databases >> SQL PLSQL
  2. Suggest New Category

i have a customer table.
trans_id trans_date trans_amt debit_credit_indicator
001 01-JAN-13 1099 cr
001 12-JAN-13 500 db
002 24-FEB-13 400 db
002 23-MAR-13 345 cr
001 18-APR-13 800 cr
002 15-MAR-13 600 db
001 12-FEB-13 200 cr

i want like this output.

trans_id trans_amt debit_credit_indicator

i want get highest credit amount and lowest credit amount
and highest debit amount and lowest debit amount for each
trans_id.

Question Posted / ajit

Answer Posted / ajit

select ac_no, max(AC_STATUS) high,min(AC_STATUS) low,ac_branch
from DIM_ACCOUNt
where ac_branch = 'cr'
and ac_no = 1
group by ac_no, ac_branch
union
select ac_no, max(AC_STATUS) high,min(AC_STATUS) low,ac_branch
from DIM_ACCOUNt
where ac_branch = 'cr'
and ac_no = 2
group by ac_no, ac_branch
union
select ac_no, max(AC_STATUS) high,min(AC_STATUS) low,ac_branch
from DIM_ACCOUNt
where ac_branch = 'db'
and ac_no = 2
group by ac_no, ac_branch
union
select ac_no, max(AC_STATUS) high,min(AC_STATUS) low,ac_branch
from DIM_ACCOUNt
where ac_branch = 'db'
and ac_no = 1
group by ac_no, ac_branch;

Is This Answer Correct ?    0 Yes 0 No



Post New Answer       View All Answers


Please Help Members By Posting Answers For Below Questions

What are system versioned tables?

548

Why partition by is used in sql?

552

What is lexical units in pl sql?

565

What is sap sql?

532

What is pl/sql table? Why it is used?

556




What is an emotional trigger?

512

What are dml commands?

524

what are integrity rules?

567

What is oracle sql developer?

603

What is the difference between the conventional and direct path loads? : aql loader

689

What is a primary key example?

510

What is sql select statement?

503

Which software is used for pl sql programming?

515

What is a sql driver?

522

What is clustered index in sql?

582