SQL Query max id max date max time (sap maxdb 7.8)

Question

i have a hard time figuring out this one which is around a date/time problem. My Table (Service) got the following structure:

ShopID |   ServiceID | Datestart | Timeend   | StatusID   
1      |      100    | 2014-03-03 | 09:16:22 | 15  
1      |      200    | 2014-03-03 | 10:16:22 | 15  
2      |      300    | 2014-03-05 | 18:16:00 | 15  
2      |      400    | 2014-03-05 | 15:30:48 | 0  
3      |      500    | 2014-03-07 | 09:16:22 | 15  
4      |      550    | 2014-03-07 | 09:50:10 | 15

The goal is to get for each ShopID in 2014:

Basicaly the most recent ServiceID which can be either the highest (therefore newest) ServiceID, or the ServiceID with most recent Datestart and most recent Timeend. Also all Results need to be with StatusID = 15

This is what i have been using until now:

select max(ss.id), ss.shopid from service ss,
 (select shopid, max(datestart) as datestart
from service s
where
      statusid = 15 and
      datestart between '2014-01-01' and '2014-12-31'
      group by shopid) x
      where ss.shopid=x.shopid and ss.statusid = 15 and ss.datestart=x.datestart group by ss.shopid

This does fine for most recent ServiceID and Date, but i cannot get the TimeEnd into it. Hope anyone can help with this.

score 0 · Answer 1 · answered Mar 31 '14 at 14:57

I simply included the TimeEnd column in the sub-query as well as the join:

SELECT
  MAX(ss.id),
  ss.shopid
FROM
  service ss,
  (
    SELECT
      shopid,
      max(DATESTART) as DATESTART,
      MAX(TIMEEND) as TIMEEND
    FROM
      service s
    WHERE
      statusid = 15
    AND datestart BETWEEN '2014-01-01' AND '2014-12-31'
    GROUP BY
      SHOPID
  )
  X
WHERE
  ss.shopid     =x.shopid
AND ss.statusid = 15
AND SS.DATESTART=X.DATESTART
AND SS.TIMEEND=X.TIMEEND
GROUP BY
  ss.shopid;

SQL Query max id max date max time (sap maxdb 7.8)

1 Answers1