Easiest way to explain this requirement is to detail its origin. Have invoice with items. Items match back to manufacturing production runs. When qty of items on invoice exceeds production run qty must assign a second production run to match invoice. Other factors also involved, such as lot numbers for raw materials used. But for report purposes it works out to adding another new production run to match invoice when the raw material from one lot runs out and is replaced by the next raw material lot during that production run. That is when a class 1 item is used up from a lot number, then for reporting purposes, it is considered a separate production run for the finished item being produced and then sold and invoiced to a customer. So for report may have:
Need to add a column to assign a unique identifier to each item production run based on the first report as shown above. The number of production runs per item has never exceeded 5. The results of the new query needed would look like:
Since these are coming from an invoice, the invoice no is the same for all the items so can identify just the Doodad items, for
COUNT(Doodad).
Also prdn_run has unique lot numbers so can identify separate prdn_run.
Can you use cascading CASE statement for logic to assign a prdn_run_no: i.e.
CASE WHEN COUNT(Doodad)= '5'
THEN ....
ELSE WHEN COUNT(Doodad)='4'
THEN...
Hi Mr. L,
Not sure exactly what you are requesting, but will try to explain. Apologies if my response seems remedial, only 3mos exp with mysql, so have yet to pay my dues in the trade.
Because the invoice item quantities cover quantities that exceed production runs, and/or use the last quantities of one production run and then start pulling parts from the next production run, for report requirements identifying from which production run the items shipped were taken from is necessary. The report/view already in place pulls up multiple production runs. Need to add to the report/view a column then update that column with a number identifying each production run based on the total number of production runs associated wtih the invoice item: 0 to 5(maximum). The report/view is then sorted by different criteria at which point the adding of an extra field almost makes sense (mine is not to reason why…) from a human reading the report sort of a perspective.
So what is needed is to be able to add a column, to each row and then update that column with a number based on which production run the items came from. It is LIFO, last in first out, so the last run, chronologically, would be 0, the one before that would be 1… up to 5 possibilities.
Using php could first put in order of production date using a while loop based on the invoice number and item with conditions to sort the production runs, then add the column to the table and update the records in the new column using another while loop with i++ or something like it. Not sure how to do that with SQL, or more specifically MySQL 5.1.
Not really looking for specific code, but rather a general approach to solving this problem using just SQL.
Again my apologies if I answered every question on the planet but the one you were asking.
this seems like it’s going to be a one-time-only thing, so i would suggest you do it with php code, in a loop if necessary, updating one row at a time with its new production run number
Yes that is what I’ve been saying for a while. Easy to do with a conditional loop. As a matter of fact, the reason that this is being done like that is due to migrating from… wait for it… foxpro. And we are talking pre Visual FoxPro here. Can you say DOS? Finally got a hold of the old code from foxpro. And here is the important part
INDEX ON ;
ALLTRIM(SKU)+ALLTRIM(FABNO) ;
TO t1
SCAN
STORE ALLTRIM(SKU) TO ment
STORE 1 TO mct
DO WHILE NOT EOF() AND ALLTRIM(SKU)=m.ment
REPLACE Blineno WITH m.mct
STORE m.mct+1 TO mct
SKIP
IF EOF() OR ALLTRIM(SKU)!=m.ment
SKIP -1
EXIT
ENDIF
ENDDO
ENDSCAN
Questions:
Does MySql have any scritping capacity that could be used to accomplish something like this ?
If not, can you call a script from MySQL running a script to handle it?
What about doing a custom function?
No worries. Its all good. Checking out the IF statement in MySQL. Not a lot to work with, but am beginning to get a mental picture of how to get it done if I can’t talk people into doing it using proper accounting standards. For instance not hard to match on production run qty verses invoice qty by date using an aggregate function, which of course holds up under an audit as apposed to doing the finger and scratch pad match from the report using -1, -2 to reference different technical runs which are then looked up by shuffling through the report and then the Jethro math on the scratch pads. Might wind up being the logic used with the if statement anyway.
Thanks again for following it out. A privilege to post with you. Have a great one!
