Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
field prodDate puts out values as such:8/12/2013 7:50:158/12/2013 7:51:038/12/2013 7:53:428/12/2013 7:54:128/12/2013 7:56:02......8/12/2013 14:57:578/12/2013 14:59:59I'd like to get a time span sum beginning with the first prodDate entry and ending with the last.The idea is I'll then use that to divide lbs produced, thus giving me a gauge for lb per hour.-G
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-09-12 : 14:00:05
quote:Originally posted by JGaston field prodDate puts out values as such:8/12/2013 7:50:158/12/2013 7:51:038/12/2013 7:53:428/12/2013 7:54:128/12/2013 7:56:02......8/12/2013 14:57:578/12/2013 14:59:59I'd like to get a time span sum beginning with the first prodDate entry and ending with the last.The idea is I'll then use that to divide lbs produced, thus giving me a gauge for lb per hour.-G
I didn't quite follow your explanation. Are you trying to add up durations? Or are you trying to compute the difference between the first prodDate and the lastProdDate, and count how many rows there are within those two times to get an idea of how many rows per hour were produced?
JGaston
Starting Member
8 Posts
Posted - 2013-09-12 : 14:10:13
sorry - trying to get the duration between first prodDate and lastProddate.if first prodDate is 8/12/2013 7:50:15and last prodDate is 8/12/2013 12:50:15it would return a value of 5:00:00 or 5 hours.i want to use that 5 hours to divide another field by(poundsproduced) to get get lbs per hour...
James K
Master Smack Fu Yak Hacker
3873 Posts
Posted - 2013-09-12 : 14:23:12
One of these?
-- if PoundsProduced is a known valueDECLARE @PoundsProduced FLOAT;SET @PoundsProduced = 254.6;SELECT @PoundsProduced*60.0/NULLIF(datediff(mi,MIN(prodDate),MAX(prodDate)),0) AS PoundsPerHourFROM YourTable-- if there is a value for poundsproduced in each rowSELECT SUM(PoundsProduced)*60.0/NULLIF(datediff(mi,MIN(prodDate),MAX(prodDate)),0) AS PoundsPerHourFROM YourTable