Re: Problem with date query - simple?

Bertram,

We currently have several local and remote databases that are being used by internal and external clients / customers and I am recording (among other things) on-going connection information (what programs, machines. userids, etc) in a central database.

I already use these tables for current and historic reporting and they've worked well until now. Management now wants the ability to query the data directly and get information on clients connected between 6 PM and 6 AM. They felt they needed at least 4 hours of current data for their validity checking (hence the 6 PM to 10 PM requirement below), or else they wanted to see the previous nights information (which I was already reporting to them). So if management queries the database anytime after 10 PM they want to see current data. All I can say is GOD I hate management.

Anyway, due to other internal and external politics, the best way seemed to be creating a view and have it return the appropriate data. Because I fully expect them to change or expand these requirements, I didn't want to start creating new tables just to provide this information (I also thought it wouldn't be difficult to create a select statement to do this ;-)

I created a decode(sign(to_char(trunc... statement and it seems to work (I haven't done any further verification after it ran the first time), but boy is it ugly. I may just look at populating another table and getting the data that way, not really sure at this point.

Time to go have another pint or two, then I'll come up with something do to management . Oops, I meant for...

Cheers,

Don.

On Sat, 08 Feb 2003 21:27:05 GMT, "Bert Bear" <bertbear_at_NOSPAMbertbear.net> wrote:

>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 - 20:44:33 CST