Output results based on row comparison?
-
Hello everyone,
I new to this forum, but have browse through it and will be frequenting it regularly.
Now for my question, I have a recordset as follows
PartNo fjobno fnqty_comp fnqty_togo ref foperno
-------- ---------- ----------------- ----------------- ------------------ -------
6000-B20 00017-0000 3.50000 6.50000 00017-00006000-B20 20
6000-B00 00017-0000 .00000 10.00000 00017-00006000-B00 30
6000-000 00017-0000 .00000 10.00000 00017-00006000-000 40
7000-B30 00018-0000 3.50000 6.50000 00018-00007000-B30 20
7000-B20 00018-0000 3.00000 7.00000 00018-00007000-B20 30
7000-B00 00018-0000 .00000 10.00000 00018-00007000-B00 40
7000-000 00018-0000 .00000 10.00000 00018-00007000-000 50
8000-B30 00019-0000 5.00000 5.00000 00019-00008000-B30 20
8000-B20 00019-0000 .00000 10.00000 00019-00008000-B20 30
8000-B00 00019-0000 .00000 10.00000 00019-00008000-B00 40
8000-000 00019-0000 .00000 10.00000 00019-00008000-000 50
This is a recordset showing how many pieces of a certain part have been produced, but if you look at the highlighted data, in order to make PartNo 7000-B20 we have to consume PartNo 7000-B30. SO in this example we actually have on hand .5 PartNo 7000-B30 and 3 PartNo 7000-B20 and oif we made 7000-B00 we would consume 7000-B20 and so on.
I need to output the correct on hand quantities so I can create an on-hand inventiry report. I've tried several methods, like joining the table to itself and using a while loop, but just can not get this to work. Maybe I'm just being brain-cramped and am thinking to much, but any help would be appreciated.
Thanks
Doug
-
Maybe I'm a little dense, but I just can't see where your example dataset shows the linkage between part 7000-B20 and 7000-B30
-
I'm with warey on this. Please explain how the data are linked and give an example of the output you are trying to get to.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Phew! I thought it was just me.
Cheers,
- Mark -
Well the good news is, you have comfirmed that I am not loosing my mind either, thanks.
Now on to the problem,
Lets look at the 2 rows I specified earlier
PartNo JobNo Qty_Comp Qty_Togo Ref OperNo
7000-B30 00018-0000 3.50000 6.50000 00018-00007000-B30 20
7000-B20 00018-0000 3.00000 7.00000 00018-00007000-B20 30
What actually feeds this table is daily labor entries, in which the shop foreman enters how many pieces of what part are produced each day, based on Job Number (JobNo) and Operation Number (OperNo), so I am thinking that these two fields are what links the part numbers.
The Ref column is a concatenation of the JobNo+PartNo, I was experimenting to see if I could find a simpler relationship.
So, I need to somehow evaluate each row to determine if the JobNo is the same, but the OperNo is 10 higher, then subtract the Qty_Comp of the higher OperNo from the Qty_Comp of the lower OperNO for each JobNo.
Each JobNo could have up to eight operations. Of course I would have to loop this logic through the entire table which could grow to be very large in the future as we usually have up to and sometime more than 100 jobs running at any given time.
The output of the above example would need to be as follows:
PartNo Qty_On_Hand
7000-B30 .5
7000-B20 3
Thanks for your help, it is appreciated.
-
You really need another table that links which parts are dependent on other parts being completed. But, using your assumption (if I understand it correctly) you need the following sql, where true_qty is the value you want:
create table #temp (partno varchar(8), fjobno varchar(10), fnqty_comp decimal (10,2),
fnqty_togo decimal(10,2), ref varchar(18), foperno int)
insert into #temp values('6000-B20', '00017-0000', 3.5, 6.5, '', 20)
insert into #temp values('6000-B00', '00017-0000', 0, 10, '', 30)
insert into #temp values('6000-000', '00017-0000', 0, 10, '', 40)
insert into #temp values('7000-B30', '00018-0000', 3.5, 6.5, '', 20)
insert into #temp values('7000-B20', '00018-0000', 3, 7, '', 30)
insert into #temp values('7000-B00', '00018-0000', 0, 10, '', 40)
insert into #temp values('7000-000', '00018-0000', 0, 10, '', 50)
update #temp set ref = fjobno + partno
select t1.*, (t1.fnqty_comp - isnull(t2.fnqty_comp, 0)) as true_qty
from #temp t1
left join #temp t2 on t1.fjobno = t2.fjobno
and t2.foperno = (select min(foperno) from #temp t where t.fjobno = t1.fjobno and t.foperno > t1.foperno)
Hope this was what you meant
Peter Tillotson
-
Peter,
I appreciate your insight and showing me a new way of looking at this problem. It may seem obvious for some people, but once you get to looking at something a certain way it is hard to see it in another light.
Anyways, your query definately has me back on track to get this report finished, but I do have a couple of question I hope won't seem like I am a complete idiot. About your query
select t1.*, (t1.fnqty_comp - isnull(t2.fnqty_comp, 0)) as true_qty
from #temp t1
left join #temp t2 on t1.fjobno = t2.fjobno
and t2.foperno = (select min(foperno) from #temp t where t.fjobno = t1.fjobno and t.foperno > t1.foperno)
I follow what is going on but the syntax I have highlighted has me a little confused. I see the table aliases t1 and t2. Is the "t" a third alias pointing back to the temp table again?
Thanks again
Doug
-
Hey Doug,
Maybe breaking it out will make it easier to see...
(select min(foperno) from #temp t where t.fjobno = t1.fjobno and t.foperno > t1.foperno)
Steve
-
Yes, #temp is refered to 3 times with three different aliases: 't1' & 't2' in the main query and 't' in the sub query. I suppose I could have used t3 as the alias in the subquery to make it more obvious.
Peter
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply