suppressing rows

[umag]

Hi Gurus
I have a question. I have a table which stores data like this. I receive entry for the same loan with all information same with latest effdt.

loannum acctnum dbt_crt_cd amount effdt

300015360 132206 10 110500.00 08/22/2003
300015360 176294 60 1105.00 08/22/2003
300015360 116540 60 111423.51 08/25/2003
300015360 422207 60 181.49 08/25/2003
300015360 176294 60 1105.00 08/25/2003
300015360 132206 10 110500.00 08/25/2003
300015360 116540 60 111423.51 08/22/2003
300015360 422207 60 181.49 08/22/2003

I want only 4 rows to be displayed in the report instead of 8 rows.

loannum acctnum dbt_crt_cd amount effdt

300015360 132206 10 110500.00 08/22/2003
(I don't want the above line on report as latest transaction on 08/25/2003 came for this)
300015360 176294 60 1105.00 08/22/2003
(I don't want the above on report as latest transaction 08/25/2003 came for this)
300015360 116540 60 111423.51 08/25/2003
300015360 422207 60 181.49 08/25/2003
300015360 176294 60 1105.00 08/25/2003
300015360 132206 10 110500.00 08/25/2003
300015360 116540 60 111423.51 08/22/2003
(I don't want the above line on report as latest transaction on 08/25/2003 came for this)
300015360 422207 60 181.49 08/22/2003
(I don't want the above on report as latest transaction 08/25/2003 came for this)


How can I do this impromptu. In sql I can write subquery like this

select c.loannum,g.acctnum,g.dbt_crt_cd,g.amount,g.effdt from stg_clsapp c,glinsert g
where c.losloanid = g.losloanid
and c.lststscd in ("FUND","PRCHS&quot
and g.report2msa not in ("Y","C&quot
and c.loannum=300015360
and g.effdt = ( select max(effdt) from glinsert g1
where g.losloanid = g1.losloanid)

Thanks in advance
Uma

 

[TexanDude]

Hi Uma,

Group the following columns and in the same order ..
loannum, acctnum, dbt_crt_cd, amount

Associate the effdt column to any one of the following ..
acctnum, dbt_crt_cd, amount

Sort "Desending" effdt column.

It should work for U.

It's a dirty trick ... but it works.

 

[nagrajm]

Uma

Do your normal impromptu report for this.

Then..

In the summary filter insert the following condition;

effdt = maximum(effdt) for loannum, acctnum

or

effdt = maximum(effdt) for loannum, acctnum, dbt_crt_cd

depending upon whether you want the latest effdt down to dbt_crt_cd or not.

Group or Sort your report on loannum, acctnum, dbt_crt_cd ASC.

Hope that helps.