简化/修复长查询

Brandon 发表于 Dev

布兰登

我有如下查询：

SELECT Delivery.DeliveryNumber,
       DeliveryStatus.StatusName,
       Delivery.PickupDateTime,
       Delivery.DeliveryDateTime,
       Delivery.PackageWeight,
       Delivery.PackageSize,
       Delivery.PickupAddress1,
       CASE
           WHEN Delivery.DeliveryDateTime IS NULL THEN 'Not Delivered'
           WHEN (DATEDIFF(hh, Delivery.PickupDateTime, Delivery.DeliveryDateTime) > 0)
                AND (DATEDIFF(hh, Delivery.PickupDateTime, Delivery.DeliveryDateTime) < 24) THEN 'Delivered 24hrs After Pickup'
           WHEN (DATEDIFF(dd, Delivery.PickupDateTime, Delivery.DeliveryDateTime) = 0) THEN 'Delivered Same Day'
       END AS Status
FROM Delivery
INNER JOIN DeliveryStatus ON Delivery.StatusCode = DeliveryStatus.StatusCode

此查询的问题在于，状态现在可以具有NULL值。。我不想Status拥有NULLs。

所以我尝试添加：

WHERE (Status IS NOT NULL) 但我知道我仍然无法做到这一点..我不想这样做：

WHERE CASE
           WHEN Delivery.DeliveryDateTime IS NULL THEN 'Not Delivered'
           WHEN (DATEDIFF(hh, Delivery.PickupDateTime, Delivery.DeliveryDateTime) > 0)
                    AND (DATEDIFF(hh, Delivery.PickupDateTime, Delivery.DeliveryDateTime) < 24) THEN 'Delivered 24hrs After Pickup'
           WHEN (DATEDIFF(dd, Delivery.PickupDateTime, Delivery.DeliveryDateTime) = 0) THEN 'Delivered Same Day'

有什么方法可以清理查询？

我的任务是设计一个查询，使其返回满足以下条件的行：

已提货但尚未发货的包裹
提货后一天（少于24小时）内交付的包裹
与取件当天同一天寄出的包裹
提货前一天（24小时内）内已交付的包裹

我认为查询正确。我只需要帮助清理它，因为它看起来很脏。有任何想法吗？

编辑：添加了整个数据结构：

在此处输入图片说明

托斯滕·凯特纳

因此，您的问题是您不想对相同的逻辑进行两次编码（一次在where子句中，一次在select列表中）。您可以通过外部查询从已经拥有的行中选择所需的行来解决此问题：

SELECT *
FROM 
(
  SELECT Delivery.DeliveryNumber,
         DeliveryStatus.StatusName,
         Delivery.PickupDateTime,
         Delivery.DeliveryDateTime,
         Delivery.PackageWeight,
         Delivery.PackageSize,
         Delivery.PickupAddress1,
         CASE
             WHEN Delivery.DeliveryDateTime IS NULL THEN 'Not Delivered'
             WHEN (DATEDIFF(hh, Delivery.PickupDateTime, Delivery.DeliveryDateTime) > 0)
                  AND (DATEDIFF(hh, Delivery.PickupDateTime, Delivery.DeliveryDateTime) < 24) THEN 'Delivered 24hrs After Pickup'
             WHEN (DATEDIFF(dd, Delivery.PickupDateTime, Delivery.DeliveryDateTime) = 0) THEN 'Delivered Same Day'
         END AS Status
  FROM Delivery
  INNER JOIN DeliveryStatus ON Delivery.StatusCode = DeliveryStatus.StatusCode
)
WHERE Status IS NOT NULL;