Home » SQL & PL/SQL » SQL & PL/SQL » Max Time for the Max date (Oracle 10g Sql*plus & Reports )
Max Time for the Max date [message #428207] Tue, 27 October 2009 08:41 Go to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
I'm trying to select the Maximum time from the maxium date.

The possible entries to select from are:


SM18-205540C (VERSION 1)	14-Sep-09	1600
SM18-205540C (VERSION 1)	14-Sep-09	2000
SM18-205540C (VERSION 1)	16-Sep-09	1000
SM18-205540C (VERSION 1)	16-Sep-09	1400
SM18-205540C (VERSION 1)	17-Sep-09	1000
SM18-205540C (VERSION 1)	17-Sep-09	1400
SM18-205540C (VERSION 1)	18-Sep-09	1000





The Query i have so far is this:


select 
max(ops$penlims.get_colltime_num(b.sample_Id)), 
max(ops$penlims.get_colldate(b.sample_Id)),
a.method

from nais_tasks a, nais_samples b

where a.sample_id = b.sample_id  --Primary Keys
and b.user_sampleid like 'ELM STREET (TB) ZONE I'  
and ops$penlims.get_colldate(b.sample_id) between '14-SEP-09' AND '18-SEP-09'
and a.method = 'SM18-205540C (VERSION 1)'
group by a.method
/


This is returning

2000 18-Sep-09 SM18-205540C (VERSION 1)

which is not what i want since there is no such entry. It appears that the query is grabbing the max date and time independantly of each other. I want it to return:

SM18-205540C (VERSION 1) 18-Sep-09 1400

Which is the LATEST TIME FROM THE LATEST DATE.

Any Suggestions?

Thank you for your time and help.

Regards,

Mike M

[EDITED by LF: fixed [code] tags]

[Updated on: Tue, 27 October 2009 08:44] by Moderator

Report message to a moderator

Re: Max Time for the Max date [message #428208 is a reply to message #428207] Tue, 27 October 2009 08:47 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Have you got the date and time in seperate columns by any chance?
Because if you've got the time in the date column the max time for the max date would be the max date.
Re: Max Time for the Max date [message #428210 is a reply to message #428207] Tue, 27 October 2009 08:47 Go to previous messageGo to next message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I couldn't find "returning" nor "desired" records in "possible entries" list, but - you might try with something like
select max(time_column)
from your_table
where date_column = (select max(date_column)
                     from your_table
                    )
Re: Max Time for the Max date [message #428212 is a reply to message #428207] Tue, 27 October 2009 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As we have not the code of your functions, how could we answer?

Translate your problem into simple test case and post it.

Regards
Michel

[Updated on: Tue, 27 October 2009 08:50]

Report message to a moderator

Re: Max Time for the Max date [message #428214 is a reply to message #428208] Tue, 27 October 2009 09:01 Go to previous messageGo to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
No, They are 2 seperate colums (actually functions).

I tried concatonating them with ||' ' || to see if i could grab the max that way. Did not work.
Re: Max Time for the Max date [message #428215 is a reply to message #428210] Tue, 27 October 2009 09:02 Go to previous messageGo to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
I need to have the Date and Time Functions returned in the select statement as they are being "data linked" to a results query.
Re: Max Time for the Max date [message #428216 is a reply to message #428207] Tue, 27 October 2009 09:04 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I can see you've got two functions, question is how is the raw data stored - one column or two?
Re: Max Time for the Max date [message #428217 is a reply to message #428215] Tue, 27 October 2009 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 27 October 2009 14:49
As we have not the code of your functions, how could we answer?

Translate your problem into simple test case and post it.

Regards
Michel

Re: Max Time for the Max date [message #428220 is a reply to message #428212] Tue, 27 October 2009 09:06 Go to previous messageGo to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
The 2 functions return a date (DD-MON-YY) and time in (24HHMM) format. Didn't think one needed the code for these functions to solve this but here we go:

FUNCTION GET_COLLTIME (p_sample_id IN NUMBER)
RETURN NUMBER
AS
v_retCollTime NUMBER := 0.0009;
BEGIN
  select to_number(a.text_value)
  into v_retCollTime
  from nais_sample_attributes a
  where upper(a.name) like 'COLLECTION TIME'
  and a.sample_id = p_sample_id;

  --If (v_retCollTime like '0009' OR v_retCollTime like '%-%') then
  --  return ('N/A');
  --else
    return v_retCollTime;
  --end if;  
EXCEPTION
  WHEN OTHERS Then
    return v_retCollTime;
END;



And for the date Function:


FUNCTION GET_COLLDATE (isample_id in NUMBER)
RETURN DATE IS
   v_date DATE := to_date('07/04/1776','MM/DD/YYYY');
BEGIN

   SELECT to_date(text_value,'DD-MON-YYYY')
   into v_date
   
   from nais_sample_attributes
   where sample_id = isample_id
   and upper(name) = 'COLLECTION DATE'
   and text_value like '__-___-____';
   return v_date;
EXCEPTION
   when others then
           return v_date;

END;



Re: Max Time for the Max date [message #428221 is a reply to message #428220] Tue, 27 October 2009 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The way to post a question is:
Quote:
Translate your problem into simple test case and post it.

Regards
Michel
Re: Max Time for the Max date [message #428222 is a reply to message #428216] Tue, 27 October 2009 09:07 Go to previous messageGo to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
Raw data is stored as 2 seperate columns in an attribute table.

Re: Max Time for the Max date [message #428226 is a reply to message #428207] Tue, 27 October 2009 09:14 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
We needed the functions to see how the data is stored and it's stored in a nasty generic data model.

I can't see any easy way to restrict the time by the given date. I certainly don't think you can do it with the existing functions.

We need more details of the data model - create tables and insert statements, because at the moment I can't actually see any correlation between date and tme.
Re: Max Time for the Max date [message #428256 is a reply to message #428226] Tue, 27 October 2009 10:35 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
I see that your time function returns a number, but your date function returns a date (not a string, so it won't have the format DD-MON-YY as you claim..)

What you could do to get the complete date and then do the max on that, is to do some calculations using your number.

mydate + (trunc(mytime/100)/24) + (mod(mytime, 100)/(24*60))


This is horribly ugly though. You should think about storing your data in a different way.. Also it will not work in the cases where you return 0.0009 as the time. (Why this number?) You'll need to make sure that the time you return can actually be interpreted in the way you claimed (HH24MI)
Re: Max Time for the Max date [message #428258 is a reply to message #428207] Tue, 27 October 2009 11:00 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as I can see from the information provided there is a more fundamental problem.
The only link between date and time appears to be the sample_id.
So for any given sample_id I can't see any way to determine which times go with which dates.
Re: Max Time for the Max date [message #428283 is a reply to message #428256] Tue, 27 October 2009 13:49 Go to previous messageGo to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
Thank you for your thoughful response.


You are right. It used to return it as a varchar before i changed it Confused

When it was a varchar, '0009' by default meant that there was no collection time written on the receiving bottle and therefore had no value to be entered in at the time of bottle receipt. I tried (albeit weakly) to keep this the case by making 0.0009 the number that meant default. Not very well thought out as i would try fixing that after i have this max date + time issue sorted out.


I used your calculations on my functions and it worked! Sorta...

When i run the following code:

select ops$penlims.get_colldate(b.sample_id), (trunc(ops$penlims.get_colltime_num(b.sample_Id)/100)/24)  + (mod(ops$penlims.get_colltime_num(b.sample_Id), 100)/(24*60)) ftime
from nais_tasks a, nais_samples b
where a.sample_id = b.sample_id
and ops$penlims.get_colldate(b.sample_id) between '14-SEP-09' AND '18-SEP-09'
--and ops$penlims.get_colltime(b.sample_id) not like '%00'
--and ops$penlims.get_colltime(b.sample_id) not like '9999'
and b.user_sampleid like 'ELM STREET (TB) ZONE I'
and a.condition in ('APPROVED')
and a.method = 'SM18-205540C (VERSION 1)'
order by ftime
/


I get:

OPS$PENLI| FTIME
---------|----------
16-SEP-09|.416666667
16-SEP-09|.416666667
17-SEP-09|.416666667
17-SEP-09|.416666667
18-SEP-09|.416666667
18-SEP-09|.416666667
16-SEP-09|.583333333
16-SEP-09|.583333333
18-SEP-09|.583333333
18-SEP-09|.583333333
17-SEP-09|.583333333
17-SEP-09|.583333333
14-SEP-09|.666666667
14-SEP-09|.666666667
14-SEP-09|.833333333
14-SEP-09|.833333333

16 rows selected.

If I Run:
select ops$penlims.get_colldate(b.sample_id) + (trunc(ops$penlims.get_colltime_num(b.sample_Id)/100)/24)  + (mod(ops$penlims.get_colltime_num(b.sample_Id), 100)/(24*60)) ftime
from nais_tasks a, nais_samples b
where a.sample_id = b.sample_id
and ops$penlims.get_colldate(b.sample_id) between '14-SEP-09' AND '18-SEP-09'
--and ops$penlims.get_colltime(b.sample_id) not like '%00'
--and ops$penlims.get_colltime(b.sample_id) not like '9999'
and b.user_sampleid like 'ELM STREET (TB) ZONE I'
and a.condition in ('APPROVED')
and a.method = 'SM18-205540C (VERSION 1)'
order by ftime
/


I get:

FTIME
---------
14-SEP-09
14-SEP-09
14-SEP-09
14-SEP-09
16-SEP-09
16-SEP-09
16-SEP-09
16-SEP-09
17-SEP-09
17-SEP-09
17-SEP-09
17-SEP-09
18-SEP-09
18-SEP-09
18-SEP-09
18-SEP-09

16 rows selected.


The calculation as is, doesnt seem to like that I'm adding a number to a date and it returns just returns a date. Maybe the number has been added and i cant see it in the present formatting?

I'm not sure if this select statement is something that can have the Max Function performed on it. Or more specifically, if i can 'group by' this crazy calculation.

Once again THANK YOU!!


Re: Max Time for the Max date [message #428285 is a reply to message #428258] Tue, 27 October 2009 14:01 Go to previous messageGo to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
These values are stored together on attribute table:
select distinct name, text_value from nais_sample_attributes
where sample_id = 200669614;


The out put is this:

NAME |TEXT_VALUE
------------------------------------------------|----------------------
Collected By |2108
COMMENT2 |*NULL*
Collection Point |RAW/HOP
Miscellaneous |N/A
Date Received |19-DEC-2007 16:35:53
Received By |KDAVIS
Minutes Operated |30
COMMENT1 |*NULL*
Hours Operated |0
Filter |N/A
Collection Date |14-DEC-2007
Collection Time |1130
Prod / BTW |BTW
Address |*NULL*
Treatment Type |N/A
Bin Temperature OK |Y

16 rows selected.



The Describe on this table looks as follows:


Name Null? Type
------------
SAMPLE_ID NOT NULL NUMBER(10)
NAME VARCHAR2(40)
TEXT_VALUE VARCHAR2(255)
HIDDEN CHAR(1)



Maybe that helps? Maybe not Confused
Re: Max Time for the Max date [message #428289 is a reply to message #428285] Tue, 27 October 2009 14:27 Go to previous messageGo to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
Using the following code:

select max((trunc(ops$penlims.get_colltime_num(b.sample_Id)/100)/24)  + (mod(ops$penlims.get_colltime_num(b.sample_Id), 100)/(24*60))) as ftime
from nais_tasks a, nais_samples b
where a.sample_id = b.sample_id
and ops$penlims.get_colldate(b.sample_id) between '14-SEP-09' AND '18-SEP-09'
--and ops$penlims.get_colltime(b.sample_id) not like '%00'
--and ops$penlims.get_colltime(b.sample_id) not like '9999'
and b.user_sampleid like 'ELM STREET (TB) ZONE I'
and a.condition in ('APPROVED')
and a.method = 'SM18-205540C (VERSION 1)'
and operation is not null
order by ftime
group by ftime


I return:
group by ftime
*
ERROR at line 12:
ORA-00933: SQL command not properly ended


It works fine without trying top use the Max Function:

select (trunc(ops$penlims.get_colltime_num(b.sample_Id)/100)/24)  + (mod(ops$penlims.get_colltime_num(b.sample_Id), 100)/(24*60)) as ftime
from nais_tasks a, nais_samples b
where a.sample_id = b.sample_id
and ops$penlims.get_colldate(b.sample_id) between '14-SEP-09' AND '18-SEP-09'
--and ops$penlims.get_colltime(b.sample_id) not like '%00'
--and ops$penlims.get_colltime(b.sample_id) not like '9999'
and b.user_sampleid like 'ELM STREET (TB) ZONE I'
and a.condition in ('APPROVED')
and a.method = 'SM18-205540C (VERSION 1)'
and operation is not null
order by ftime
/


Any Suggestions?

[Updated on: Tue, 27 October 2009 14:30]

Report message to a moderator

Re: Max Time for the Max date [message #428292 is a reply to message #428289] Tue, 27 October 2009 14:41 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>from nais_tasks a, nais_samples b
NAIS_TASKS should not be part of FROM clause since it contributes no data to SELECT clause

>and ops$penlims.get_colldate(b.sample_id) between '14-SEP-09' AND '18-SEP-09'

With Oracle characters between single quote marks are STRINGS!
'This is a string, 2009-12-31, not a date'
When a DATE datatype is desired, then use TO_DATE() function.

Re: Max Time for the Max date [message #428293 is a reply to message #428292] Tue, 27 October 2009 14:45 Go to previous messageGo to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
It has and will continue to bring in other data from the select clause...

I will be bringing a.method into the select clause once i know how to solve my issue.

AND

and a.condition in ('APPROVED')
and a.method = 'SM18-205540C (VERSION 1)'

is from the tasks table Shocked
Re: Max Time for the Max date [message #428296 is a reply to message #428293] Tue, 27 October 2009 15:03 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
SELECT   Max((Trunc(ops$penlims.Get_colltime_num(b.sample_id) / 100) / 24) + (Mod(ops$penlims.Get_colltime_num(b.sample_id),
                                                                                  100) / (24 * 60))) ftime
FROM     nais_samples b
WHERE    b.sample_id IN (SELECT a.sample_id
                         FROM   nais_tasks a
                         WHERE  a.condition IN ('APPROVED')
                                AND a.method = 'SM18-205540C (VERSION 1)')
         AND ops$penlims.Get_colldate(b.sample_id) 
             BETWEEN To_date('14-SEP-2009','DD-MON-YYYY') AND To_date('18-SEP-2009','DD-MON-YYYY')
         AND b.user_sampleid = 'ELM STREET (TB) ZONE I'
         AND operation IS NOT NULL
ORDER BY 1 

[Updated on: Tue, 27 October 2009 15:12]

Report message to a moderator

Re: Max Time for the Max date [message #428298 is a reply to message #428296] Tue, 27 October 2009 15:21 Go to previous messageGo to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
That works great but i'm still unable to format/code it correctly so i can select more than just ftime.

This query is supposed to select Method with the Max Colltime fromt he Max Colldate. Then,i can use oracle reports and datalink a results query.

I'm so close i can feel it!!! Very Happy


Thanks for the help!!!
Re: Max Time for the Max date [message #428299 is a reply to message #428298] Tue, 27 October 2009 15:30 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Re: Max Time for the Max date [message #428301 is a reply to message #428207] Tue, 27 October 2009 16:03 Go to previous messageGo to next message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
Thank you for the lesson blackswan.

I have read the section and ask you to read my post again and answer that question (post message #428289)

Please dont re-arrange my queries and confuse my question.


Sorry i sound snippy but i dont think i'm doing anything wrong here.

Have a goodnight Blackswan.



Re: Max Time for the Max date [message #428302 is a reply to message #428283] Tue, 27 October 2009 16:37 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
Quote:
The calculation as is, doesnt seem to like that I'm adding a number to a date and it returns just returns a date. Maybe the number has been added and i cant see it in the present formatting?


Adding a number to a date will increment the date by x days (where x is the number you're adding). This is why the hour part of the time given was divided by 24, and the minute part was divided by 24*60. You will then find the number of days to add (for instance 6 hours would give 0.25) to a date with a time of 00:00 to get the right time of day in it.

The date datatype in Oracle always contains a time portion. Use to_char on it in the select to see the time portion as well.
for instance: to_char(mydate, 'DD-MON-YYYY HH24:MI')
Re: Max Time for the Max date [message #428548 is a reply to message #428302] Wed, 28 October 2009 14:38 Go to previous message
mike1976
Messages: 26
Registered: June 2005
Location: Happaugue, NY
Junior Member
Thank you.


I ended up creating a function that returns the string as a char string (using to_char). In the SQL*Query, I then converted the function to a date again using to_date. I performed the Max command on this new function and it worked great!


Thank everyone very much for their time and help!!

PROBLEM SOLVED!!!

Mike
Previous Topic: updating a LONG column (merged)
Next Topic: Assistance With Script for Monthly DB & EBS Sessions.
Goto Forum:
  


Current Time: Thu Sep 29 07:21:43 CDT 2016

Total time taken to generate the page: 0.16615 seconds