主页 / dba / 问题 / 122495
Accepted
Hector
Asked: 2015-12-01 13:50:40 +0800 CST

包含/排除行的多个条件

  • 772

澄清更新

不幸的是，我们基于第 3 方应用程序和数据库进行报告，因此我们无法修改数据类型，因为我们不拥有它。

为了帮助澄清，该报告基本上向我们展示了ORDERS在我们的系统中没有状态为“80”和“90”的所有“已完成”订单。

运营经理希望报告向我们显示状态为 80 或 90 的所有订单，但前提是它最近在 7 分钟内更改为该订单状态。如果您在查询中注意到我试图在选择行中创建一个查询，该查询将显示自状态更改以来经过的时间（我通过比较CONSOL_END_TIME列 vs来执行此操作CURRENT_TIMESTAMP）

我基本上希望报告在80 和 90ORDER_NO之间20-70并且仅consol_end_time在current_timestamp.

希望对您有所帮助，如果您需要更多说明，请提出建议。我已经更新了查询以更好地显示我想要完成的任务。

SELECT * 
     , SUBSTRING(PM.SHIP_TIME, 1, 2)+':'+SUBSTRING(PM.SHIP_TIME, 3, 2)+':'+RIGHT(PM.SHIP_TIME, 2) AS MANIFEST_TIME
     , CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,114) AS CURR_TIME
     , DATEDIFF(MINUTE,SUBSTRING(H.CONSOL_END_TIME, 1, 2)+':'+SUBSTRING(H.CONSOL_END_TIME, 3, 2)+':'+RIGHT(H.CONSOL_END_TIME, 2),CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,114)) AS MINS_ELAPSED  
FROM PK_HEADER H 
            JOIN PK_SHIPPING S ON H.ORDER_NUM = S.ORDER_NUM 
            LEFT OUTER JOIN CARRIER_CFG C ON S.SERVICE_CODE = C.SERVICE_CODE 
            LEFT OUTER JOIN ROUTING_PACK R ON H.ROOT_CNTNR = R.CNTNR_ID 
            LEFT OUTER JOIN PK_MANIFEST PM ON (H.ORDER_NUM = PM.ORDER_NUM) -- JUST JOIN TO THE MANIFEST TABLE TO GET THE TIME IT WAS MANIFESTED
WHERE CUST_NUM = @CUSTOMER 
        AND @SERVICE = S.SERVICE_CODE
        AND @ADD1 = S.SHIP_ADDRESS_1
        AND @ZIP = S.SHIP_POSTAL_CODE
        AND (H.ORDER_STATUS BETWEEN 20 AND 70 
             OR (H.ORDER_STATUS IN ('80','90'))) 
        OR SUM(DATEDIFF(MINUTE,SUBSTRING(H.CONSOL_END_TIME, 1, 2) +':'+SUBSTRING(H.CONSOL_END_TIME, 3, 2)+':'+RIGHT(H.CONSOL_END_TIME, 2),CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,114)) < '7')
        AND H.LARGE_ORDER = 'Y' 
        AND CREATION_DATE > 20120701 


ORDER BY 
     H.LARGE_ORDER DESC
    ,H.ORDER_STATUS ASC
    ,H.ASSOC_DATE ASC
    ,H.ASSOC_TIME ASC

任何修改之前的原始查询

declare @customer char(18) 
declare @service char(18)
declare @add1 char(40)
declare @zip char(18)


select top 1 @customer = cust_num, @service = s.service_code , @add1 = S.SHIP_ADDRESS_1, @zip = S.SHIP_POSTAL_CODE
FROM PK_HEADER H JOIN PK_LINE L on H.order_num = L.order_num JOIN PK_SHIPPING S ON H.ORDER_NUM = S.ORDER_NUM LEFT OUTER JOIN CARRIER_CFG C ON S.SERVICE_CODE = C.SERVICE_CODE LEFT OUTER JOIN ROUTING_PACK R ON H.ROOT_CNTNR = R.CNTNR_ID 
where L.PKG_ID = '{?Package}'

