Convert custom SQL units

Question

I am looking for a solution for custom unit conversion to SQL, in the database used by my company, Microsoft SQL Server, I need to write SQL to return the conversion factor based on the "unit conversion table"

they 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: What is the closing stock of chicken wings in grams if I have 10 boxes

How to write this sql to return the "conversion coefficient"?

Thanks in advance

Answer 1

I think that a recursive table that finds a path from your desired unit to the desired device will work best. Something like this (It is assumed that if there is a path a → b → c, the database also has a path c → b → a. If it cannot be changed to search in both directions).

 select 1001 as itemID ,5000 as vendorID ,10 as fromUnit ,500 as toUnit ,cast(1000 as float) as fromQuantity ,cast(1 as float) as toQuantity into #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 as ( select 0 as LevelNum ,ct.fromUnit ,ct.toUnit ,ct.toQuantity / ct.fromQuantity as multiplicationFactor from #conversionTable ct where ct.fromUnit = @fromUnit union all select LevelNum + 1 ,rt.fromUnit ,ct.toUnit ,rt.multiplicationFactor * (ct.toQuantity / ct.fromQuantity) from #conversionTable ct inner join recursiveTable rt on rt.toUnit = ct.fromUnit ) select @input * r.multiplicationFactor from ( select top 1 * from recursiveTable where (fromUnit = @fromUnit and toUnit = @toUnit) ) r 

Answer 2

I would use a conversion table and enter all the combinations. So even if 5000g → 5kg → 1 box, I would put a gram → envelopes in a box. Something like that:

 create table unit_unit_conv( from_unit varchar(10) not null ,to_unit varchar(10) not null ,rate decimal(10,6) not null ,primary key(from_unit, to_unit) ); insert into unit_unit_conv values('kilogram', 'kilogram', 1); insert into unit_unit_conv values('kilogram', 'gram', 1000); insert into unit_unit_conv values('kilogram', 'box', 0.2); insert into unit_unit_conv values('gram', 'gram', 1); insert into unit_unit_conv values('gram', 'kilogram', 0.001); insert into unit_unit_conv values('gram', 'box', 0.0002); insert into unit_unit_conv values('box', 'box', 1); insert into unit_unit_conv values('box', 'kilogram', 5); insert into unit_unit_conv values('box', 'gram', 5000); 

So, whatever unit you have, you can convert it to any unit by multiplying the amount you have with the speed column in this table. Therefore, if you have a table of such elements:

 create table items( item_id varchar(10) not null ,item_qty int not null ,item_qty_unit varchar(10) ); insert into items values('chicken', 5, 'kilogram'); insert into items values('babies', 5000, 'gram'); insert into items values('beef', 1, 'box'); 

... and you want to convert everything to fields, you must query the data as follows:

 select i.item_id ,i.item_qty as qty_original ,item_qty_unit as qty_unit_original ,i.item_qty * c.rate as box_qty from items i join unit_unit_conv c on(i.item_qty_unit = c.from_unit) where c.to_unit = 'box'; +---------+--------------+-------------------+----------+ | item_id | qty_original | qty_unit_original | box_qty | +---------+--------------+-------------------+----------+ | chicken | 5 | kilogram | 1.000000 | | babies | 5000 | gram | 1.000000 | | beef | 1 | box | 1.000000 | +---------+--------------+-------------------+----------+ 

Answer 3

