Find order that has deliveries in multiple weeks I thought I had a simple run of the mill problem, but I can't manage to figure out how to slice it.  Assume I have a table of Deliveries that has the OrderNumber, the DeliveryNumber and the DeliveryDate.  The question I want to ask is "Which OrderNumbers have DeliverDates that all aren't in the same week" 

Here is a test script.

DECLARE @DeliveryTable Table
(
    OrderNumber INT,
    DeliveryNumber INT,
    DeliveryDate Date
)
;

INSERT INTO @DeliveryTable 
(OrderNumber, DeliveryNumber, DeliveryDate)
VALUES 
(1,300, '2020-04-27'),
(1,301, '2020-04-28'),
(1,302, '2020-04-30'),
(4,730, '2020-04-26'),
(4,731, '2020-04-28'),
(4,732, '2020-05-05')
;


My query would return OrderNumber={4} because the '2020-05-05' is not in the same week as '2020-04-28' and '2020-04-26'.  OrderNumber 1 would not be returned because all the deliveries are in the same week.  

I've thought about using a LEAD/LAG function, but there can be an arbitrary number of Deliveries so I wouldn't know how far to LEAD/LAG.  I was thinking about using some type of join, but I don't know what I would join on other than ( ..DATEPART(WK, first.DeliveryDate) &lt;&gt; DATEPART(WK, second.DeliveryDate)..) but that seems hamfisted to do over 1.4 million rows.
Try following query 

SELECT OrderNumber
FROM @DeliveryTable
GROUP BY OrderNumber
HAVING DATEPART(WEEK, MIN(DeliveryDate)) &lt; DATEPART(WEEK, max(DeliveryDate))


Calculating date-diff can lead to incorrect results because if we compare difference of Sunday and next Monday it will return 1, but these days are in different weeks.