SQL Server - Selecting Most Recent Record to generate a list of changes

Question

Here is the situation:

I have a database of 'tickets', and we track changes to the tickets each time they are saved. I am specifically looking at status changes, which track with the following format:

STATUS:{FROM}:{TO}

with {FROM} and {TO} changing to the respective statuses. What I need to do is generate numbers by weeks of the amount of tickets that were 'open' (meaning in draft status) at the end of any given week, say for the past 12 weeks. However, you are not limited to 'closing' a ticket and then reopening it, or making multiple changes in a single week.

So, what I need to do is modify the SQL below to ONLY consider the most recent "action" for any given entry. This way we avoid the problem of having entries that were 'closed' appear in the open count because they had been opened earlier.

SELECT track.historyID
      FROM RS_HistoryTracker track 
     WHERE (track.action = 'STATUS:INITIAL:DRAFT'
            OR track.action = 'STATUS:DELETED:DRAFT'
            OR track.action = 'STATUS:DRAFT:DRAFT')
       AND track.trackDateTime <= @endOfWeek

However, this statement is contained within another select statement, and is used to generate a complete list of history items:

    SELECT COUNT(DISTINCT his.historyID) AS theCount
  FROM RS_History his
 WHERE his.historyID IN 
       (SELECT track.historyID
          FROM RS_HistoryTracker track 
         WHERE (track.action = 'STATUS:INITIAL:DRAFT'
                OR track.action = 'STATUS:DELETED:DRAFT'
                OR track.action = 'STATUS:DRAFT:DRAFT')
           AND track.trackDateTime <= @endOfWeek)

So how do I make the inner select consider only the most recent tracked 'action' that occured up to or on the endOfWeek date? HistoryTracker contains a datetime stamp column.

Answer 1

As a starter you can find the last history item for each ticket by doing something like this

select * from
(
    --find max history id for each ticket
    select 
        T1.ticketId, 
        max(T1.historyId) As LastHistoryId
    from #Ticket T1
    --add WHERE clause to filter out dates
    group by 
       T1.ticketId
) MaxTicket

inner join 
#Ticket T2 --find the ticket so you can get the status
on MaxTicket.ticketId = T2.ticketId 
and MaxTicket.LastHistoryId=T2.Historyid

You may want to change how you find the latest ticket to be based on the date rather than the history id.

Answer 2

The are many variations of this question floating around on stackoverflow, here is the first I found :)

sql-query-to-get-most-recent-row-for-each-instance-of-a-given-key

Essentially you do need to do it in two parts.
- Use one query to find the most recent timestamp per item
- Use another query to do the work you set out to do

To Find all items still open by a given date:

SELECT
  [data].*
FROM
  track AS [data]
WHERE
  [data].trackDateTime =
    (
       SELECT
          MAX(trackDateTime)
       FROM
          track
       WHERE
          track.ticketID = [data].ticketID
          AND track.DateTime < @endOfWeek
    )
  AND track.action IN ('STATUS:INITIAL:DRAFT','STATUS:DELETED:DRAFT','STATUS:DRAFT:DRAFT'))

This assumes that ticketID the unique identifier for each ticket. (Based on one of your comments)

Answer 3

Will work with SQL Server 2005+:

WITH history AS (
  SELECT rh.historyID,
         MAX(rh.action) 'action'
    FROM RS_HISTORYTRACKER rh 
   WHERE rh.action IN ('STATUS:INITIAL:DRAFT', 'STATUS:DELETED:DRAFT', 'STATUS:DRAFT:DRAFT')
     AND rh.trackDateTime <= @endOfWeek)
SELECT COUNT(DISTINCT t.historyID) AS theCount
  FROM RS_HISTORY t
  JOIN history h ON h.historyi = t.historyid

Alternate, non-CTE using query:

SELECT COUNT(DISTINCT t.historyID) AS theCount
  FROM RS_HISTORY t
  JOIN (SELECT rh.historyID,
            MAX(rh.action) 'action'
          FROM RS_HISTORYTRACKER rh 
         WHERE rh.action IN ('STATUS:INITIAL:DRAFT', 'STATUS:DELETED:DRAFT', 'STATUS:DRAFT:DRAFT')
          AND rh.trackDateTime <= @endOfWeek) h ON h.historyi = t.historyid

Answer 4

Looks like this works:

  SELECT query.historyID
    FROM
    (SELECT MAX(track.trackID) AS maxTrackID, track.historyID
    FROM RS_HistoryTracker track
    WHERE track.trackDateTime <= '2009-08-06 23:59:59'
      AND track.action LIKE 'STATUS:%'
    GROUP BY historyID) AS query, RS_HistoryTracker track
    WHERE track.historyID = query.historyID
      AND track.trackID = query.maxTrackID
      AND track.action LIKE 'STATUS:%:DRAFT'