时间戳

首页 > 解决方案 > 如果两者都为空，则返回两个日期或特定文本中的较低者

sql-server - 如果两者都为空，则返回两个日期或特定文本中的较低者

问题描述

我试图从两个字段中找到较低的日期。该字段也可以为 NULL，因此请选择非 NULL，或者如果两者都为 NULL，则选择“某些错误”

POdate 从中填充（我将它与其他字段一起放在临时表中）

    (SELECT MIN(PD.ReceiptDate)
FROM Structure.Parts
INNER JOIN Purchase.PurchaseOrderDetails PD   ON pd.PartID = structure.Parts.PartID    
INNER JOIN Purchase.PurchaseOrders PO     
ON PD.PurchaseOrderNumber = PO.PurchaseOrderNumber 
WHERE Structure.Parts.Partnumber = sp.PartNumber AND (PO.PurchaseOrderStatusCode < 4) --4 Complete 5 --Cancelled       
AND (PD.ReceiptStatusID IN (1,2) )) AS POdate,

AND WOdate 像这样（我把它和其他字段一起放在临时表中）

(SELECT MIN(wt.CompletionDate)
FROM Structure.Parts
INNER JOIN Production.WorksOrder as wo     
ON  wo.PartID = Structure.Parts.PartID  
INNER JOIN Production.WorksOrderTransfers  wt ON wt.WorksOrderNumber = wo.WorksOrderNumber  
WHERE Structure.Parts.Partnumber = sp.PartNumber AND wt.WorksOrderStatusCode < 4      
AND (wt.BatchQuantity - ISNULL (wt.QuantityStored,0)) > 0 )
 AS WOdate,

我的案例陈述在这里

CASE
WHEN POdate IS NULL THEN CAST(WOdate AS varchar (25))
WHEN WOdate IS NULL THEN CAST (POdate AS varchar (25))
WHEN POdate > WOdate THEN CAST (WOdate AS varchar (25)) 
WHEN POdate < WOdate THEN CAST (POdate AS varchar (25)) 
ELSE 'No Planned Stock'

END AS StockDueIn

任何指针将不胜感激

标签： sql-serverdatereporting-servicescase

解决方案

这应该涵盖所有可能的情况

CASE
WHEN POdate IS NULL AND WOdate IS NULL
    THEN 'No Planned Stock'
--If desired output for BOTH EQUAL case is 'No Planned Stock'
--replace the above WHEN with below one
--WHEN ISNULL(POdate, '99990101') = ISNULL(WOdate, '99990101')
    --THEN 'No Planned Stock'
WHEN ISNULL(POdate, '99990101') > ISNULL(WOdate, '99990101')
    THEN CAST (WOdate AS varchar (25)) 
WHEN ISNULL(POdate, '99990101') < ISNULL(WOdate, '99990101')
    THEN CAST (POdate AS varchar (25)) 
END AS StockDueIn

推荐阅读