Script to display parent cost and child cost

Hi Rico Towers

I'm sure there must be a way to do this with a self-join but my SQL skills are a little rusty - but you can achieve this with a couple of views (which are IMO one of the coolest things about the database)

One view creates the "parent table" the other the "child table" with your calculated cost

create view vparentcost as select i.id, i.itemlookupcode as ParentILC, i.description as ParentDesc, i.cost as ParentCost from item i where i.id in (select parentitem from item where parentitem 0)

create view vchildrencost as select i.itemlookupcode, i.description, vparentcost.parentcost, i.parentquantity as ChildParentQty, cast(vparentcost.parentcost/i.parentquantity as money) as CalculatedChildCost from item i INNER JOIN VPARENTCOST ON i.PARENTITEM = VPARENTCOST.ID where i.parentitem 0

Test your results by running SELECT * FROM VPARENTCOST and SEECT * FROM VCHILDRENCOST

The views can also probably be called on RMS reports, so you could incorporate them onto a custom report if need be.

H> I'm trying to create a SQL script where I can display the Parent item and

Very true!

Thanx

"c> Hi Rico Towers