Haris Haris - 1 year ago 28
SQL Question

Trying to retrieve the most recent previous entry in a table

I am trying to retrieve the most recent entry in a table that's past a certain Event. The main table this concerns contains an ID for a user, the kind of event that happened as well as a timestamp.

This is where I am currently at:

SELECT cc.RefNo
,cc.Subcase
,ev.EventDate
,evt.EventCode
,ev.Amount
,previousevent.EventCode as [previous Event]
,previousevent.previousdate as [date previous event]
FROM event ev JOIN EventTemplate evt ON ev.EventTemplateID = evt.ID
JOIN CCase cc ON cc.ID = ev.CCaseID
JOIN (SELECT MAX(ev.EventDate) as previousdate, evt.EventCode, CCaseID
FROM Event ev JOIN EventTemplate evt ON ev.EventTemplateID = evt.ID
WHERE (evt.EventCode LIKE 'SI%' OR evt.EventCode LIKE 'SM%')
Group by CCaseID, evt.EventCode) as previousevent
ON previousevent.CCaseID = cc.ID
AND previousevent.previousdate < ev.EventDate


The output for this is giving me ALL previous Events (though no dupes for individual events, I assume). I am now looking for a way to get to the most recent one for each combination of RefNo, Subcase, EventDate and EventCode.

Answer Source

Not tested, but perhaps the WITH TIES clause in concert with Row_Number() may help here

SELECT Top 1 with ties
       cc.RefNo
      ,cc.Subcase
      ,ev.EventDate 
      ,evt.EventCode
      ,ev.Amount
      ,previousevent.EventCode as [previous Event]
      ,previousevent.previousdate as [date previous event]
  FROM event ev JOIN EventTemplate evt ON ev.EventTemplateID = evt.ID
  JOIN CCase cc ON cc.ID = ev.CCaseID 
  JOIN (SELECT MAX(ev.EventDate) as previousdate, evt.EventCode, CCaseID
                            FROM Event ev JOIN EventTemplate evt ON ev.EventTemplateID = evt.ID
                            WHERE (evt.EventCode LIKE 'SI%' OR evt.EventCode LIKE 'SM%')
                            Group by CCaseID, evt.EventCode) as previousevent
  ON previousevent.CCaseID = cc.ID
  AND previousevent.previousdate < ev.EventDate
  Order By Row_Number() over (Partition By RefNo, Subcase, EventCode Order By EventDate Desc)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download