quote:

---

Originally posted by Dmh188

Hey guys. So here is what i am working on. I need to find the average amount of orders per hour that a picker picked. ultimately i am trying to take the time they picked their first order and subtract it from the time they picked their last order. This will give me total hours spent picking. Then i will divide total number picked by that time.
here is what i have:
select count(pickstartuser),pickstartuser, max(convert(varchar,pickcompletedate, 108)) , min(convert(varchar(8),pickcompletedate, 108)) from orderstatus
where pickcompletedate>=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
group by pickstartuser

The only problem with this is subtraction can not be done between to varchar fields. To do the subtraction i tried this:

select count(pickstartuser),pickstartuser, (convert(varchar(8),max(pickcompletedate), 108) - convert(varchar(8),min(pickcompletedate), 108)) from orderstatus
where pickcompletedate>=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
group by pickstartuser

i get the good ol' operand data type varchar is invalid for subtract operator.
Also i am running mssql2005

---

SELECT CONVERT(varchar,(max(pickcompletedate) - min(pickcompletedate)), 108) FROM orderstatus

quote:

---

Originally posted by Kristen

"DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))"

I suggest you sue "Day" instead of "dd" - much easier to read, less confusing too - what's the mnemonic for Month? Minute? Millisecond? Microsecond? I don't know either, but all those long-form keywords work find and are unambiguous.

Given that you are using that formula just use "Hour" as the time unit to round to the Full hour, instead of using a VARCHAR conversion.

---