The following solution is tested on SQL Server 2012. To reduce the size of the code on the page, I only supply bulk measurements, as they are tested and work.

 CREATE TABLE [Measurement type] ( [Type ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL, [Type Name] NVARCHAR(30) NOT NULL ) CREATE TABLE [Measurement unit] ( [Unit ID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL, [Type ID] INT REFERENCES [Measurement type]([Type ID]) NOT NULL, [Unit name] NVARCHAR(30) NOT NULL, [Unit symbol] NVARCHAR(10) NOT NULL ) /* Use both multiplier and divizor to reduce rounding errors */ CREATE TABLE [Measurement conversions] ( [Type ID] INT NOT NULL REFERENCES [Measurement type]([Type ID]), [From Unit ID] INT NOT NULL REFERENCES [Measurement unit]([Unit ID]), [To Unit ID] INT NOT NULL REFERENCES [Measurement unit]([Unit ID]), [From Unit Offset] FLOAT NOT NULL DEFAULT(0), [Multiplier] FLOAT NOT NULL DEFAULT(1), [Divizor] FLOAT NOT NULL DEFAULT(1), [To Unit Offset] FLOAT NOT NULL DEFAULT(0), PRIMARY KEY ([Type ID], [From Unit ID], [To Unit ID]) ) INSERT INTO [Measurement type]([Type ID], [Type Name]) VALUES(4, 'Mass') INSERT INTO [Measurement unit]([Unit ID], [Type ID], [Unit name], [Unit symbol]) VALUES (28, 4, 'Milligram', 'mg'), (29, 4, 'Gram', 'g'), (30, 4, 'Kilogram', 'kg'), (31, 4, 'Tonne', 't'), (32, 4, 'Ounce', 'oz'), (33, 4, 'Pound', 'lb'), (34, 4, 'Stone', 's'), (35, 4, 'hundred weight', 'cwt'), (36, 4, 'UK long ton', 'ton') INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [Multiplier], [Divizor]) VALUES (4, 28, 29, 1, 1000), (4, 28, 30, 1, 1000000), (4, 28, 31, 1, 1000000000), (4, 28, 32, 1, 28350), (4, 32, 33, 1, 16), (4, 32, 34, 1, 224), (4, 32, 35, 1, 50802345), (4, 32, 36, 1, 35840) INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [From Unit Offset], [Multiplier], [Divizor], [To Unit Offset]) SELECT DISTINCT [Measurement Conversions].[Type ID], [Measurement Conversions].[To Unit ID], [Measurement Conversions].[From Unit ID], -[Measurement Conversions].[To Unit Offset], [Measurement Conversions].[Divizor], [Measurement Conversions].[Multiplier], -[Measurement Conversions].[From Unit Offset] FROM [Measurement Conversions] -- LEFT JOIN Used to assure that we dont try to insert already existing keys. LEFT JOIN [Measurement conversions] AS [Existing] ON [Measurement Conversions].[From Unit ID] = [Existing].[To Unit ID] AND [Measurement Conversions].[To Unit ID] = [Existing].[From Unit ID] WHERE [Existing].[Type ID] IS NULL 

Run the following query until it affects 0 rows.

 INSERT INTO [Measurement conversions]([Type ID], [From Unit ID], [To Unit ID], [From Unit Offset], [Multiplier], [Divizor], [To Unit Offset]) SELECT DISTINCT [From].[Type ID], [From].[To Unit ID] AS [From Unit ID], [To].[To Unit ID], -[From].[To Unit Offset] + (([To].[From Unit Offset]) * [From].[Multiplier] / [From].Divizor) AS [From Unit Offset], [From].[Divizor] * [To].[Multiplier] AS Multiplier, [From].[Multiplier] * [To].[Divizor] AS Divizor, [To].[To Unit Offset] - (([From].[From Unit Offset]) * [To].[Multiplier] / [To].Divizor) AS [To Unit Offset] FROM [Measurement conversions] AS [From] CROSS JOIN [Measurement conversions] AS [To] -- LEFT JOIN Used to assure that we dont try to insert already existing keys. LEFT JOIN [Measurement conversions] AS [Existing] ON [From].[To Unit ID] = [Existing].[From Unit ID] AND [To].[To Unit ID] = [Existing].[To Unit ID] WHERE [Existing].[Type ID] IS NULL AND [From].[Type ID] = [To].[Type ID] AND [From].[To Unit ID] <> [To].[To Unit ID] AND [From].[From Unit ID] = [To].[From Unit ID] 

Finally, to reset multisets and dividers that cancel each other out:

 UPDATE [Measurement conversions] SET [Multiplicand] = 1, [Dividend] = 1 WHERE [Multiplicand] = [Dividend] 

Answer 4

Now it is complicated. It looks like you will need a recursive selection or cursor to solve it. Basically, you want to select from the unit conversion table where itemid = @desiredId and vendorid = @desiredVendor and unit2id = @finalUnitId. Then you want to run the same request, but where unit2id = unit1id from the request you requested is up to unit1id = @originalUnitId. Keeping the conversion factor all the time.

So, start with something like this:
declare @factor float
set @factor = 0
select unit1id, quantity1, quantity2 from unitconversion where itemid = 1001 and vendorid = 5000 and unit2id = 305
set @factor = @factor + quantity1 / quantity 2

Then you want to check whether unit1id will be selected from the unit you want to end on (in the example, you check if unit1id = 10). If not, repeat the same query, but limit unit2id to 10.

This is just a rough plan of how I will do this. There are a few assumptions made here, mainly if you follow the unit chain that the previous block is always smaller than the unit you are testing, but I think this may be enough to convey the essence of this. This is likely to be best implemented as a UDF, which returns a coefficient based on the components of the product, supplier, start and end. I would use a while statement.