We help IT Professionals succeed at work.

SQL searh issue

Hello all

I have this SQL where i search all data entered since 01/01/2009.

But i want to extract all last "ff.access_id," added in the database based on "ff.effective_dt," since 01/01/2009


Last records of certain "ff.access_id," may be from last year. But this is what i'm trying to do.


select 
            ff.carrier_id,
            ff.effective_dt,
            ff.region_id,
            ff.access_id,
            ff.chg_metric,
            ff.charge_cur,
            ff.charge,
            ff.min_cur,
            ff.min_charge,
            ff.max_cur,
            ff.max_charge,
            ff.access_key,
            rd.low_key_value,
            rd.high_key_value          
from 
            ff_access_charges_detail ff,
            region_detail rd 
where 
            ff.region_id=rd.region_id,
            and ff.effective_dt >= to_date('01/01/2009','mm/dd/rrrr')

Open in new window


I just want to extract all ff.region_id but only the last effective date per ff.region_id.

How can i do this please?


Thanks for your help
Comment
Watch Question

Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Try this:
select 
            ff.carrier_id,
            ff.effective_dt,
            ff.region_id,
            ff.access_id,
            ff.chg_metric,
            ff.charge_cur,
            ff.charge,
            ff.min_cur,
            ff.min_charge,
            ff.max_cur,
            ff.max_charge,
            ff.access_key,
            rd.low_key_value,
            rd.high_key_value          
from 
            ff_access_charges_detail ff,
            region_detail rd,
	    (select max(effective_dt) as max_dt, region_id
	     from ff_access_charges_detail
             group by region_id) max_ff
where 
            ff.region_id=rd.region_id,
            and ff.effective_dt >= to_date('01/01/2009','mm/dd/rrrr')
            and ff.region_id = max_ff.region_id
	    and ff.effective_dt = max_ff.max_dt;

Open in new window


In case it doesn't work, please post some sample data from ff_access_charges, region_detail including required output. Thanks.
Hello gerwinjansen

Ok, i will try it Monday morning for sure.

But for now, i can still give you the extract of the data i have yesterday from my SQL:

select 
            ff.carrier_id,
            ff.effective_dt,
            ff.region_id,
            ff.access_id,
            ff.chg_metric,
            ff.charge_cur,
            ff.charge,
            ff.min_cur,
            ff.min_charge,
            ff.max_cur,
            ff.max_charge,
            ff.access_key,
            rd.low_key_value,
            rd.high_key_value          
from 
            ff_access_charges_detail ff,
            region_detail rd 
where 
            ff.region_id=rd.region_id,
            and ff.effective_dt >= to_date('01/01/2009','mm/dd/rrrr')

Open in new window


Do you see that it will work from it?
SQL-extract.xlsx
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Well, you posted in Oracle SQL :) Best if you test it on your database first. Btw, you didn't post the region_detail table. Let me know if it works for you on monday.
awking00Information Technology Specialist

Commented:
>>But i want to extract all last "ff.access_id," added in the database based on "ff.effective_dt," since 01/01/2009<<

>>I just want to extract all ff.region_id but only the last effective date per ff.region_id.<<

I'm a little confused by this. Do you want the access_id based on the last effective_dt greater than 1/1/2009 and the region_id based on the last effective_dt regardless of whether it's greater than 1/1/2009?

Also, what dbms are you using?
Hello

I want the last effective date of every access_id since 1/1/2009.

Actually, i don't use it but i have to sent an SQL to IT when i need an extract.

It's a long process since i don't have access and i cant test it by myself.

I think it is:Oracle 9i

I will ask IT and let you know.
Ok,

I just had the extract and the list is shorter but still have duplicate access_id with that SQL:

select 
            ff.carrier_id,
            ff.effective_dt,
            ff.region_id,
            ff.access_id,
            ff.chg_metric,
            ff.charge_cur,
            ff.charge,
            ff.min_cur,
            ff.min_charge,
            ff.max_cur,
            ff.max_charge,
            ff.access_key,
            rd.low_key_value,
            rd.high_key_value          
from 
            ff_access_charges_detail ff,
            region_detail rd,
	    (select max(effective_dt) as max_dt, region_id
	     from ff_access_charges_detail
             group by region_id) max_ff
where 
            ff.region_id=rd.region_id,
            and ff.effective_dt >= to_date('01/01/2009','mm/dd/rrrr')
            and ff.region_id = max_ff.region_id
	    and ff.effective_dt = max_ff.max_dt;

Open in new window



i will try to have some other info about the table.
awking00Information Technology Specialist

Commented:
See attached.
query.txt
Information Technology Specialist
Commented:
Sorry, I left off the filter for >= 1/1/2009
query.txt
Ok, Thanks

let me send that IT for extract. i will let you know the result.
Hello awking00

Yes, i only have the most effective date per access_id now. This is very good.

I'm just missing some fields not that i will had.

I will try like this:
select 
            ff.carrier_id,
            ff.effective_dt,
            ff.region_id,
            ff.access_id,
            ff.chg_metric,
            ff.charge_cur,
            ff.charge,
            ff.min_cur,
            ff.min_charge,
            ff.max_cur,
            ff.max_charge,
            ff.access_key,
            rd.low_key_value,
            rd.high_key_value from
            (select access_id, effective_dt,
            row_number() over (partition by access_id order by effective_dt desc) rn
          
from 
            ff_access_charges_detail
                 where effective_dt >= to_date('01/01/2009','mm/dd/rrrr'))

where 
            rn = 1;

Open in new window

Hello all

Thanks, now i have all the most effective access_ids extract.

Thanks again