We help IT Professionals succeed at work.

SQL searh issue

Wilder1626
Wilder1626 asked
on
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



How can i do this please?


Thanks for your help
Comment
Watch Question

Data Warehouse / Database Architect
Commented:
Hi Wilder,

If this is really DB2 SQL, you don't need the to_date function.  That's typically an Oracle requirement.

If the SQL is otherwise correct (I'm not sure what you need to do), just change the filter to:

where
            ff.region_id=rd.region_id,
            and ff.effective_dt >= '01/01/2009'


DB2 will automatically convert the string to a date object.


Good Luck,
Kent
Sorry, you are right!

This is an Oracle.

Yes, this SQL already work but it pulls all data entered since 2009, even it there is duplicate ff.region_id but with different date ff.effective_dt.

I just want to extract all ff.region_id but only the last effective date per ff.region_id.
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Hello Wilder1626, this question is the same as http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_27476959.html - you've posted it twice :) I'll ask a moderator to delete this questionhttp://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_27476959.html

@Kdo - you could post your remark here if you like.
Thanks Kdo for your help.

Really appreciate