SQL Server 2008 – Issues with HAVING Clause

sql serversql-server-2008-r2t-sql

I have a question regarding a report I am currently writing.

I need this query to only show me line items that have < -5 as mins_elapse

Here is the code. I have left out the FROM clause to prevent forms of business identity.

select 
    H.ORDER_NUM
    ,H.CUST_NUM
    ,H.CREATION_DATE
    ,H.DUE_DATE
    ,H.ORDER_PRIORITY
    ,H.ORDER_STATUS
    ,H.ORDER_TYPE
    ,H.TRAILER_ROUTE
    ,S.SHIP_ADDRESS_1
    ,S.SHIP_CITY
    ,S.SHIP_STATE
    ,S.SHIP_COUNTRY
    ,S.SHIP_POSTAL_CODE
    ,C.SERVICE_CODE
    ,C.CODE_TEXT
    ,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
    ,--determine the difference between when the order was manifested and the current time and use it in the WHERE clause below
    DATEDIFF(MINUTE, SUBSTRING(pm.ship_time, 1, 2) + ':' + SUBSTRING(pm.ship_time, 3, 2) + ':' + RIGHT(pm.ship_time, 2), convert(varchar(8), current_timestamp, 114)) as mins_elapsed
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 90
    and DATEDIFF(MINUTE, SUBSTRING(pm.ship_time, 1, 2) + ':' + SUBSTRING(pm.ship_time, 3, 2) + ':' + RIGHT(pm.ship_time, 2), convert(varchar(8), current_timestamp, 114)) <= 5
    and h.large_order = 'Y' `
    and creation_date > 20120701 `
group by 
    H.ORDER_NUM
    ,H.CUST_NUM
    ,H.CREATION_DATE
    ,H.DUE_DATE
    ,H.ORDER_PRIORITY
    ,H.ORDER_STATUS
    ,H.ORDER_TYPE
    ,H.TRAILER_ROUTE
    ,PM.SHIP_TIME
    ,S.SHIP_ADDRESS_1
    ,S.SHIP_CITY
    ,S.SHIP_STATE
    ,S.SHIP_COUNTRY
    ,S.SHIP_POSTAL_CODE
    ,C.SERVICE_CODE
    ,C.CODE_TEXT
    ,H.LARGE_ORDER
    ,H.ASSOC_DATE
    ,H.ASSOC_TIME`
having 
    DATEDIFF(MINUTE, SUBSTRING(pm.ship_time, 1, 2) + ':' + SUBSTRING(pm.ship_time, 3, 2) + ':' + RIGHT(pm.ship_time, 2), convert(varchar(8), current_timestamp, 114)) <= 5
order by 
    H.LARGE_ORDER desc
    ,H.ORDER_STATUS asc
    ,H.ASSOC_DATE asc
    ,H.ASSOC_TIME asc

manifest_time

Best Answer

Ok, changing my answer a bit based on the comment by @dnoeth.

First of all you only need a GROUP BY and a HAVING if you are doing an aggregate. So, if you want the total difference to be < 5 you would do something like this:

HAVING SUM(DATEDIFF(MINUTE,pm.ship_time,getdate())) < 5

Same for average difference etc. But don't put it in the WHERE clause.

If on the other hand you only need the < 5 minutes for each individual row then you just put it in the WHERE clause and get rid of the HAVING entirely.

WHERE SUM(DATEDIFF(MINUTE,pm.ship_time,getdate())) < 5

At this point you need to decide if you want a GROUP BY at all. Unless you have some aggregates then you may not need it at all. You can use it to get rid of duplicates but at that point you can just as easily use DISTINCT.

Related Question