Oracle:  Query help to get earliest date value

Maliki Hassani
Maliki Hassani used Ask the Experts™
on
Experts,

This may be simple and I thought I could use the MIN function but it isn't work the way I want it to. (Showing the incorrect original Severity)

Overview:I have a table "SD_AUDIT" that reports on all ticket status changes.  The objective is to capture the orginal severity staus.  Since there can be multiple changes to the severity, (Critical,Urgent,High,Medium, Low).  I need to use the earliest date and grab the "NEW_VALUE" field which shows thee status (Critical,High,etc).  Using table "SD_AUDIT" I am using the field "Field_Affected" = 'Severity' to narrow down my search.

Issue:  I thought that MIN function when placed around the CREATE_DATE, it would grab the earliest date, then MIN the NEW_VALUE.  I am not getting the results I am wanting.

Here are the table details:
Query to show table:

SELECT UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') AD DATE_CREATED,NEW_VALUE AS ORIGINAL_SEVERITY,TICKET_ID
                FROM ARADMIN.SD_AUDIT
                  WHERE UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= TO_DATE('01/01/2012','MM/DD/YYYY')
                    AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
                    AND Field_Affected = 'Severity'
                    AND (TICKET_ID IN 'BHN-477221')

OUTPUT:
DATE_CREATED      ORIGINAL_SEVERITY      TICKET_ID
2/28/2012 6:14:19 PM      Medium      BHN-477221
2/28/2012 6:32:52 PM      High      BHN-477221
2/28/2012 7:02:26 PM      Urgent      BHN-477221



Query with MIN:
SELECT MIN(UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern')) AS DATE_CREATED,MIN(NEW_VALUE) AS ORIGINAL_SEVERITY,MIN(TICKET_ID)
                FROM ARADMIN.SD_AUDIT
                  WHERE UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= TO_DATE('01/01/2012','MM/DD/YYYY')
                    AND UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
                    AND Field_Affected = 'Severity'
                    AND (TICKET_ID IN 'BHN-477221')

OUTPUT: NOT SHOWING "MEDIUM"
DATE_CREATED      ORIGINAL_SEVERITY      MIN(TICKET_ID)
2/28/2012 6:14:19 PM      High      BHN-477221

Please help!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What you are doing in your query is taking separately the minimum value of the date and the minimum value of severity.

Instead you should rank the records according to the date and then take the complete record with the earliest date. Something like this:

SELECT
  date_created
  ,new_value
  ,ticket_id
FROM
(SELECT
  UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern')) AS date_created
  ,new_value
  ,ticket_id
  ,RANK() OVER(PARTITION BY ticket_id ORDER BY create_date) AS rnk
FROM
  ARADMIN.SD_AUDIT                
WHERE
  UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') >= TO_DATE('01/01/2012','MM/DD/YYYY')
AND
UDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern') < (TRUNC(SYSDATE) - INTERVAL '0' DAY)
AND
  Field_Affected = 'Severity'
AND
  (TICKET_ID IN 'BHN-477221')
) asd
WHERE
  rnk = 1
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Does UDF_CONVERT_UNIX_DATETIME return a varchar2 or a date?

Looks like it is doing an ASCII sort and '2/28/2012 6:14:19 PM' is the min using ascii values.

Can you change the function to retunr a date?  Then MIN will work.

Otherwise try:

to_char(MIN(to_dateUDF_CONVERT_UNIX_DATETIME(CREATE_DATE, 'US/Eastern'),'MM/DD/YYYY HH:MI:SS AM')),'MM/DD/YYYY HH:MI:SS AM')
Most Valuable Expert 2011
Top Expert 2012

Commented:
SELECT *
  FROM (SELECT   udf_convert_unix_datetime(create_date, 'US/Eastern') AS date_created,
                 new_value AS original_severity,
                 ticket_id
            FROM aradmin.sd_audit
           WHERE     udf_convert_unix_datetime(create_date, 'US/Eastern') >=
                         TO_DATE('01/01/2012', 'MM/DD/YYYY')
                 AND udf_convert_unix_datetime(create_date, 'US/Eastern') <
                         (TRUNC(SYSDATE) - INTERVAL '0' DAY)
                 AND field_affected = 'Severity'
                 AND (ticket_id IN 'BHN-477221')
        ORDER BY create_date)
 WHERE ROWNUM = 1
Most Valuable Expert 2011
Top Expert 2012
Commented:
alternate version

SELECT date_created, original_severity, ticket_id
  FROM (SELECT udf_convert_unix_datetime(create_date, 'US/Eastern') AS date_created,
               new_value AS original_severity,
               ticket_id,
               ROW_NUMBER() OVER (ORDER BY create_date) rn
          FROM aradmin.sd_audit
         WHERE     udf_convert_unix_datetime(create_date, 'US/Eastern') >=
                       TO_DATE('01/01/2012', 'MM/DD/YYYY')
               AND udf_convert_unix_datetime(create_date, 'US/Eastern') <
                       (TRUNC(SYSDATE) - INTERVAL '0' DAY)
               AND field_affected = 'Severity'
               AND (ticket_id IN 'BHN-477221'))
 WHERE rn = 1

Author

Commented:
Wow, that works..  I knew what I was doing wrong but didn't know how to explain it.  I just learned something!  Thanks Experts!
Most Valuable Expert 2011
Top Expert 2012

Commented:
I suggest tosse get part of the split

RANK will work like ROW_NUMBER in this query

his example also demonstrates how you would get the earliest record for each ticket.
It's not necessary here because the where clause already restricts to a single ticket, but it doesn't hurt

Author

Commented:
I agree..  I actually thought I included him.
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial