SQL Server 2008 having issues with HAVING clause

Question

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

Answer 1

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.

Answer 2

If you are struggling with "Group By" queries than @Kenneth Fisher's answer might help or you need to provide more details same as asked by @dnoeth

I wanted to point out that all values in the image table are already less than -5 or 5. Because values are negative so -42,-71,-100 are less than -5 or 5. (In your question you said -5 but in query you have 5)

Second, about DateDiff returned result. Syntax = DATEDIFF ( datepart , startdate , enddate ) so if enddate is today and if DateDiff result is positive, it indicates past duration, if DateDiff result is negative, it indicates future duration.

If you are interested in order shipped in past 5 minutes you should look for

DATEDIFF(MINUTE,ShipDate,GETDATE()) > 0 AND DATEDIFF(MINUTE,ShipDate,GETDATE()) <= 5

(I am guesing this based on your column name, mins_elapsed indicates that you are interested in past duration.)

Third, In DateDiff you are considering only time values, examine below queries,

DECLARE @ShippedYesterday DateTime, @ShippedToday DateTime;
SELECT @ShippedYesterday = DATEADD(DAY,-1,GETDATE()), @ShippedToday = GETDATE();
SELECT @ShippedYesterday, @ShippedToday;
SELECT convert(varchar(8), @ShippedYesterday, 114) AS YesterdayShippedTime, convert(varchar(8), @ShippedToday, 114) As TodayShippedTime;
SELECT DATEDIFF(MINUTE, convert(varchar(8), @ShippedYesterday, 114), convert(varchar(8), @ShippedToday, 114));
SELECT DATEDIFF(MINUTE, @ShippedYesterday, @ShippedToday);

second last query returns 0 while the last query returns > 1000.

If you store shipping date in your database than you should restrict it to today's date before using DateDiff with Current date.

Right now you are probably comparing today's 4pm to yesterday's or day before yesterday's 5pm and you get result -60 minutes.