My task is to write a query that returns all orders for customer with a CustomerID of 90, along with the order total and number of days since the customer’s previous order.
I am to use a correlated subquery to calculate the number of days since the previous order.
I am using the tables Sales.Orders and Sales.OrderLines in the WideWorldImporters Sample Database.
I am required to have columns OrderID, OrderDate, OrderTotal, and DaysSincePreviousOrder. The latter being the days since the previous order for the customer.
My attempt at a solution is starting to setup my query as follows:
SELECT
O.OrderID,
O.OrderDate,
ISNULL(SUM(OL.UnitPrice * OL.Quantity), 0.00) AS OrderTotal,
DATEDIFF(DAY,
(
SELECT MAX(OrderDate)
WHERE OrderDate < O.OrderDate
), O.OrderDate) AS DaysSincePreviousOrder
FROM Sales.Orders O
INNER JOIN Sales.OrderLines OL ON OL.OrderID = O.OrderID
AND O.CustomerID = 90
GROUP BY O.OrderID, O.OrderDate, O.CustomerID
ORDER BY O.OrderID;
I am not sure how to get the date of the customers previous order in a correlated subquery.
My top two results should look like:
**OrderID**--- **OrderDate** ------**OrderTotal** -----**DaysSincePreviousOrder**
1455 -------2013-01-29 -----365.00 --------- NULL
1890 -------2013-02-06 -----915.00 ----------8
Any help would be much appreciated.
Maybe this will help.
create table MyOrder ( OrderID int,
CustomerID int,
OrderDate date )
insert into MyOrder ( OrderID, CustomerID, OrderDate ) values
( 1, 9999, '2018-05-01T00:00:00.000' ),
( 2, 9999, '2018-05-10T00:00:00.000' ),
( 3, 9999, '2018-06-10T00:00:00.000' ),
( 4, 7777, '2018-07-01T00:00:00.000' ),
( 5, 7777, '2018-07-15T00:00:00.000' ),
( 6, 3333, '2018-09-09T00:00:00.000' )
select OrderID,
CustomerID,
OrderDate,
( select max(OrderDate)
from MyOrder
where CustomerID = O.CustomerID
and OrderDate < O.OrderDate )
as PreviousOrderDate
from MyOrder O
order by CustomerID, OrderDate