Re: Problem with date query - simple?

Don,

So, what you are saying is:

Until today's data is completely collected (e.g. after 10 PM) show me yesterday's data (between times X and Y). Once today's data is complete, start collecting for tomorrow and show me data collected today.

My first thought is you could look at using two tables:

1. Today's data -- set up for quick collection (e.g. OLTAP like).
2. Previous data -- set up for data mining / warehousing.

During day-end close, you would merge the today's data into the previous data and then truncate today's data. (If you are running a 24x7 OLTAP envrionment, it isn't this simple. For some reason I get the feeling you're doing a POS or at least where business stops and you can do end-of-day processing in a quiet mode.)

This "design" would give you several performance options and might make your SQL select / report easier. Using this method, you only need to look in previous data. The decision on "nearest current time/otherwise grab the data from tonight" is NOT in your select, but in when the merge process occurs.

Are these thought going down the same path you are thinking?

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"Don A." <DBA_at_direct.ca> wrote in message news:ie8a4vgrucuoa8babcs8jrkqqc9177t8vk_at_4ax.com...
> Greetings,
>
> I'm trying to select records from a table based on a date field and can't
seem
> to figure out how to set up variable date checking. This select statement
is in
> a view so it needs to be able to determine the current time and then
request
> either today's data or yesterday's. (All of this is on 8.1.7.2.5 under
W2K)
>
> Basically I want all records between a certain time (say 6 PM and 10 PM)
from
> the nearest current time.
>
> That is, if it's currently before 10 PM I want the data from last night,
> otherwise grab the data from tonight.
>
> I can put in a set date and time manually :
>
> i.e. ... where RDATE between ('08-FEB-03 18:00:00','DD-MM-YY hh24:mi:ss')
> and ('08-FEB-03 22:00:00','DD-MM-YY hh24:mi:ss')
>
> But obviously it means the select statement is static. How can I check the
> current time and then pass this to the where clause so it requests the
> appropriate days data?
>
> Any help, pointers, suggestions appreciated.
>
> Thanks,
>
> Don
Received on Sat Feb 08 2003 - 15:27:05 CST