SQL query for time difference for tracking performance

It is an status-driven application-tracking software. Some of the basic tables in the module are:

dbo.Application with columns ApplicationID, FirstName, LastName, Email, ApplicationTimestamp, CurrentStatusID
dbo.ApplicationStatusHistory with columns ApplicationID, StatusID, StatusName, StatusTimestamp, isCurrent (1/0)

I want to check the efficiency in processing new applications and asked for a report that indicate how long does an application stays in the initial status (let’s assume “Application Received”) before it is moved to a second status (it can be many things, e.g. “Reject”, “Scheduled Interview” etc) for all applications received in year 2020.

I tried something as below but it's wrong.

SELECT
    T1.Application_ID,
    T1.FirstName,
    T1.LastName,
    T1.ApplicationTimestamp AS 'Application Received On',
    T2.StatusID AS 'Current Status',
    DATEDIFF(DAY, T2.StatusTimeStamp, T1.ApplicationTimestamp) AS 'Processing Time (Days)',
    DATEDIFF(HOUR, T2.StatusTimeStamp, T1.ApplicationTimestamp) AS 'Processing Time (Hours)',
    DATEDIFF(MINUTE, T2.StatusTimeStamp, T1.ApplicationTimestamp) AS 'Processing Time (Minutes)'
FROM
    dbo.Application T1
LEFT JOIN
    dbo.ApplicationStatusHistory T2
ON
    T1.Application_ID = T2.Application_ID
WHERE
    T1.ApplicationTimestamp BETWEEN '2020-01-01' AND '2020-12-31'
    AND T1.CurrentStatusID <> T2.StatusID

Solution

Without knowing more of the data structure, it appears you have some status field that identifies the "stage" of the data from application received to schedule an interview. You may want to do multiple JOINs of root table based on each respective "status" ID.

So, lets take some premise that your status codes have some consecutive sequence context

1 = Application Received
2 = Reviewed
3 = Rejected
4 = Interview
5 = Hired

If the application is entered with a status of 1, then it would never have anything in the history/change log table correct? So, if you can't find anything in history, its a new application. Once changed to "Reviewed" status (2), the Received (1) gets put into history and so on. Is that accurate? OR, will there ALWAYS be an entry in the status history table, even on the very first entry when received. If so, that would simplify some as you go.

SELECT
      T1.Application_ID,
      T1.FirstName,
      T1.LastName,
      T1.ApplicationTimestamp AS 'Application Received On',
      -- now compute differences...  Only doing days, but you get the idea
      DATEDIFF(DAY, coalesce( Received.Application_ID, now(),
                    T1.ApplicationTimeStamp ) 
         as TimeSinceReceived,

      -- if there is a reviewed record, there should at least be the
      -- application original entry too
      case when Reviewed.Application_ID IS NULL
           then 0
           else 
      DATEDIFF(DAY, coalesce( Reviewed.StatusTimeStamp, now() ),
                    coalesce( Received.StatusTimeStamp, T1.ApplicationTimeStamp )) end
         as TimeToReview,


      -- if there is a Rejected record, there should at least be the
      -- application original entry too
      case when Rejected.Application_ID IS NULL
           then 0
           else 
      DATEDIFF(DAY, coalesce( Rejected.StatusTimeStamp, now() ),
                    coalesce( Reviewed.StatusTimeStamp, T1.ApplicationTimeStamp )) end 
         as TimeToReject

      etc... for interviewed and hired as applicable

   FROM
      dbo.Application T1
         LEFT JOIN dbo.ApplicationStatusHistory Received
            ON T1.Application_ID = Received.Application_ID
            -- Status application received
            AND Received.StatusID = 1

         LEFT JOIN dbo.ApplicationStatusHistory Reviewed
            ON T1.Application_ID = Reviewed.Application_ID
            -- Status application Reviewed
            AND Reviewed.StatusID = 2

         LEFT JOIN dbo.ApplicationStatusHistory Rejected
            ON T1.Application_ID = Rejected.Application_ID
            -- Status application Rejected
            AND Rejected.StatusID = 3

         LEFT JOIN dbo.ApplicationStatusHistory Interview
            ON T1.Application_ID = Interview.Application_ID
            -- Status application Interviewed
            AND Interview.StatusID = 4

         LEFT JOIN dbo.ApplicationStatusHistory Hired
            ON T1.Application_ID = Hired.Application_ID
            -- Status application Hired
            AND Hired.StatusID = 5
   WHERE
      T1.ApplicationTimestamp BETWEEN '2020-01-01' AND '2020-12-31'