June 26, 2007 at 7:58 pm
I have a list of transactions which define the recipe for a manufactured product. I have no other history, and I need to extract the historical recipes from these transactions. I have no useful attributes to group these (like a job number or transaction batch number). The records are as such:
----------
trans_num | trans_type | trans_date | item | qty
1 | F | 1/7/04 | ITEM1 | 250
2 | I | 1/7/04 | ITEM2 | 25
3 | I | 1/7/04 | ITEM3 | 20
4 | I | 1/7/04 | ITEM4 | 200
5 | I | 1/7/04 | ITEM5 | 10
6 | F | 1/7/04 | ITEM78 | 500
7 | I | 1/7/04 | ITEM2 | 25
8 | I | 1/7/04 | ITEM3 | 20
9 | I | 1/7/04 | ITEM6 | 2000
10 | I | 1/7/04 | ITEM5 | 10
--------
All the 'F' type transactions are the finished product, and all the 'I' type transactions are the items which were used to manufacture the last 'F' type transaction. I want to capture all unique recipes - I'd guess about 90% of the recipes will be the same, but I want to see when the recipes change.
I want output like this:
--------
finished_item | ingredient_item | qty | obsolete_date
--------
The obsolete date will be the last time that recipe was used before the recipe changed.
I don't have much of an idea where to start, since I get stuck at working out the repetition for the finished_item column. Can anybody help me out?
June 26, 2007 at 10:58 pm
Please provide DDL and sample data as described at http://www.aspfaq.com/etiquette.asp?id=5006
"The obsolete date will be the last time that recipe was used before the recipe changed"
This is not clear and without DDL, sample data and expected results, no solution is given.
create table OLIST
( trans_num integer not null primary key
, trans_type char(1) not null
, trans_date datetime not null
, item varchar(30) not null
, qty integer not null
)
insert into OLIST
( trans_num, trans_type, trans_date, item, qty)
select 1 , 'F' , '1/7/04', 'ITEM1' , 250 union all
select 2 , 'I' , '1/7/04', 'ITEM2' , 25 union all
select 3 , 'I' , '1/7/04', 'ITEM3' , 20 union all
select 4 , 'I' , '1/7/04', 'ITEM4' , 200 union all
select 5 , 'I' , '1/7/04', 'ITEM5' , 10 union all
select 6 , 'F' , '1/7/04', 'ITEM78' , 500 union all
select 7 , 'I' , '1/7/04', 'ITEM2' , 25 union all
select 8 , 'I' , '1/7/04', 'ITEM3' , 20 union all
select 9 , 'I' , '1/7/04', 'ITEM6' , 2000 union all
select 10 , 'I' , '1/7/04', 'ITEM5' , 10
go
What you have provided is an object-oriented ordered list and must first be converted into a normalized tables.
This SQL creates a table of Ingredients and the Finished Good trans_num:
SELECTingredient.trans_num as Ingredient_item
,MAX(FinishedGood.trans_num) as finished_item
from OLIST as Ingredient
cross join OLIST as FinishedGood
where ingredient.trans_num >= FinishedGood.trans_num
andFinishedGood.trans_type = 'F'
group by ingredient.trans_num
Now join the result of the above SQL to the OLIST table to get the desired result:
selectFinishedGoodIngredient.finished_item
, OLIST.item
, olist.qty
, OLIST.trans_date
from(
SELECTIngredient.trans_num as Ingredient_item
,MAX(FinishedGood.trans_num) as finished_item
from OLIST as Ingredient
cross join OLIST as FinishedGood
where ingredient.trans_num >= FinishedGood.trans_num
andFinishedGood.trans_type = 'F'
group by ingredient.trans_num
)As FinishedGoodIngredient
joinOLIST
onOLIST.trans_num = FinishedGoodIngredient.Ingredient_item
order by FinishedGoodIngredient.finished_item
, OLIST.item
SQL = Scarcely Qualifies as a Language
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply