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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Summing Time Span (for total per hour)

Author  Topic 

JGaston
Starting Member

8 Posts

Posted - 2013-09-12 : 13:53:23
field prodDate puts out values as such:

8/12/2013 7:50:15
8/12/2013 7:51:03
8/12/2013 7:53:42
8/12/2013 7:54:12
8/12/2013 7:56:02
...
...
8/12/2013 14:57:57
8/12/2013 14:59:59


I'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:15
8/12/2013 7:51:03
8/12/2013 7:53:42
8/12/2013 7:54:12
8/12/2013 7:56:02
...
...
8/12/2013 14:57:57
8/12/2013 14:59:59


I'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?
Go to Top of Page

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:15
and last prodDate is 8/12/2013 12:50:15

it 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...
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-12 : 14:23:12
One of these?
-- if PoundsProduced is a known value
DECLARE @PoundsProduced FLOAT;
SET @PoundsProduced = 254.6;
SELECT
@PoundsProduced*60.0/NULLIF(datediff(mi,MIN(prodDate),MAX(prodDate)),0) AS PoundsPerHour
FROM YourTable

-- if there is a value for poundsproduced in each row
SELECT
SUM(PoundsProduced)*60.0/NULLIF(datediff(mi,MIN(prodDate),MAX(prodDate)),0) AS PoundsPerHour
FROM YourTable
Go to Top of Page
   

- Advertisement -