Databases
StackOverflow2013
Postsdbo
POSQL Find most recent date from 2 columns, AND identify which column it was in
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Find most recent date from 2 columns, AND identify which column it was in
    text
    copied!<p>I have a table called tblAssetsInUse with the following structure:</p> <pre><code>intPK intAssetID datCheckedOut datCheckedIn 1 450 1/5/2009 10/5/2009 2 300 2/5/2009 &lt;NULL&gt; 3 200 2/5/2009 &lt;NULL&gt; 4 450 12/5/2009 5/7/2009 </code></pre> <p>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.</p> <pre><code>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 </code></pre> <p>Is there a literal value of some kind I can add in so that it flags a bit value in the results perhaps?</p> <p>Thanks for your help,</p> <p>Paul Reynolds</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload