SQL Find most recent date from 2 columns, AND identify which column it was in

Question

I have a table called tblAssetsInUse with the following structure:

intPK      intAssetID      datCheckedOut     datCheckedIn
1          450             1/5/2009          10/5/2009
2          300             2/5/2009          <NULL>
3          200             2/5/2009          <NULL>
4          450             12/5/2009         5/7/2009

and I have a SP that receives a scanned Asset ID then either Inserts, or Updates to the table for assets being checked Out or In respectively. As you can see datCheckedIn may be Null which is used to work out which assets are currently in use. This procedure works perfectly. I wish to be able to determine what the last asset to be scanned was and also what the last operation to the table was (i.e. Check In or Out). I have some SQL code which finds the row with the most recent date (regardless of which column) and I then use this to join to a separate Assets View, which also works. I just need to be able to work out if the most recent date was in the Checked Out or Checked In column somehow.

SELECT TOP (1) allDates.intPK, MAX(allDates.datLastAction) AS datLastScan,    dbo.viwAssets.strFriendlyName, tblAssetsInUse_join.intAssetID
FROM (SELECT intPK, MAX(datCheckedOut) AS datLastAction
      FROM dbo.tblAssetsInUse AS tblAssetsInUse_out
      GROUP BY intPK

      UNION ALL

      SELECT intPK, MAX(datCheckedIn) AS datLastAction
      FROM dbo.tblAssetsInUse AS tblAssetsInUse_in
      GROUP BY intPK) AS allDates 
INNER JOIN
dbo.tblAssetsInUse AS tblAssetsInUse_join ON allDates.intPK = tblAssetsInUse_join.intPK 
INNER JOIN
dbo.viwAssets ON tblAssetsInUse_join.intAssetID = dbo.viwAssets.intPK
GROUP BY allDates.intPK, dbo.viwAssets.strFriendlyName, tblAssetsInUse_join.intAssetID
ORDER BY datLastScan DESC

Is there a literal value of some kind I can add in so that it flags a bit value in the results perhaps?

Thanks for your help,

Paul Reynolds

Answer 1

One way would be to use a CASE statement and SELECT either IN or OUT depending on whether the MAX date value is IN or OUT

Answer 2

Try this as your inner query:

SELECT intPK, 'Out', MAX(datCheckedOut)
FROM dbo.tblAssetsInUse
GROUP BY intPK

UNION

SELECT intPK, 'In', MAX(datCheckedIn)
FROM dbo.tblAssetsInUse
GROUP BY intPK

Edited to add: A slicker solution would be to create a Greater user defined function that compares two dates.

Answer 3

Beside from getting the type of operation performed, I think that the whole query could be simplified (and optimized) this way:

SELECT TOP 1 allDates.intPK, allDates.datLastAction AS datLastScan, allDates.operation, dbo.viwAssets.strFriendlyName, tblAssetsInUse_join.intAssetID
FROM (SELECT TOP 1 intPK, intAssetID, datCheckedOut AS datLastAction, 'Out' AS operation
  FROM dbo.tblAssetsInUse AS tblAssetsInUse_out
  ORDER BY datCheckedOut DESC

  UNION ALL

  SELECT TOP 1 intPK, intAssetID, datCheckedIn AS datLastAction, 'In' AS operation
  FROM dbo.tblAssetsInUse AS tblAssetsInUse_in
  ORDER BY datCheckedIn DESC) AS allDates 
INNER JOIN
  dbo.viwAssets ON allDates.intAssetID = dbo.viwAssets.intPK
ORDER BY datLastScan DESC

EDIT: also note that by removing the TOP 1 in the first line you get both the last Asset checked in and the last checked out.

Answer 4

This should work. If you end up with two items checked in or out at identical times you will get multiple rows of course.

SELECT
     intPK,
     intAssetID,
     datCheckedOut,
     datCheckedIn,
     CASE
          WHEN datCheckedIn IS NOT NULL THEN 'Checked In'
          ELSE 'Checked Out'
     END AS last_action
FROM
     tblAssetsInUse AIU
INNER JOIN
     (
          SELECT
               MAX(datCheckedOut) AS max_in,
               MAX(datCheckedIn) AS max_out
          FROM
               tblAssetsInUse
     ) SQ ON
     CASE
          WHEN max_in > max_out THEN max_in
          ELSE NULL
     END = AIU.datCheckedIn OR
     CASE
          WHEN max_out > max_in THEN max_out
          ELSE NULL
     END = AIU.datCheckedOut