深圳智汇创想科技有限公司

根据订单状态和完成备注内容判断是否是完结订单状态

对应的SQL

SELECT TransportName,
                                       WareHouseName,
                                       ToWareHouseName,
                                       TrackNumbers,
                                       AmazonCompeteRemark,
                                       OrderStatus,
                                       AmazonDeliveryStatus,
                                       f.ShipmentId,
                                       a.OrderCode,
                                       a.ProductNum,
                                       a.ShipTime,
                                       f.QuantityReceived,
                                       f.ReceivedTime,
                                       (CASE
                                         WHEN ( a.ProductNum - Isnull(f.QuantityReceived, 0) ) > 0 THEN ( a.ProductNum - Isnull(f.QuantityReceived, 0) )
                                         ELSE 0
                                       END )                                                                             AS tz,
                                       Datediff(DAY, a.ShipTime, f.ReceivedTime)                                        AS TakeDay,
                                       Datediff(DAY, a.AddTime, a.ShipTime)                                             AS ShipmentDay,
                                       Datediff(DAY, a.ShipTime, f.ReceivedTime) + Datediff(DAY, a.AddTime, a.ShipTime) AS TotalDay,
                                       --SUM(a.AvgShippingFee) as AvgShippingFee,
                                       --SUM(a.ClearedQuantity) as ClearedQuantity,
                                       AmazonDestinationFulfillmentCenterId,
                                       PostCode,
                                       a.OrderSourceID,
                                       a.SellerSKU,
                                       a.PackageId,
                                       a.LogisticsOrderNo
                                FROM
