SQL custom unit conversion

I am looking for a solution for a custom unit conversion in SQL, the database my company used is Microsoft SQL server, I need to 开发者_如何转开发write a SQL to return a conversion factor based on a "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: What is the closing stock for chicken wings in gram if I have 10 boxes

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

Thanks in advance

I would use a conversion table and put in all combinations. So even if 5000g ->5kg -> 1 box, I would put gram -> box conversions as well. Something like this:

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 of measure you have, you can convert it into any unit by multiplying the quantity you have with the rate column in this table. So if you have a table of items like this:

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 boxes, you would query the data like this:

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 |
+---------+--------------+-------------------+----------+

The following solution is tested on SQL server 2012. To reduce Code size on the page, I only supply the Mass measurements since these are tested and working.

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 multiplicands and divizors that cancel each other out:

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

I think a recursive table that finds a path from your desired from unit and to the desired to unit would work best. Something like this (This assumes that if there is a path a-->b-->c there is also a path c-->b-->a in the database. If not it could be modified to search 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

Now that's a tricky one. It looks like you are going to need a recursive-select, or a cursor to solve it. Basically, what you want to do is select from the unit conversion table where itemid = @desiredId and vendorid = @desiredVendor and unit2id = @finalUnitId. Then, you will want to run the same query, but where unit2id = unit1id from the query you just ran -- until unit1id = @originalUnitId. All the while, keeping a running conversion-factor.

So, begin 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 will want to check to see if unit1id selected from the above equals the unit you want to end up with (in you example, you are checking to see if unit1id = 10). If not, run the same query again, but restrict for a unit2id of 10.

This is just a rough outline of how I would do it. There are a few assumptions made here, mainly that if you follow the chain of units that the previous unit is always smaller than the unit you are testing for, but I think this may be enough to convey the gist of it. This will probably work out best implemented as a UDF that returns the factor based on the product, vendor, start, and end units. I would use a while statement.