select * FROM PK_HEADER H /* JOIN PK_LINE L on H.order_num = L.order_num */ JOIN PK_SHIPPING S ON H.ORDER_NUM = S.ORDER_NUM LEFT OUTER JOIN CARRIER_CFG C ON S.SERVICE_CODE = C.SERVICE_CODE LEFT OUTER JOIN ROUTING_PACK R ON H.ROOT_CNTNR = R.CNTNR_ID 
where cust_num  = @customer and @service = s.service_code and @add1 = S.SHIP_ADDRESS_1 and @zip = S.SHIP_POSTAL_CODE and H.ORDER_STATUS BETWEEN 20 AND 70 and h.large_order = 'Y' and creation_date > 20120701


ORDER BY H.LARGE_ORDER DESC, H.ORDER_STATUS asc, H.ASSOC_DATE ASC, H.ASSOC_TIME ASC



INSERT INTO DATA
INSERT INTO [dbo].[PK_HEADER]
           ([ORDER_NUM]
           ,[COMPANY_CODE]
           ,[CUST_NUM]
           ,[CREATION_DATE]
           ,[CREATION_TIME]
           ,[DUE_DATE]
           ,[DUE_TIME]
           ,[ORDER_PRIORITY]
           ,[ORDER_STATUS]
           ,[ASSIGNMENT_ZONE_NUM]
           ,[ON_HOLD]
           ,[ORDER_CANCEL]
           ,[ROOT_CNTNR]
           ,[PICK_CONSOL]
           ,[HAD_BKORDER]
           ,[MISS_INV]
           ,[SUPERVISOR_AUTH]
           ,[INCOMPLETE_INS]
           ,[CONSOL_ROUTING]
           ,[CONSOL_ST_DATE]
           ,[CONSOL_ST_TIME]
           ,[CONSOL_END_DATE]
           ,[CONSOL_END_TIME]
           ,[SPECIAL_HANDL]
           ,[ELAPSED_PACK_TIME]
           ,[SHIP_CNTNR_PREV]
           ,[SHIP_CNTNR_TOTAL]
           ,[MANF_CNTNR_TOTAL]
           ,[ORDER_EXPORT]
           ,[ORDER_TYPE]
           ,[WAVE_TYPE]
           ,[WAVE_ID]
           ,[ORIG_ORDER_PRIORITY]
           ,[ORDER_VALUE]
           ,[ORDER_WEIGHT]
           ,[LARGE_ORDER]
           ,[PROD_SUBSTN_OVERRIDE]
           ,[ASSOC_DATE]
           ,[ASSOC_TIME]
           ,[TRAILER_SEQ]
           ,[HOST_ORDER_NUM]
           ,[HOST_ORDER_NUM_2]
           ,[HOST_OP]
           ,[TRAILER_ROUTE]
           ,[DESTINATION]
           ,[HOLD_REASON]
           ,[CANCEL_REASON])
     VALUES
           (<ORDER_NUM, char(25),>
           ,<COMPANY_CODE, char(15),>
           ,<CUST_NUM, char(15),>
           ,<CREATION_DATE, char(8),>
           ,<CREATION_TIME, char(6),>
           ,<DUE_DATE, char(8),>
           ,<DUE_TIME, char(6),>
           ,<ORDER_PRIORITY, numeric(18,0),>
           ,<ORDER_STATUS, smallint,>
           ,<ASSIGNMENT_ZONE_NUM, char(3),>
           ,<ON_HOLD, char(1),>
           ,<ORDER_CANCEL, char(1),>
           ,<ROOT_CNTNR, char(25),>
           ,<PICK_CONSOL, char(1),>
           ,<HAD_BKORDER, char(1),>
           ,<MISS_INV, char(1),>
           ,<SUPERVISOR_AUTH, char(1),>
           ,<INCOMPLETE_INS, int,>
           ,<CONSOL_ROUTING, char(1),>
           ,<CONSOL_ST_DATE, char(8),>
           ,<CONSOL_ST_TIME, char(6),>
           ,<CONSOL_END_DATE, char(8),>
           ,<CONSOL_END_TIME, char(6),>
           ,<SPECIAL_HANDL, char(3),>
           ,<ELAPSED_PACK_TIME, numeric(18,0),>
           ,<SHIP_CNTNR_PREV, int,>
           ,<SHIP_CNTNR_TOTAL, int,>
           ,<MANF_CNTNR_TOTAL, int,>
           ,<ORDER_EXPORT, char(1),>
           ,<ORDER_TYPE, smallint,>
           ,<WAVE_TYPE, smallint,>
           ,<WAVE_ID, char(20),>
           ,<ORIG_ORDER_PRIORITY, numeric(18,0),>
           ,<ORDER_VALUE, real,>
           ,<ORDER_WEIGHT, real,>
           ,<LARGE_ORDER, char(1),>
           ,<PROD_SUBSTN_OVERRIDE, smallint,>
           ,<ASSOC_DATE, char(8),>
           ,<ASSOC_TIME, char(6),>
           ,<TRAILER_SEQ, int,>
           ,<HOST_ORDER_NUM, char(30),>
           ,<HOST_ORDER_NUM_2, char(30),>
           ,<HOST_OP, char(10),>
           ,<TRAILER_ROUTE, char(30),>
           ,<DESTINATION, char(20),>
           ,<HOLD_REASON, char(3),>
           ,<CANCEL_REASON, char(3),>)