(SELECT MIN(   CASE
                      WHEN ISNULL(p.ID, 0) > 0 AND ISNULL(p.TransportID,0)>0 THEN
                          pt.TransportName
                      ELSE
                          t.TransportName
                  END
              ) AS TransportName,
               Min(w.WareHouseName)                   AS WareHouseName,
               Min(rwto.WareHouseName)                AS ToWareHouseName,
               MIN(   CASE
                      WHEN ISNULL(p.ID, 0) > 0 AND ISNULL(p.TrackNumber,'')<>'' THEN
                          p.TrackNumber
                      ELSE
                          CASE
                              WHEN CHARINDEX(',', TrackNumbers) > 0 THEN
                                  LEFT(TrackNumbers, CHARINDEX(',', TrackNumbers) - 1)
                              ELSE
                                  TrackNumbers
                          END
                  END
              ) AS TrackNumbers,
               o.OrderSourceID,
               l.AmazonCompeteRemark,
               Min(l.AmazonDeliveryStatus)            AS AmazonDeliveryStatus,
               o.OrderStatus,
               l.SellerSKU,
               o.AmazonShipmentId,
               o.CustomerID,
               ( Sum(CASE w.WareHouseType
                       WHEN 1 THEN Isnull(d.ProductNum, 0)
                       ELSE ( Isnull(l.ProductNum, 0) + Isnull(l.ProductNumUpdate, 0) )
                       END) / Isnull(Min( CASE
                                  WHEN ISNULL(l.GroupSKU, '') <> ''
                                       AND (l.ProductNum + l.ProductNumUpdate) >= (ISNULL(l.GroupProductNum, 0)
                                                                                   + ISNULL(l.GroupProductNumUpdate, 0)
                                                                                  )
                                       AND (ISNULL(l.GroupProductNum, 0) + ISNULL(l.GroupProductNumUpdate, 0)) > 0 THEN
                              (l.ProductNum + l.ProductNumUpdate)
                              / (ISNULL(l.GroupProductNum, 0) + ISNULL(l.GroupProductNumUpdate, 0))
                                  ELSE
                                      1
                              END), 1) )ProductNum,
               o.OrderCode,
               o.ShipTime,
               o.AddTime,
               o.AmazonDestinationFulfillmentCenterId,
               o.PostCode,p.ID AS PackageId,p.LogisticsOrderNo
        FROM   RB_OrderInfo o WITH(nolock)
               LEFT JOIN RB_OrderList l WITH(nolock)
                 ON o.OrderCode = l.OrderCode
                    AND o.CustomerID = l.CustomerID
               LEFT JOIN RB_Procurement_Order_Package_OrderDetail d WITH(nolock)
                 ON d.OrderCode = o.OrderCode
                    AND d.CustomerID = o.CustomerID
                    AND d.OrderListID = l.ID
               --LEFT JOIN RB_Overseas_WareHouse_Product_PutIn_Log inlog WITH(nolock)
                 --ON d.CustomerID = inlog.CustomerId
                    --AND d.ID = inlog.PackageOrderDetailID
              
               LEFT JOIN RB_WareHouse w WITH(nolock)
                 ON o.CustomerID = w.CustomerID
                    AND o.WareHouseID = w.ID
               LEFT JOIN RB_WareHouse rwto (nolock)
                 ON o.ToWareHouseID = rwto.ID
                    AND o.CustomerID = rwto.CustomerID
               LEFT JOIN RB_Transport t WITH(nolock)
                 ON o.TransportID = t.ID
                    AND o.CustomerID = t.CustomerID
               LEFT JOIN RB_Procurement_Order_Package p WITH(nolock)
                 ON p.ID = d.PackageID
                    AND p.CustomerID = d.CustomerID
               LEFT JOIN dbo.RB_Transport (NOLOCK) pt
                ON p.TransportID = pt.ID
                   AND p.CustomerID = pt.CustomerID
              LEFT OUTER JOIN dbo.RB_Product_GroupSku AS g WITH (NOLOCK)
                 ON l.GroupSKU = g.GroupSku
                    AND l.CustomerID = g.CustomerID
                    AND l.SKU = g.SKU
        WHERE   LEN(o.AmazonShipmentId) > 0
               AND o.IsStockOrder=1
               --AND o.OrderSourceID = @OrderSourceID
               AND o.CustomerID = 1
               --AND l.SellerSKU = @OrderSourceSKU
               --AND rwto.WareHouseType =3
               AND o.OrderCode='202310071919154354849'
                  group by o.OrderSourceID,l.SellerSKU,l.SKU , CASE
                                  WHEN ISNULL(l.GroupSKU, '') <> ''
                                       AND (l.ProductNum + l.ProductNumUpdate) >= (ISNULL(l.GroupProductNum, 0)
                                                                                   + ISNULL(l.GroupProductNumUpdate, 0)
                                                                                  )
                                       AND (ISNULL(l.GroupProductNum, 0) + ISNULL(l.GroupProductNumUpdate, 0)) > 0 THEN
                              (l.ProductNum + l.ProductNumUpdate)
                              / (ISNULL(l.GroupProductNum, 0) + ISNULL(l.GroupProductNumUpdate, 0))
                                  ELSE
                                      1
                              END,o.AmazonShipmentId,o.CustomerID,o.OrderCode,o.ShipTime,o.AddTime,o.OrderStatus,l.AmazonCompeteRemark,o.AmazonDestinationFulfillmentCenterId,o.PostCode,case when l.GroupSKU='' then l.SKU else l.GroupSKU end,p.ID,p.LogisticsOrderNo) a
             LEFT JOIN (SELECT Row_number() OVER(PARTITION BY fba.ordersourceid, fba.ShipmentId, fba.SellerSKU ORDER BY fba.OrderSourceID) AS row,
                         OrderSourceID,
                         CustomerID,
                         ShipmentId,
                         SellerSKU,
                         QuantityReceived,
                         (SELECT TOP 1 AddTime
                          FROM   RB_FBA_ShipmentDetail_log WITH(nolock)
                          WHERE  CustomerID = fba.CustomerID
                                 AND OrderSourceID = fba.OrderSourceID
                                 AND ShipmentId = fba.ShipmentId
                                 AND SellerSKU = fba.SellerSKU
                                 AND QuantityReceived > 0
                          ORDER  BY AddTime ASC)                                                                                     AS ReceivedTime
                  FROM   RB_FBA_ShipmentDetail fba WITH(nolock)) f
         ON f.CustomerID = a.CustomerID
            AND f.ShipmentId = a.AmazonShipmentId
            AND a.SellerSKU = f.SellerSKU
            AND a.OrderSourceID = f.OrderSourceID
            where (a.CustomerID=1)
        GROUP  BY TransportName,
          WareHouseName,
          ToWareHouseName,
          TrackNumbers,
          AmazonCompeteRemark,
          OrderStatus,
          AmazonDeliveryStatus,
          f.ShipmentId,
          a.OrderCode,
          a.ProductNum,
          a.ShipTime,
          f.QuantityReceived,
          f.ReceivedTime,
          CASE
            WHEN ( a.ProductNum - Isnull(f.QuantityReceived, 0) ) > 0 THEN ( a.ProductNum - Isnull(f.QuantityReceived, 0) )
            ELSE 0
          END,
          Datediff(DAY, a.ShipTime, f.ReceivedTime),
          Datediff(DAY, a.AddTime, a.ShipTime),
          Datediff(DAY, a.ShipTime, f.ReceivedTime) + Datediff(DAY, a.AddTime, a.ShipTime),
          AmazonDestinationFulfillmentCenterId,
          PostCode,
          a.OrderSourceID,
          a.SellerSKU,
          a.PackageId,
          a.LogisticsOrderNo