Help Sorting data for an average

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)

Help Sorting data for an average

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-03-05 : 09:14:13

I was wondering if someone can give some insight on how I can get an average on specific data within a query. I have a query (shown below) that is currently being used to find the total time of a given run for the day (from start to finish). Now I want to group the runs by item_no and get an average for each. Here's what I have for code pulling data from two tables:

--Change these varibles for different daily results
Declare @SrtofDay datetime
Declare @EndofDay datetime
Set @SrtofDay =
'3/4/2004 6:00:00 AM'
Set @EndofDay =
'3/5/2004 12:55:00 AM'

Select Distinct prod.item_no, prod.date_time, eff.stack_no,
convert(varchar(10),dateadd(ss,abs(DateDiff (ss, eff.start_time, prod.Shts_into_oven_time)),0),8) AS [Time of Prod Run]
From prod_data prod
Left Join tbl_Efficiencies eff
On prod.work_order_no = eff.work_order_no and
prod.stack_no = eff.stack_no
WHERE
(prod.date_time BETWEEN @SrtofDay AND @EndofDay
and prod.stack_no LIKE '%30%')
Group By eff.start_time, prod.Shts_into_oven_time, prod.item_no, prod.date_time,
eff.stack_no
Order by prod.date_time DESC

Here are the results:

Item_no dat_time stack_no Time of prod run
ISI0163 2004-03-04 23:15:39.000 30006 00:54:23
ISI0163 2004-03-04 22:15:55.000 30005 00:57:43
ISI0163 2004-03-04 21:17:30.000 30004 01:05:19
ISI0163 2004-03-04 19:55:28.000 30003 NULL
ISI0163 2004-03-04 18:38:59.000 30002 01:11:21
ISI0163 2004-03-04 17:35:35.000 30001 01:15:36
ISI0246 2004-03-04 15:12:05.000 30028 01:14:14
ISI0246 2004-03-04 14:17:59.000 30027 01:13:19
ISI0246 2004-03-04 13:10:40.000 30026 01:13:43
ISI0246 2004-03-04 11:27:04.000 30025 NULL
ISI0246 2004-03-04 10:25:43.000 30024 NULL
ISI0246 2004-03-04 09:23:13.000 30023 NULL
ISI0246 2004-03-04 08:31:10.000 30022 NULL
ISI0246 2004-03-04 07:37:59.000 30021 NULL

From the results you can see there are 6 records under Item_no showing as ISI0163 and there are 8 showing as ISI0246. I want to modify my query (or make a new one) so that it will produce the average times of these specific item numbers. For example:

(Sum of times for ISI0163)/(total records or 6)=avg time for ISI0163
(Sum of times for ISI0246)/(total records or 8)=avg time for ISI0246

Any thoughts on how I can accomplish this?

Thanks in advance.

John

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-05 : 09:55:59

[code]select item_no, avg(seconds) as [Avg Time of Prod Run - Seconds]
from
(
select item_no, isnull((left([Time of Prod Run],2)*3600) + (substring([Time of Prod Run],4,2)* 60) + (right([Time of Prod Run],2)),0) seconds
from
(
--Put your query here
) d
) d
group by item_no
[/code]

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-03-05 : 10:17:23

What does this do with the NULL valued records? That, of course, was the next question. It looks like it includes it in the average and sets it to zero. What if I wanted to omit the NULL values in the total average? btw, this worked nicely.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-03-05 : 10:39:14

[code]select item_no, avg(seconds) as [Avg Time of Prod Run - Seconds]
from
(
select item_no, (left([Time of Prod Run],2)*3600) + (substring([Time of Prod Run],4,2)* 60) + (right([Time of Prod Run],2)) seconds
from
(
--Put your query here
) d
where [Time of Prod Run] is not null
) d
group by item_no[/code]

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-03-05 : 13:45:16

Thanks!!! Thats got it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources