Problem with select data. Help! RSS

• • Reply
  

  ctee

  Member

  115 Points

  250 Posts

  Problem with select data. Help!

  May 21, 2012 03:12 AM|LINK

  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

• • Reply
  

  ruipedromach...

  Member

  258 Points

  84 Posts

  Re: Problem with select data. Help!

  May 21, 2012 07:24 AM|LINK

  ..and what are you trying to acomplish exacly?

  RuiMachado
• • Reply
  

  Lannie

  Contributor

  3736 Points

  727 Posts

  Re: Problem with select data. Help!

  May 21, 2012 08:55 PM|LINK

  /* 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 Young Yang - MSFT on May 31, 2012 01:35 PM