sql - SQL 按数据范围内的日期组分组

Question

在sql中查询如下表的最佳方法是什么？

对于给定的,我需要通过 , 来确定PackListGrp变量（PICKONLY或JITPICK）。expectedshipdatedeliverymethodOrderID

当expectedshipdate每个订单项在 2 天内，则PackListGrpfor 将是JITPICK。

当expectedshipdateis > 2 天时， PickDeliveryMethod将是PickOnly并且 thePSIEST将是JITPICK。

基本上我只是想按预期发货日期确定订单行项目。

谢谢

OrderID ExpectedShipDate    Sku     DeliveryMethod  PackListGrp
66064   13-Sep-18         22-81506  Pick            PickOnly
66064   13-Sep-18         878-85487 Pick            PickOnly
66064   13-Sep-18         5-4878-   Pick            PickOnly
66064   13-Sep-18         3-020-02  Pick            PickOnly
66064   13-Sep-18         7-209-23  Pick            PickOnly
66064   13-Sep-18         7-206-05  Pick            PickOnly
66064   13-Sep-18         7-305-08  Pick            PickOnly
66064   13-Sep-18         7-567-20  Pick            PickOnly
66064   18-Sep-18         6-104-24  Pick            JITPICK
66064   19-Sep-18         TCC_KS    PSIEST          JITPICK
66064   17-Sep-18         TCF-DK    PSIEST          JITPICK

标签： sqlsql-server

Answer 1

你只是想要一个case表情？

select t.*,
       (case when datediff(day, t. expectedshipdate, getdate()) <= 2
             then 'JITPICK'
             else 'PICKONLY'
        end) as packlistgrp
from t;