Need help from SQL guru! RRS feed

  • Question

  • I have this complex query to come up with and can't seem to get my head around it.

    The main table is Cases. Each case can have a series of Events, each of which is of an EventType

    Lets call the event types A, B and C

    These event types have a hierarchy such that event type A is the highest and C is the lowest (this hierarchy is not indicated by any table column)

    Each event type has zero to n events of that type, each event having a date/time.

    One particular event for each event type is an 'end event' which means that all events are finished for that event type.

    I hope you are still with me!

    Each event record has an FK pointing to the Case.

    What I need is a query that for each case will return the latest event and date where there is no 'end' event but in event type sequence. So for instance if there is an 'end' event for type A (or no Type A events at all), I must then go on to look at event type B. If there are 'end' events for all three event types (or no events at all), I should return nothing. If I do find an event type with no 'end' event, I must return the event with the most recent EventDate.



      CaseId (PK)


      EventId (PK)

      CaseId (FK)

      EventTypeId (FK)



      EventTypeId (PK)


      EndEventFlag (True/False)

    Wednesday, February 13, 2019 5:13 PM

All replies

  • Hi. Just curious... Is this for Access or SQL Server?
    Wednesday, February 13, 2019 5:19 PM
  • It's for an Access DB with a SQL back end.

    But I'd be happy to get a solution as an MS Access query.

    Thursday, February 14, 2019 2:10 AM
  • Try this - hopefully I understood your question correcly.  this assumes only 1 event per day or that eventdate is a timestamp

    Select caseid, maxeventdate
    Select caseid, eventid, eventypeid, maxeventdate
    From event E
    Inner join
    (Select caseid, max(eventdate) maxeventdate                        
    From event
    Group by caseid) m
    On e.caseid = m.caseid and e.eventdate = m.eventdate
    ) Z
    Inner join eventtype t
    On z.eventypeid = t.eventtypeid
    Where t.endeventflag = false

    this essentially....finds the max event date for each case (m); then joins (m) back to the event table to get the event id and event type id  for the case and max date (z).  finally Z joins the eventype table to only retrieve the rows in Z where end event type id is false

    Friday, February 15, 2019 4:02 PM
  • Too late guys, your opportunity for greatness is gone. I solved it myself with help of a co-worker.
     I'll post the solution when its finalized.
    Friday, February 15, 2019 4:52 PM
  • Hi, I gave you a helpful vote for trying but I can see your solution is not taking into account the hierarchy of event types (the A, B and C in my original email). If there is no 'end' event amongst the events for type A, the most recent type A date is taken. If there is an 'end' event for event type A, the process repeats for event type B and then event type C. The most recent date must always be from an event type with no 'end' event.
    Friday, February 15, 2019 4:59 PM