Início / dba / Perguntas / 122495
Accepted
Hector
Asked: 2015-12-01 13:50:40 +0800 CST

Vários critérios para incluir/excluir linhas

  • 772

ATUALIZAÇÃO DE ESCLARECIMENTO

Infelizmente, reportamos com base no aplicativo e no banco de dados de terceiros, portanto, não podemos modificar os tipos de dados, pois não os possuímos.

Para ajudar a esclarecer, Este relatório basicamente nos mostra todos os ORDERSque não possuem status '80' e '90' que são pedidos 'concluídos' em nosso sistema.

O gerente de operações gostaria que o relatório nos mostrasse todos os pedidos que também têm um status de 80 ou 90, mas apenas se ele mudou recentemente para esse status de pedido em 7 minutos. Se você notou na consulta, tentei criar uma consulta na linha de seleção que mostrará os tempos decorridos desde que o status foi alterado (estou fazendo isso comparando a CONSOL_END_TIMEcoluna vs. CURRENT_TIMESTAMP)

Eu basicamente gostaria que o relatório pegasse TODOS ORDER_NOentre 20-70e apenas '80' e '90' se consol_end_timefosse apenas 7 minutos antes do current_timestamp.

Espero ter ajudado, se precisar de mais esclarecimentos, por favor avise. Atualizei a consulta para mostrar melhor o que estou tentando realizar.

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

A PERGUNTA ORIGINAL ANTES DE QUALQUER MODIFICAÇÃO

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 respostas

  1. Best Answer

    Não sei por que você acha que precisa de um SUMaqui, ou por que está fazendo toda essa manipulação na CONSOL_END_TIMEcoluna. Seus problemas de palavras me levam a mudar isso:

        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')

    Para isso:

    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)

  )
)

    (Removido o convert to timedesde que você revelou que não está usando o 2008 R2, afinal.)

    Ainda muito mais legível do que todas aquelas chamadas SUBSTRING/ . RIGHTObserve que isso permitirá horários inválidos (como '999999') e ainda não reflete nada sobre o dia em que o evento aconteceu. Existe outra coluna talvez que represente a data?

    Mas só posso ter certeza de que está certo se você fornecer a estrutura da tabela (os tipos de dados são importantes) e dados de amostra suficientes para cobrir todos esses cenários:

    1. Está concluído (status não em 80,90) - deve ser incluído no resultado
    2. Não foi concluído, mas alterado nos últimos sete minutos - deve ser incluído
    3. Não foi concluído e alterado há mais de sete minutos - não deve ser incluído
    4. Não foi concluído e alterado > 24 horas e 7 minutos atrás, mas < 24 horas atrás (por exemplo, se são 14h32 agora, então algo das 14h35 de ontem); atualmente acho que você está ignorando a data da mudança de status - não deve ser incluída

    Eu poderia inventar esses dados, mas estou mais atrás dos tipos de dados para ter certeza de que não há conversões explícitas ou outras alterações necessárias e os dados reais que estão CONSOL_END_TIME(não está claro apenas com sua consulta).

    • 4

relate perguntas