# 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) <> DATEPART(WK, second.DeliveryDate)..)` but that seems hamfisted to do over 1.4 million rows.

• Do you mean same week number or calendar week? For example if you add to your list (5,733, '2020-12-31'), (5,733, '2020-01-01') then what would you expect? Should those be the same week?
– BCM
Apr 30, 2020 at 16:03

Try following query

``````SELECT OrderNumber
FROM @DeliveryTable
GROUP BY OrderNumber
HAVING DATEPART(WEEK, MIN(DeliveryDate)) < 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.

• It's worth noting that this would incorrectly report when DeliveryDate is 1 year apart. Probably not a concern, but should be noted. May 30, 2020 at 21:29

Here is an alternative to the answer by Artashes Khachtryan which would support delivery dates a year apart. If that is not a concern, it provides no other benefit:

``````SELECT OrderNumber
FROM @DeliveryTable
GROUP BY OrderNumber
HAVING COUNT(DISTINCT DATEADD(D, -DATEPART(Weekday, DeliveryDate), DeliveryDate)) > 1
``````