Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Update Value but Reduce Amount in Query

Author  Topic 

Zoomer36
Starting Member

13 Posts

Posted - 2014-01-30 : 15:01:02
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 numbers

select a.batch_hdr, A.Order_Qty, A.Sup_Qty_Change, A.Redo
FROM [SupplyChain].[dbo].[XP_PZ_Data_Capture_Main] A
inner join [SupplyChain].[dbo].[XP_PZ_Data_Capture_Main] B
on A.Batch_Hdr = B.Batch_Hdr
and A.Op_Code = B.Op_Code
and A.Redo = B.Redo
where 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.redo
order by A.Sup_Qty_Change


These are the results of that Query:
Batch_Hdr Order_Qty Sup_Qty_Change Redo
LA401656E 1 3 1
LA401656E 1 3 1
LA401656E 4 3 1
LA401656E 1 6 0
LA401656E 1 6 0
LA401656E 4 6 0

So I want to give the 3 to the first 3 and the 6 to the second three.

This is table data that I have

First is the scanned data:

Emp_ID Batch_Hdr Op_Code Sup_Qty_Change Quantity Redo Reason Approver Shift
19475 LA401656E FR 6 0 0 1
19475 LA401656E FR 3 0 1 Damaged 11264 1

Second 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_Qty
19475 1 Ajones FR 15 LA401656E 6 0 0 1 42/4488 1 1
19475 1 Ajones FR 15 LA401656E 6 0 0 2 42/4488 2 1
19475 1 Ajones FR 15 LA401656E 6 0 0 3 42/4488 3 4
19475 1 Ajones FR 15 LA401656E 3 0 1 Damaged 112639 3 42/4488 3 4
19475 1 Ajones FR 15 LA401656E 3 0 1 Damaged 112639 2 42/4488 2 1
19475 1 Ajones FR 15 LA401656E 3 1 1 Damaged 112639 1 42/4488 1 1

As 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!

Zoomer36
Starting Member

13 Posts

Posted - 2014-01-31 : 14:58:00
I know there are lots of sharp, sharp people that can help me with this one. Looking forward to your reply. Thanks!

Have a great day!
Go to Top of Page
   

- Advertisement -