GO
sql-server sql-server-2005

1 个回答

  1. Best Answer

    不知道您为什么认为需要SUM此处，或者为什么要对CONSOL_END_TIME列进行所有操作。你的文字问题导致我改变这个：

        AND (H.ORDER_STATUS BETWEEN 20 AND 70 
         OR (H.ORDER_STATUS IN ('80','90'))) 
         OR SUM(DATEDIFF(MINUTE,SUBSTRING(H.CONSOL_END_TIME, 1, 2) +':'
    +SUBSTRING(H.CONSOL_END_TIME, 3, 2)+':'
    +RIGHT(H.CONSOL_END_TIME, 2),CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,114)) < '7')

    对此：

    AND
(
  (
    H.ORDER_STATUS NOT IN (80,90)
  )
  OR
  (
    H.ORDER_STATUS IN (80,90)
    AND 
    -- this is the nonsense you get yourself into when you use the wrong data types
    CASE WHEN ISDATE(CONSOL_END_DATE + ' ' +
     STUFF(STUFF(CONVERT(VARCHAR(8),CONSOL_END_TIME),5,0,':'),3,0,':')) = 1
    THEN CONSOL_END_DATE + ' ' +
     STUFF(STUFF(CONVERT(VARCHAR(8),CONSOL_END_TIME),5,0,':'),3,0,':') END
       >= DATEADD(MINUTE, -7, CURRENT_TIMESTAMP)

  )
)

    （删除了转换为，time因为您现在已经透露您毕竟没有使用 2008 R2。）

    仍然比所有那些SUBSTRING/RIGHT电话更具可读性。请注意，这将允许无效时间（如'999999'）并且它仍然不会反映事件发生当天的任何信息。是否还有另一列代表日期？

    但我只能确定如果您提供表结构（数据类型很重要）和足够的示例数据来涵盖所有这些场景，那是正确的：

    1. 已完成（状态不在 80,90 中）-应包含在结果中
    2. 未完成但在最后七分钟内更改 -应包括在内
    3. 超过七分钟前未完成和更改 -不应包括在内
    4. 未完成和更改 > 24 小时 7 分钟前但 < 24 小时前（例如，如果现在是 14:32，那么从昨天 14:35 开始）；目前我认为您忽略了状态更改的日期-不应包括在内

    我可以发明该数据，但我更关注数据类型以确保不需要显式转换或其他更改，以及其中的实际数据CONSOL_END_TIME（仅从您的查询中不清楚）。

    • 4

相关问题