SQL Server 2008 having issues with HAVING clause I have a question regarding a report I am currently writing.

I need this query to only show me line items that have &lt; -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)) &lt;= 5
    and h.large_order = 'Y' `
    and creation_date &gt; 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)) &lt;= 5
order by 
    H.LARGE_ORDER desc
    ,H.ORDER_STATUS asc
    ,H.ASSOC_DATE asc
    ,H.ASSOC_TIME asc
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()) &gt; 0 AND DATEDIFF(MINUTE,ShipDate,GETDATE()) &lt;= 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.