Totaling A given set of return Results

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

SQL Server 2000 Forums

SQL Server Development (2000)

Totaling A given set of return Results

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-01-26 : 08:07:33

I have this:

Declare @startdate as datetime
Declare @enddate as datetime

Set @startdate = '1/18/2005 11:37:06'
Set @enddate = '1/25/2005 11:37:06'

SELECT DISTINCT product As Product, campaign_number As Campaign_No,
caliper As Caliper,
MIN (CaptureDateTime) AS StartTime, MAX (CaptureDateTime) AS EndTime,
Convert(Decimal (6,2),DateDiff (mi, MIN (CaptureDateTime), MAX (CaptureDateTime))/60.0) AS Total_Time_Hrs
FROM tbl_3PMProduction
WHERE
(tbl_3PMProduction.CaptureDateTime >= @Startdate
AND tbl_3PMProduction.CaptureDateTime <= @Enddate)

GRoup BY campaign_number, product, caliper
Order By StartTime Desc

Which produces:

MP-15 302045 .047 2005-01-20 04:16:00.030 2005-01-20 08:03:02.780 3.78
MP-15 302045 .039 2005-01-20 02:13:04.000 2005-01-20 04:15:04.650 2.03
MP-15 302045 .031 2005-01-19 19:47:00.140 2005-01-20 02:12:03.980 6.42
MP-15 302044 .031 2005-01-19 19:45:02.870 2005-01-19 19:49:01.780 .07
MP-15 302039 .031 2005-01-19 17:37:00.230 2005-01-19 17:37:00.230 .00
TN-9045 302039 .062 2005-01-19 08:15:04.930 2005-01-19 17:34:59.750 9.32
TN-9045 302039 .031 2005-01-19 07:04:03.600 2005-01-19 08:14:04.930 1.17
TN-9045 302039 .016 2005-01-19 05:53:03.680 2005-01-19 07:03:03.600 1.17
N-8092 302046 .039 2005-01-18 21:34:02.340 2005-01-19 01:44:02.920 4.17
N-8092 302046 .031 2005-01-18 11:38:04.680 2005-01-18 21:33:02.320 9.92

I want to get the SUM of all the Total_Time_Hrs results. I can't just SUM MIN to MAX because this would include hours considered down time. Im sure this is a simple one for many of you.

Any ideas?

The result set I am looking for would be 38.05 hrs total.

Thanks!

Your limits are only as far as you set your boundries....

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 08:14:44

so what wrong with:


select sum(Total_Time_Hrs) as Total_Time_Hrs
from
(
SELECT DISTINCT product As Product, campaign_number As Campaign_No, 
caliper As Caliper,
MIN (CaptureDateTime) AS StartTime, MAX (CaptureDateTime) AS EndTime,
Convert(Decimal (6,2),DateDiff (mi, MIN (CaptureDateTime), MAX (CaptureDateTime))/60.0) AS Total_Time_Hrs
FROM tbl_3PMProduction
WHERE 
(tbl_3PMProduction.CaptureDateTime >= @Startdate 
AND tbl_3PMProduction.CaptureDateTime <= @Enddate)
) t

Go with the flow & have fun! Else fight the flow

jpiscit1
Posting Yak Master

130 Posts

Posted - 2005-01-26 : 08:22:34

Nothing. Thats it. Thanks!

Declare @startdate as datetime
Declare @enddate as datetime

Set @startdate = '1/18/2005 11:37:06'
Set @enddate = '1/25/2005 11:37:06'

select sum(Total_Time_Hrs) as Total_Time_Hrs
from
(
SELECT DISTINCT product As Product, campaign_number As Campaign_No,
caliper As Caliper,
MIN (CaptureDateTime) AS StartTime, MAX (CaptureDateTime) AS EndTime,
Convert(Decimal (6,2),DateDiff (mi, MIN (CaptureDateTime), MAX (CaptureDateTime))/60.0) AS Total_Time_Hrs
FROM tbl_3PMProduction
WHERE
(tbl_3PMProduction.CaptureDateTime >= @Startdate
AND tbl_3PMProduction.CaptureDateTime <= @Enddate)
GRoup BY campaign_number, product, caliper

) t

Your limits are only as far as you set your boundries....

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-01-26 : 09:03:32

get that DISTINCT out of your query!

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources