sql server - SQL custom unit conversion -

i looking solution custom unit conversion in sql, database company used microsoft sql server, need write sql return conversion factor based on "unit conversion table"

say:

item: chicken wings (itemid 1001) vendor: food wholesale ltd (vendorid 5000) unitid: gram (id=10) unitid: kilogram (id=500) unitid: boxes (id=305) quantity: 1000 grams = 1kgs = 5 boxs 

unit conversion table:

itemid | vendorid | unit1id | unit2id | quantity1 | quantity2  1001 5000 10 500 1000 1  1001 5000 500 305 1 5 

question: closing stock chicken wings in gram if have 10 boxes

how write sql return "conversion factor"?

thanks in advance

i think recursive table finds path desired unit , desired unit work best. (this assumes if there path a-->b-->c there path c-->b-->a in database. if not modified search both directions).

select  1001 itemid         ,5000 vendorid         ,10 fromunit         ,500 tounit         ,cast(1000 float) fromquantity         ,cast(1 float) toquantity #conversiontable union select  1001         ,5000         ,500         ,305         ,1         ,5 union select 1001         ,5000         ,305         ,500         ,5         ,1 union select  1001         ,5000         ,500         ,10         ,1         ,1000  declare @fromunit int         ,@tounit int         ,@input int set @fromunit = 305 --box set @tounit =  10 --gram set @input = 10  ;with recursivetable (     select  0 levelnum             ,ct.fromunit             ,ct.tounit             ,ct.toquantity / ct.fromquantity multiplicationfactor     #conversiontable ct       ct.fromunit = @fromunit      union      select  levelnum + 1             ,rt.fromunit             ,ct.tounit             ,rt.multiplicationfactor * (ct.toquantity / ct.fromquantity)     #conversiontable ct     inner join recursivetable rt on rt.tounit = ct.fromunit )  select @input * r.multiplicationfactor (     select top 1 * recursivetable      (fromunit = @fromunit     , tounit = @tounit) ) r