Re: Brain freeze doing Date comparisons

From: Jared Still <jkstill_at_gmail.com>
Date: Thu, 26 Feb 2009 10:14:40 -0800
Message-ID: <bf46380902261014p5df1351es1294db454f352f1c_at_mail.gmail.com>

If I understand the question properly, here's a first take on it.

drop table bill;

create table bill (

        dep_id    number,
        update_date timestamp

)
/

begin

        for i in  0..9
        loop
                insert into bill values (i,current_timestamp - ( ( (10-i) /
24))+(1/24));
        end loop;

end;
/

select * from bill;

with maxkey as (

        select max(dep_id) dep_id
        from bill

)
select b.dep_id, b.update_date
from bill b, maxkey m
where b.dep_id = m.dep_id
and b.update_date >= (systimestamp-(1/24)) /

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

On Thu, Feb 26, 2009 at 6:57 AM, Bill Ferguson <wbfergus_at_gmail.com> wrote:

> Hi all,
>
> Dealing with management has made my brain freeze and I'm stumped.
>
> I have a table as :
> dep_id number -- my primary key for most tables in system
> xml_clob --manually generated clob field
> update_date timestamp -- when this record was added
> updated_by varchar2(30) --userid of person
>
> Management wants a 'robust' auditing system in place, so my approach
> is if a piece of data in any table for a particular record has been
> changed or added, etc., then I'll copy my current xml_clob field from
> my master search table (which is always up-to-date) into this other
> table. But, I only want to do it if this hasn't been done within the
> last hour. So, in essence, at the most, I'll only have one record in
> this table for every 60 minutes.
>
> So, my problem is selecting only the most current record in this table
> (if one exists), and checking to see if it was added within the last
> 60 minutes.
>
> This is for Oracle 11 on Windows.
>
> Thanks for your help.
>
> --
> -- Bill Ferguson
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 26 2009 - 12:14:40 CST