troubleshooting Question

How to calculate orders in completed hours only?

Avatar of wiggy353
wiggy353 asked on
Microsoft SQL Server 2008SQL
6 Comments1 Solution241 ViewsLast Modified:
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!
ASKER CERTIFIED SOLUTION
knightEknight

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros