3

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 at 16:03
2

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.

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

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 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.