Get Max(Time) if Max(Date) is same date 1

[cwsstins]

I've built the following code to find records with more than 1 "tsqcallid". These are duplicates and I need to find the earliest record to delete it. The code below works great if the duplicate records have a different tsqClosedDate value. However, if they are the same, I need to be able to find the "NOT IN MAX(tsqClosedTime)". I'm having trouble figuring out how to use either CASE or IF,THEN to do this...any help appreciated.

SELECT CallID
        FROM Detail d1
         WHERE tsqCallID IN 
          (
           SELECT tsqCallID 
            FROM Detail 
             WHERE tsqCallID IS NOT NULL
              GROUP BY tsqCallID HAVING COUNT(*) > 1
           )
         AND tsqClosedDate NOT IN 
          (
           SELECT MAX(tsqClosedDate) 
            FROM Detail d2
             WHERE d2.tsqCallID = d1.tsqCallID
           )

 

[Beilstwh]

What is the date and time columns are they both date? or a combination of date and seconds from midnight. Once we have that information, the solution should be fairly easy.

Bill
Oracle DBA/Developer
New York State, USA

 

[cwsstins]

Sorry, they are actually both varchar2.
Standard format is:
tsqClosedDate 2007-02-12
tsqClosedTime 16:27:32

 

[Beilstwh]

I won't yell at you for storing date information in a character field, but try the following

SELECT CallID
FROM Detail d1
WHERE tsqCallID IN
(
SELECT tsqCallID
FROM Detail
WHERE tsqCallID IS NOT NULL
GROUP BY tsqCallID HAVING COUNT(*) > 1
)
AND to_date(tsqClosedDate||tsqClosedtime,'yyyy-mm-ddhh24:mi:ss') NOT IN
(
SELECT MAX(to_date(tsqClosedDate||tsqClosedtime,'yyyy-mm-ddhh24:mi:ss'))
FROM Detail d2
WHERE d2.tsqCallID = d1.tsqCallID
)

Bill
Oracle DBA/Developer
New York State, USA

 

[cwsstins]

Thanks for not yelling...there's really nothing I can do about it because it's inherent to the application. But your suggestion works...thanks much!

 

[cwsstins]

I've come across a glitch in our application where some records are being created with the exact same tsqClosedDate||tsqClosedTime.

For records where this is the case, I need to identify the earliest record (and therefore the one to delete) by using a column on another table.

The CallLog table has a one-to-one relationship with Detail on the CallID field. There is a "DTLastMod" timestamp field (number) on CallLog and I want to identify the record(s) that is not MAX(DtLastMod).

This is what I'm trying, but it's not working. I think I'm having trouble getting a handle on the relationship between the two tables.

SELECT CallID
        FROM Detail d1
         WHERE tsqCallID IN
          (
           SELECT tsqCallID
            FROM Detail
             WHERE tsqCallID IS NOT NULL
              GROUP BY tsqCallID HAVING COUNT(*) > 1
           )
      AND 
 [b]      (
        (
         to_date(tsqClosedDate||tsqClosedtime,'yyyy-mm-ddhh24:mi:ss') NOT IN
          (
           SELECT MAX(to_date(tsqClosedDate||tsqClosedtime,'yyyy-mm-ddhh24:mi:ss'))
            FROM Detail d2
             WHERE d2.tsqCallID = d1.tsqCallID
           ) 
         )
       OR
        (
         to_date(tsqClosedDate||tsqClosedtime,'yyyy-mm-ddhh24:mi:ss') IN
          (
           SELECT MAX(to_date(tsqClosedDate||tsqClosedtime,'yyyy-mm-ddhh24:mi:ss'))
            FROM Detail d2
             WHERE d2.tsqCallID = d1.tsqCallID
           ) 
          AND
           CallID IN 
            SELECT CallID
	     FROM CallLog
              WHERE DTLastMod < MAX(DTLastMod)
               AND CallID = d1.CallID
         )
        )[/b]