Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unanswered: 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,
    Lee
    Last edited by clinel; 05-30-03 at 09:05.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    not sure if this is what you are looking for but give it a go

    Code:
    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
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    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

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    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.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •