We help IT Professionals succeed at work.

# How to calculate orders in completed hours only?

on
I am calculating an "orders per hour" rate. However, I only want to use data from hours that are complete, i.e. if it is 8:15, I do not want to use data from any orders between 8:00 and now. I only want to use data from orders between 5 am and 8:00 (or 7:59).

This is the query that I have been using to give me how many minutes have passed since the first order was packed today (after 5:00 am). I use this to calculate how many orders are packed per minute. However, I want to further narrow it down to only orders that were packed between 5 and the current hour, demonstrated by the commented out line. This is the calculation that I need help figure out how to right.

``````SELECT
DATEDIFF(mi,MIN(DATETIMEPACKED), MAX(DATETIMEPACKED))
FROM
ORDERDETAILS WITH(NOLOCK)
WHERE
DATETIMEPACKED >= CONVERT(VARCHAR, GETDATE(), 110) + ' 05:00:00'
-- and datetimepacked "hour" is less than the "hour" of current time.
``````

Thanks for the help!
Comment
Watch Question

## View Solution Only

Commented:
SELECT
DATEDIFF(mi,MIN(DATETIMEPACKED), MAX(DATETIMEPACKED))
FROM
ORDERDETAILS WITH(NOLOCK)
WHERE
DATETIMEPACKED >= CONVERT(VARCHAR, GETDATE(), 110) + ' 05:00:00'

-- and datetimepacked "hour" is less than the "hour" of current time.
AND datepart(hour,DATETIMEPACKED) < datepart(hour,GETDATE())
Director

Commented:
I am not quite sure how to write the SQL - I appear to be having a mental block.  But can you not include in the where clause something like:

I think this should take the amount of minutes in now() and deduct it from now() leaving the just the hour.

Commented:
this will be more efficient

SELECT
DATEDIFF(mi,MIN(DATETIMEPACKED), MAX(DATETIMEPACKED))
FROM
ORDERDETAILS WITH(NOLOCK)
WHERE
DATETIMEPACKED >= CONVERT(VARCHAR, GETDATE(), 110) + ' 05:00:00'
AND DATETIMEPACKED < dateadd(hh, datediff(hh, 0, getdate()), 0)
Director

Commented:
Sorry, now() in my above comment should be GETDATE().

Commented:
Perfect, thanks!

Commented:
>>DATETIMEPACKED < dateadd(mi,datepart(mi,now())*-1,now())<< this will get the minutes but not the seconds, so results might be off.