We help IT Professionals succeed at work.

How to calculate orders in completed hours only?

wiggy353
wiggy353 asked
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.

Open in new window


Thanks for the help!
Comment
Watch Question

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())
John EastonDirector

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:

DATETIMEPACKED < dateadd(mi,datepart(mi,now())*-1,now())

I think this should take the amount of minutes in now() and deduct it from now() leaving the just the hour.
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)
John EastonDirector

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

Author

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