SQL Server:Last Record Written

SQL Server:Last Record Written

In many situations you'll find yourself looking at date-specific data.  For example,
in the electronic trading industry, you may find yourself looking at 'trade date', or the datetime at which a particular trade was performed.  In the HFT (high frequency trading) world, it is all about when (and what)... the datetime attributes for the trading activity are very important for positions, trending and analysis, and many other things.

When was the last time a trade was submitted by a particular trading desk?


DECLARE @date DATETIME
SELECT @date = 
                         MAX(TradeDate) 
                         FROM dbo.TradeTable 
                         WHERE TradingDesk = 5   --Trading desks are numerically identified.
    SELECT
          TradeDate,TradingDesk,TraderName,Exchange,Symbol,Price,Quantity,OrderID
    FROM
          dbo.TradeTable
    WHERE
          TradingDesk = 5
          AND TradeDate = @date


Ok, let's get a little more clever, and create a stored procedure with input parameters:


   IF OBJECT_ID('usp_lasttradedetails','p')>0
   DROP PROC dbo.usp_LastTradeDetails
   GO
   CREATE PROC dbo.usp_LastTradeDetails (
       @desk INT
   )
   AS 
   SET NOCOUNT ON;
   /*
   Allows retrieval of the last trade written, for the given @tradingdesk.
   EXEC dbo.usp_LastTradeDetails @desk = 5
   */


  SELECT @date = 
                         MAX(TradeDate) 
                         FROM dbo.TradeTable 
                         WHERE TradingDesk = @desk


  SELECT
     TradeDate,TradingDesk,TraderName,Exchange,Symbol,Price,Quantity,OrderID
  FROM
     dbo.TradeTable
  WHERE
     TradingDesk = @desk
     AND TradeDate = @date


SET NOCOUNT OFF;