0

CLARIFICATION UPDATE

Unfortunately we report based on the 3rd party application and database so we cannot modify the datatypes since we do not own it.

To help clarify, This report basically shows us all ORDERS that do not have a status of '80' and '90' which are 'completed' order in our system.

The operations manager would like for the report to show us all orders that also DO have a status of 80 or 90 but only if it has recent changed to that order status within 7 mins. If you noticed on the query I have attempted to create a query in the select line that will show times elapse since the status has changed (I am doing this by comparing the CONSOL_END_TIME column vs CURRENT_TIMESTAMP)

I essentially would like the report to pick up ALL ORDER_NO between 20-70 and only the '80' and '90' if the consol_end_time was only 7 mins prior to the current_timestamp.

Hope that helps, if you need more clarification, please advise. I have updated the query to better show what I am trying to accomplish..

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

THE ORIGINAL QUERY BEFORE ANY MODIFICATIONS AT ALL

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
14
  • 5
    Can you show a few rows of sample data and desired results? You're mixing a lot of things here that I don't know will work well together (if at all), most importantly SUM / HAVING without any grouping. Nov 30, 2015 at 21:54
  • 1
    (And also, if you know the data types are wrong, why not fix them?) Nov 30, 2015 at 21:59
  • 1
    And you tell if something is "completed" by performing some kind of aggregate operation against part of the end_time value? Also this: AND CREATION_DATE > 20120701 is wrong, it should be: AND CREATION_DATE > '20120701'. Dec 2, 2015 at 20:55
  • 1
    That's not going to work - as soon as it hits 'NULL' or an invalid time, you'll see Conversion failed when converting date and/or time from character string. Also, don't specify types like char without length. Dec 4, 2015 at 18:30
  • 1
    Next time please provide all of that information up front. I asked for it 5 days ago and have spent 5 days teasing little bits of info from you. Dec 4, 2015 at 18:58

1 Answer 1

4

Don't know why you think you need a SUM here, or why you're doing all that manipulation to the CONSOL_END_TIME column. Your word problems lead me to change this:

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

To this:

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)

  )
)

(Removed the convert to time since you've now revealed that you're not using 2008 R2 after all.)

Still a heck of a lot more readable than all those SUBSTRING/RIGHT calls. Note that this will allow invalid times (like '999999') and it still doesn't reflect anything about the day the event happened on. Is there another column perhaps that represents the date?

But I can only be sure that's right if you provide table structure (data types are important), and enough sample data to cover all of these scenarios:

  1. Is completed (status not in 80,90) - should be included in result
  2. Is not completed but changed in the last seven minutes - should be included
  3. Is not completed and changed more than seven minutes ago - should not be included
  4. Is not completed and changed > 24 hours and 7 minutes ago but < 24 hours ago (e.g. if it is 14:32 right now, then something from 14:35 yesterday); currently I think you are ignoring the date of the status change - should not be included

I could invent that data but I'm more after the data types to be sure there are no explicit conversions or other changes required, and the actual data that is in CONSOL_END_TIME (it is not clear from just your query).

14
  • I see, I just verified the datatype for consol_end_time is (char(8),null) Your above statements (1,2,3) are correct. When I added your suggestion, it gave me a Incorrect syntax near the keyword 'ORDER'.'
    – Hector
    Dec 2, 2015 at 21:12
  • @Hector It's CHAR(8)? Great. Now can you provide some sample data? I'm not asking for that repeatedly to be a pain, I'm asking for it so I can help you get to a working solution. Dec 2, 2015 at 21:22
  • No worries at all. I appreciate you taking the time to help me out with this, I have been driving myself up the wall with this all because of bad datatypes. When you say sample data, would you like to see a screenshot of how the data looks in ssms?
    – Hector
    Dec 2, 2015 at 21:25
  • I am currently testing out your solution but converted here is my example ( ( H.ORDER_STATUS NOT IN (80,90) ) OR ( H.ORDER_STATUS IN (80,90) AND SUBSTRING(H.CONSOL_END_TIME, 1, 2)+':'+SUBSTRING(H.CONSOL_END_TIME, 3, 2)+':'+RIGHT(H.CONSOL_END_TIME, 2) > DATEADD(MINUTE, -7, CURRENT_TIMESTAMP) ) ) I will keep you posted. Thanks!
    – Hector
    Dec 2, 2015 at 21:31
  • Quick update since I just saw your modification, I just tied the ( ( H.ORDER_STATUS NOT IN (80,90) ) OR ( H.ORDER_STATUS IN (80,90) AND >= DATEADD(MINUTE, -7, CONVERT(TIME, CURRENT_TIMESTAMP)) ) ) and its throwing a incorrect syntax near '<' I think its due to the convert(time, current_timestamp) because of the datatype
    – Hector
    Dec 2, 2015 at 21:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.