How to convert a query to use joins?

Question

I want to write a query that uses 'joins concepts' to return, for each order, the number of days that passed since the same customer's previous order.

I am using the AdventureWorks2017 database, table Sales.SalesOrderHeader

I tried the following code, but i actually want it through joins concept:

select customerid,
    orderdate,
    salesorderid,
    datediff(day, 
            (select top(1) s1.orderdate 
             from  sales.SalesOrderHeader s1
             where s1.CustomerID=s.CustomerID
                   and (s1.OrderDate = s.Orderdate 
                       and s1.SalesOrderID < s.Salesorderid
                       or  s1.OrderDate < s.orderdate)
             order by s1.orderdate desc,s1.SalesOrderID desc
             ),
            orderdate) as Daysdifference  
    from sales.SalesOrderHeader s  
    order by CustomerID,OrderDate,SalesOrderID

Answer 1

Starting from your existing query, a simple approach uses a lateral join:

select s.customerid, s.orderdate, s.salesorderid,
    datediff(day, s.orderdate, s1.orderdate) days_difference
from sales.salesorderheader s
cross apply (
    select top(1) s1.orderdate 
    from sales.salesorderheader s1
    where s1.customerid = s.customerid
    and (s1.orderdate < s.orderdate) or (s1.orderdate = s.orderdate and s1.id < s.id)
    order by s1.orderdate desc, s1.id desc
) s1

This answers your question as of how to implement the logic with a join. Let me pinpoint, however, that this would be more efficiently expressed with lag():

select customerid, orderdate, salesorderid,
    datediff(day, orderdate, lag(orderdate) over(partition by customer_id order by order_date, id) days_difference
from sales.salesorderheader s

Answer 2

I would write it like so

SELECT s.CustomerID,
       s.OrderDate,
       s.SalesorderID,
       datediff(day, s1.OrderDate, s.OrderDate) as Daysdifference  
FROM sales.SalesOrderHeader s
OUTER APPLY (
  SELECT top(1) s1.OrderDate-- maybe MAX(s1.OrderDate) ?
  FROM sales.SalesOrderHeader s1
  WHERE s1.CustomerID = s.CustomerID
    AND ((s1.OrderDate = s.OrderDate 
          AND s1.SalesOrderID < s.SalesOrderID) -- I've added parenthesis
       OR  s1.OrderDate < s.OrderDate)
  ORDER BY s1.OrderDate DESC, s1.SalesOrderID DESC
) s1
ORDER BY s.CustomerID, s.OrderDate, s.SalesOrderID

Or maybe something like that?

SELECT s.customerID,
       s.orderDate,
       s.SalesOrderID,
       datediff(day, MAX(s1.OrderDate), s.OrderDate) as Daysdifference  
FROM sales.SalesOrderHeader s
LEFT JOIN sales.SalesOrderHeader s1 ON s1.CustomerID = s.CustomerID
                                AND ((s1.OrderDate = s.OrderDate 
                                  AND s1.SalesOrderID < s.SalesOrderID) -- I've added parenthesis
                                OR  s1.OrderDate < s.OrderDate))
GROUP BY s.CustomerID, s.OrderDate, s.salesorderId
ORDER BY s.CustomerID, s.OrderDate, s.SalesOrderID