I'm currently trying to make a live report with data sent from a SQL server where the machines statistics are logged. For the most part it works fine, but I've encountered an issue today when switching between job numbers and parts produced.
The machine records houlry figures as to what it produced in parts. I then have another table that tells me how many parts go into a box, and so use this to calculate how many boxes are made. Likewise, the scrap numbers are recorded in parts, and I have a table that tells me how much a part weighs and so calculate how much scrap we've lost in weight. I am currently using MAX formula against a date/time key column that translates it into shifts, and the SQL server pushes through that we are now making a different part/order and the start time of that change.
The issue I now have is I want the bar charts that plot this information hourly to retain the old information, while using the new information going forward.
e.g. if we're making a part that is 100 per case for an hour and we make 1000, it will show 10 boxes. However, when the next part comes into play that is 50 per case, it will change the previous hours information to 20 boxes. Likewise with the scrap, the part may have a different weight per unit.
Any advise would be greatly appreciated.
