I have this problem that I have been trying to solve with vbscript without a great sucess.
What I have is two tables. the first is a table with production numbers, the 2nd is a table that contains the last time that an event occurred for a particular production line and for a specific task. each time an event is done, this table is updated. each task should be done after X amount of production is reached. what i need to do is give the user the point at which they are at at any given time
Line Number Event Pos Install Date Production
1 Cleaning A 4/1/2004 1,200,000
1 Painting A 4/1/2004 1,200,000
1 Scraping B 4/2/2004 900,000
2 Cleaning A 4/2/2004 850,000
2 Painting A 4/2/2004 850,000
2 Scraping B 4/3/2004 750,000
What has to happen is that I must match up the line and event from the event table with the line in the production table and sum the production where RemoveDate (which becomes the new install date) is greater than the production date. then i need to take the amount of production done on the date that is equal to the removal date and add it to the total minus any production done prior to the RemoveShift (which becomes the new install date). our production begins with the 3rd shift followed by 1st then second.
So the calcuation would look something like this for Line 1 for cleaning
Total production for EntryDate >= 4/2/2003 = 12792750
Production for the EntryDate = 4/1/2003 = 280800
with 100000 on 3rd, 50000 on 1st, and 130800 on 2nd. there was 3300 ran before the cleaning started on 1st shift. so the net is 1st shift production 50000 - 3300 + 2nd shift production 130800 + the total ran from 4/2/2003 foward for a total of 12970250
Summary need to calculate on the fly where each line is on production for each event. must back out any production done prior to work. production for each day is order like this 3rd, 1st, 2nd. In my table RemoveDate and RemoveShift is the day that that event occured as I have another table that the net is entered into for historical data. each time an event occurs the EventDate table gets updated.
My Question is can this be done in SQL? If so how do I go about it?
here are DDL for the two tables, they only have 3 shifts of production data for lines 1 and 2 for date that the events occured.
insert into tblProd values('4/1/2003', '3/28/2003', 1, 1, 3300, Cleaning, A)
insert into tblProd values('4/1/2004', '3/28/2004', 1, 1, 3300, Painting, A)
insert into tblProd values('4/2/2004', '3/27/2004', 1, 2, 200, Scraping, B)
insert into tblProd values('4/2/2004', '3/27/2004', 2, 3, 300, Cleaning, A)
insert into tblProd values('4/2/2004', '3/27/2004', 2, 3, 300, Painting, A)
insert into tblProd values('4/3/2004', '3/28/2004', 2, 1, 200, Scraping, B)
insert into tblProd values('4/3/2004', '3/28/2004', 3, 2, 350, Cleaning, A)
insert into tblProd values('4/1/2004', '3/28/2004', 3, 2, 350, Painting, A)
insert into tblProd values('4/2/2004', '3/27/2004', 3, 3, 100, Scraping, B)