Can this be done (Query)?

Thanks in advance for any direction given!!!!!!!!

I have a table that has production infromation in it. One of the columns is to flag an exception for lack of production (Preventative Maintenance).
The table includes Equipment number, production units, date, shift and PM.

What I have been asked to do is to get the average number of units produced between PM's for each piece of equipment. I could pull the data out and put it in Excells and get the answer, but I want to automate the system and build a query that would give me the answer anytime my boss wants it.

My question is

Is It/CAN/HOW would I structure a query to average the production between the PM dates for an extended period of time (over several PM's)??

Table and data would be similar to the following

EntryDate EquipNum Shift Production PM
4/1/2003 1 1 250000 1
4/2/2003 1 2 350000 0
4/3/2003 1 3 220000 0
4/4/2003 1 1 560000 0
4/5/2003 1 2 545000 0
4/6/2003 1 3 625000 0
4/7/2003 1 1 705000 1
4/8/2003 1 2 785000 0
4/9/2003 1 3 865000 0
4/10/2003 1 1 945000 0
4/11/2003 1 2 1025000 0
4/12/2003 1 3 1105000 0
4/13/2003 1 1 1185000 1
4/14/2003 1 2 1265000 0
4/15/2003 1 3 1345000 0

Thanks,
Leenot sure if this is what you are looking for but give it a go

create table #Tmp(EntryDate datetime, EQuipNum int, Shift int, Production int, PM bit)

insert into #Tmp values('4/1/2003', 1, 1, 250000, 1)
insert into #Tmp values('4/2/2003', 1, 2, 350000, 0)
insert into #Tmp values('4/3/2003', 1, 3, 220000, 0)
insert into #Tmp values('4/4/2003', 1, 1, 560000, 0)
insert into #Tmp values('4/5/2003', 1, 2, 545000, 0)
insert into #Tmp values('4/6/2003', 1, 3, 625000, 0)
insert into #Tmp values('4/7/2003', 1, 1, 705000, 1)
insert into #Tmp values('4/8/2003', 1, 2, 785000, 0)
insert into #Tmp values('4/9/2003', 1, 3, 865000, 0)
insert into #Tmp values('4/10/2003', 1, 1, 945000, 0)
insert into #Tmp values('4/11/2003', 1, 2, 1025000, 0)
insert into #Tmp values('4/12/2003', 1, 3, 1105000, 0)
insert into #Tmp values('4/13/2003', 1, 1, 1185000, 1)
insert into #Tmp values('4/14/2003', 1, 2, 1265000, 0)
insert into #Tmp values('4/15/2003', 1, 3, 1345000, 0)

insert into #Tmp values('4/1/2003', 2, 1, 25000, 1)
insert into #Tmp values('4/2/2003', 2, 2, 35000, 0)
insert into #Tmp values('4/3/2003', 2, 3, 22000, 0)
insert into #Tmp values('4/4/2003', 2, 1, 56000, 0)
insert into #Tmp values('4/5/2003', 2, 2, 54500, 0)
insert into #Tmp values('4/6/2003', 2, 3, 62500, 0)
insert into #Tmp values('4/7/2003', 2, 1, 70500, 1)
insert into #Tmp values('4/8/2003', 2, 2, 78500, 0)
insert into #Tmp values('4/9/2003', 2, 3, 86500, 0)
insert into #Tmp values('4/10/2003', 2, 1, 94500, 0)
insert into #Tmp values('4/11/2003', 2, 2, 102500, 0)
insert into #Tmp values('4/12/2003', 2, 3, 110500, 0)
insert into #Tmp values('4/13/2003', 2, 1, 118500, 1)
insert into #Tmp values('4/14/2003', 2, 2, 126500, 0)
insert into #Tmp values('4/15/2003', 2, 3, 134500, 0)

insert into #Tmp values('4/1/2003', 3, 1, 2500, 1)
insert into #Tmp values('4/2/2003', 3, 2, 3500, 0)
insert into #Tmp values('4/3/2003', 3, 3, 2200, 0)
insert into #Tmp values('4/4/2003', 3, 1, 5600, 0)
insert into #Tmp values('4/5/2003', 3, 2, 5450, 0)
insert into #Tmp values('4/6/2003', 3, 3, 6250, 0)
insert into #Tmp values('4/7/2003', 3, 1, 7050, 1)
insert into #Tmp values('4/8/2003', 3, 2, 7850, 0)
insert into #Tmp values('4/9/2003', 3, 3, 8650, 0)
insert into #Tmp values('4/10/2003', 3, 1, 9450, 0)
insert into #Tmp values('4/11/2003', 3, 2, 10250, 0)
insert into #Tmp values('4/12/2003', 3, 3, 11050, 0)
insert into #Tmp values('4/13/2003', 3, 1, 11850, 1)
insert into #Tmp values('4/14/2003', 3, 2, 12650, 0)
insert into #Tmp values('4/15/2003', 3, 3, 13450, 0)

declare @.dtFrom datetime, @.dtTo datetime
select @.dtFrom = min(EntryDate) from #Tmp where PM = 1
select @.dtTo = min(EntryDate) from #Tmp where PM = 1 and EntryDate > @.dtFrom
while (@.dtFrom is not null and @.dtTo is not null) begin
select @.dtFrom as 'From', @.dtTo as 'To', EquipNum, avg(Production) as 'Average Units' from #Tmp where EntryDate >= @.dtFrom and EntryDate < @.dtTo group by EquipNum
set @.dtFrom = @.dtTo
select @.dtTo = min(EntryDate) from #Tmp where PM = 1 and EntryDate > @.dtFrom
end
select @.dtTo = max(EntryDate) from #Tmp
select @.dtFrom as 'From', @.dtTo as 'To', EquipNum, avg(Production) as 'Average Units' from #Tmp where EntryDate between @.dtFrom and @.dtTo group by EquipNum|||Thanks Paul for the quick response! (had issues at home and had to leave work early, checking this from home)

Please bare with me and don't laugh as I am learning this stuff on the fly and I am somewhat dense!

I think I understand the part where you are actually defining the begin and ending dates by using min() along with AND > first min(). The part I am not sure of is the #Tmp table and how it is used.

The table I will be using has 27 entries per day for the past 5 months. I assume I can create the tmp table using create with a select to populate the tmp table, but do I need to create a tmp table for each PM that has occured for the past 5 months?

I am thinking that you might have created 3 tables to see the different returns from each table as each table has a drop in value by 10. And the fact that I just so happend to have 3 PM's in the time span is just coincidence??

If my assumtion is wrong, then I don't fully understand how it loops through the 3 tables.

Thanks again,
Lee|||Just one table, the "#Tmp" should be the same as your production table, I just didn't know the name.

You should be able to cut and past the code in QA and run it as is or change the table name to your production table name and give it a go.

Basically, The loop looks for the PM dates and does an AVG() for all data between the PM dates grop by EquipNum.

Not very pretty, but works.