This is a difficult one for me and I need a little help.I have workers scan data into a table through VB.Net. Then I take their batch_hder and search in the Header table and get the orders associated with the batch. I join the two tables so when I have one scanned batch and 3 items on that batch, I now have 3 rows in my Main table. Part of the information I get from the Header table is the Order_Qty for each line of the Batch_Seq. The reason I do this is to end up at the end giving the workers XP credit where they work over standard. I have 2 issues at this point.First, the supervisors have the option of changing a quantity. If the batch has 6 items and there are only 4 in inventory to work with, then the supervisor can change it to 4. If I have 4 for the Sup_Qty_Change and 6 for the Order_Qty, then I can say make the Quantity = Sup_Qty_Change if it is <= Order_Qty. My problem comes when the batch has 3 lines and then the Order_Qty is 1, 1, and 4 totaling 6. I need to be able to update my Quantity to accurately reflect the quantity. So if the Supervisor puts 6 in then I need to split that 6 as 1, 1, 4. If the Supervisors puts 3 in then I need to split that 3 among the batch. Then there is also the issue of the redos where they can get credit again on the same batch.Here is my code that gives me the numbersselect a.batch_hdr, A.Order_Qty, A.Sup_Qty_Change, A.RedoFROM [SupplyChain].[dbo].[XP_PZ_Data_Capture_Main] Ainner join [SupplyChain].[dbo].[XP_PZ_Data_Capture_Main] Bon A.Batch_Hdr = B.Batch_Hdrand A.Op_Code = B.Op_Codeand A.Redo = B.Redowhere A.Batch_Hdr = B.Batch_Hdr and A.Op_Code = B.Op_Code and A.Sup_Qty_Change > 0 and a.batch_hdr = 'LA401656E'group by A.Batch_Hdr, A.Op_Code, A.Employee_ID, A.Sup_Qty_Change, A.Order_Qty, A.Batch_Seq, A.redoorder by A.Sup_Qty_Change
These are the results of that Query:Batch_Hdr Order_Qty Sup_Qty_Change RedoLA401656E 1 3 1LA401656E 1 3 1LA401656E 4 3 1LA401656E 1 6 0LA401656E 1 6 0LA401656E 4 6 0So I want to give the 3 to the first 3 and the 6 to the second three.This is table data that I haveFirst is the scanned data:Emp_ID Batch_Hdr Op_Code Sup_Qty_Change Quantity Redo Reason Approver Shift19475 LA401656E FR 6 0 0 119475 LA401656E FR 3 0 1 Damaged 11264 1Second is the joined Data:Emp_ID Shift Emp_name Op_Code PV_Number Batch_Hdr Sup_Qty_Change Quantity Redo Reason Approver Batch_Seq Item# Detail_Seq Order_Qty19475 1 Ajones FR 15 LA401656E 6 0 0 1 42/4488 1 119475 1 Ajones FR 15 LA401656E 6 0 0 2 42/4488 2 119475 1 Ajones FR 15 LA401656E 6 0 0 3 42/4488 3 419475 1 Ajones FR 15 LA401656E 3 0 1 Damaged 112639 3 42/4488 3 419475 1 Ajones FR 15 LA401656E 3 0 1 Damaged 112639 2 42/4488 2 119475 1 Ajones FR 15 LA401656E 3 1 1 Damaged 112639 1 42/4488 1 1As you can see my join may give me the right number of rows but working with those quantity changes is rough.At the end, I will count how many PV_Number 15s the worker has for the report. That is why I did that join to grow the table.Any and all Help is greatly Appreciated!!!Have a great day!
Update Value but Reduce Amount in Query
Posted - 2014-01-30 : 15:01:02
