Querying The Most Recent Records

November 13, 2012    SQL

Here’s another fun solution for you business intelligence people out there. Suppose you wanted to pull data that was updated in the last 15mins based on when a row was last. I’m sure there are many ways to accomplish this but I like to use the DATEADD() function in Microsoft SQL Server 2008. Below is the query using the AdventureWorks2008R2 database enjoy.

SELECT I.ProductID, I.LocationID, I.Bin, I.Shelf, I.Quantity, P.ProductNumber, P.Name, I.ModifiedDate

FROM AdventureWorks2008R2.Production.ProductInventory AS I LEFT OUTER JOIN AdventureWorks2008R2.Production.Product AS P ON I.ProductID = P.ProductID

WHERE (I.ModifiedDate >= REPLACE(CONVERT(VARCHAR(19), DATEADD(minute,-30,GETDATE()), 120),'-', ‘/'))

P.S. Before the query in the example will work on the AdventureWorks database you’ll need to update a couple of rows on the ProductInventory table.