Today's question (by Kathi Kellenberger):

Calculate the Days Between Orders

SELECT CustomerID, OrderID, OrderDate, 
   DATEDIFF(DAY,OrderDate, LEAD(OrderDate,1,OrderDate) 
   OVER(PARTITION BY CustomerID ORDER BY OrderID)) AS DaysUntilNextOrder
FROM Sales;

SELECT CustomerID, OrderID, OrderDate, 
   DATEDIFF(DAY,OrderDate, LAG(OrderDate,-1,0) 
   OVER(PARTITION BY CustomerID ORDER BY OrderID)) AS DaysUntilNextOrder
FROM Sales;

SELECT O.CustomerID, O.OrderID, O.OrderDate, 
   ISNULL(DATEDIFF(DAY,O.OrderDate,NextOrder.OrderDate),0) AS DaysUntilNextOrder
FROM Sales AS O
OUTER APPLY (
   SELECT TOP(1) OrderDate 
   FROM Sales AS I
   WHERE I.CustomerID = O.CustomerID 
      AND I.OrderID > O.OrderID
   ORDER BY I.OrderID) AS NextOrder;

Think you know the answer? Click here, and find out if you are right.

Using TOP with ROW_NUMBER

Which query will generate ten “random” sample rows from the Sales.SalesOrderDetail table (AdventureWorks2017) with new ID numbers (NewOrderID) that continue after the highest SalesOrderID in the table? In other words, if the highest SalesOrderID is 75123, then the NewOrderID in the rows returned will be between 75124 and 75133.

Query #1

SELECT TOP(10) SalesOrderID, ProductID, OrderQty, 
ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MAX(SalesOrderID) OVER() AS NewOrderID
FROM Sales.SalesOrderDetail AS SOD
ORDER BY NEWID();

Query #2

WITH SalesID AS (
SELECT TOP(10) SalesOrderID,  ProductID, OrderQty,  
            MAX(SalesOrderID) OVER() AS MaxID
FROM Sales.SalesOrderDetail AS SOD 
ORDER BY NEWID())
SELECT TOP(10) SalesOrderID, ProductID, OrderQty,  
        ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MaxID AS NewOrderID
FROM SalesID;

Query #3

SELECT SalesOrderID, ProductID, OrderQty, 
ROW_NUMBER() OVER(ORDER BY SalesOrderID) 
        + MAX(SalesOrderID) OVER() AS NewOrderID 
FROM Sales.SalesOrderDetail 
WHERE ROW_NUMBER() OVER(ORDER BY SalesOrderID) <= 10;

Answer: Query #2

Explanation: By ordering by NEWID(), you will get a random-ish (enough for our purposes) set of rows each time you run the query to produce the sample. You can find the last SalesOrderID by using a window aggregate MAX(SalesOrderID) OVER(). Window aggregates allow you to use an aggregate function even though the query is not an aggregate query. To continue the sequence of IDs, it’s possible to use ROW_NUMBER which returns a list of integers beginning with 1. Because the row numbers are generated before TOP is applied in Query #1, the query will return sample rows, but the row numbers, and therefore NewOrderID numbers, returned will not be the ones you are looking for. Here is a sample: Query #2 just won’t run because you cannot filter the row numbers directly in the WHERE clause. Query #1 is the answer. By placing the query to return the ten rows in a common table expression (CTE), you can apply the row numbers afterwards. (You could also do this with a temp table, table variable, or derived table subquery instead of a CTE.) In this case, the sample rows have the NewOrderID numbers that you need. Ref: Expert T-SQL Window Functions in SQL Server 2019: The Hidden Secret to Fast Analytic and Reporting Queries 2nd ed.

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.