locked
Problem with select data. Help! RRS feed

  • Question

  • User-322746205 posted

    Hi,

    I try to select data from my database where i will pull the data based on latest time and group by name.

    below are the sample data:

    ID   Name    Starttime                       Endtime

    1    AAA        1/5/2012 8.00am           1/5/2012 10.00a.m

    2    BBB          2/5/2012 9.00a.m

    3    CCC          2/5/2012 10.00a.m       2/5/2012 3.00pm

    4    AAA          2/5/2012  10.00a.m

    select ID, Name, Max(Start_Time), End_Time from chamber group by Name
    
    
    System will return me data as below:

    ID   Name       Starttime                       Endtime

    2    BBB          2/5/2012 9.00a.m

    3    CCC          2/5/2012 10.00a.m       2/5/2012 3.00pm

    4    AAA          2/5/2012  10.00a.m      1/5/2012 10.00a.m

    System will automatic replace the empty value on End_Time on 'AAA'

    Can someone help me to fix the error.

    Thanks

    Sunday, May 20, 2012 11:12 PM

Answers

  • User269602965 posted
    /* WHEN NAME is AAA AND the ENDTIME is NULL, replace with system date (date and time now) */
    
    SELECT
     ID,
     NAME,
     START_TIME
     CASE
       WHEN NAME = 'AAA' AND ENDTIME IS NULL THEN SYSDATE
       ELSE ENDTIME
     END AS ENDTIME  
    FROM
    (
    SELECT
      ID, 
      Name, 
      MAX(Start_Time) AS STARTTIME, 
      End_Time        AS ENDTIME
    FROM
      chamber
    GROUP BY
      Name
    ) a
    /  
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 21, 2012 4:55 PM

All replies

  • User1013750657 posted

    ..and what are you trying to acomplish exacly?

    Monday, May 21, 2012 3:24 AM
  • User269602965 posted
    /* WHEN NAME is AAA AND the ENDTIME is NULL, replace with system date (date and time now) */
    
    SELECT
     ID,
     NAME,
     START_TIME
     CASE
       WHEN NAME = 'AAA' AND ENDTIME IS NULL THEN SYSDATE
       ELSE ENDTIME
     END AS ENDTIME  
    FROM
    (
    SELECT
      ID, 
      Name, 
      MAX(Start_Time) AS STARTTIME, 
      End_Time        AS ENDTIME
    FROM
      chamber
    GROUP BY
      Name
    ) a
    /  
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 21, 2012 4:55 PM