Converting Measurements with T-SQL

I frequently find myself having to perform calculations on the source data based on a range of user supplied values. Usually there will be some kind of target and destination parameter supplied. So, for example, the users will want to see total sales in terms of burgers sold or something like that. Here’s where it gets tricky. Let’s say I own two franchises – one that sells burgers and one that sells tacos. I don’t want to know what my taco stand profits look like in terms of burgers. I want to see that number in terms of tacos.

So, what’s the best way to go about this?

Well, in order to get started you’re going to need the fn_split function to split strings and you’ll need a numbers table to create fn_split.

Once you have created your numbers table and the fn_split function you’ll want to set up some data to get started.

IF OBJECT_ID('conversions') IS NOT NULL
  DROP TABLE conversions;

IF OBJECT_ID('measurements') IS NOT NULL
  DROP TABLE measurements;

CREATE TABLE conversions
(
  convert_from INT,
  convert_to INT,
  factor DECIMAL (5, 1)
);

CREATE TABLE measurements
(
  m_type INT,
  measurement DECIMAL(18, 5)
);

INSERT INTO conversions
SELECT 1, 1, 1
 UNION ALL
SELECT 1, 2, 0.5
 UNION ALL 
SELECT 1, 3, 4
 UNION ALL
SELECT 2, 1, 2
 UNION ALL
SELECT 2, 2, 1
 UNION ALL
SELECT 2, 3, 9
 UNION ALL
SELECT 3, 1, 4;

INSERT INTO measurements
SELECT 1, 50.5
 UNION ALL
SELECT 1, 100
 UNION ALL
SELECT 2, 93
 UNION ALL
SELECT 2, 76
 UNION ALL
SELECT 3, 56;

If you take a look at the data, you’ll see that there are a lot of possible combinations out there even with just this limited data set:

SELECT * 
  FROM measurements AS m
       INNER JOIN conversions AS c
          ON m.m_type = c.convert_from

What’s the best way to limit these results so we aren’t writing strange where clauses? JOINs, of course!

Let’s say that our user wants to see all measurements with a type of 1 converted to the measurement of type 2 and measurements of type 2 converted to type 3 (this doesn’t really make much sense, I know, but business rules don’t have to make sense). Here’s what our input might look like:

DECLARE @source AS NVARCHAR(50);
DECLARE @target AS NVARCHAR(50);

SET @source = '1,2';
SET @target = '2,3';

Rather annoyingly, the best way I’ve found part from using comma delimited tuples is to do positional processing of data. Fortunately, the fn_split function supplies a position number as one of the output columns which makes keeping track of this sort of thing infinitely easier.

So, in order to move forward here’s what we do:

  1. Load @source into a temporary table
  2. Load @target into a temporary table
  3. Join #sources to #targets to create a master conversion table
  4. Profit!
IF OBJECT_ID('tempdb..#sources') IS NOT NULL
  DROP TABLE #sources;

IF OBJECT_ID('tempdb..#targets') IS NOT NULL
  DROP TABLE #targets;

IF OBJECT_ID('tempdb..#convert') IS NOT NULL
  DROP TABLE #convert;

SELECT pos, element AS c_from
  INTO #sources
  FROM dbo.fn_split(@source, ',');

SELECT pos, element AS c_to
  INTO #targets
  FROM dbo.fn_split(@target, ',');

SELECT s.pos, s.c_from, t.c_to
  INTO #convert
  FROM #sources AS s
       INNER JOIN #targets AS t
          ON s.pos = t.pos

Woohoo, we’re almost there. But how do we profit from this? Well, we use that query we wrote right after we filled the sample tables with data and we JOIN on the #convert table:

SELECT m.measurement AS original_measurement,
       (m.measurement * c.factor) AS converted_measurement
  FROM measurements AS m
       INNER JOIN conversions AS c
          ON m.m_type = c.convert_from
       INNER JOIN #convert AS conv
          ON c.convert_from = conv.c_from
         AND c.convert_to = conv.c_to

Through the magic of joins we’ve managed to convert more than one type of thing to another type of thing without having to resort to UNIONs (the insert doesn’t really count).

You also can download the entire example source code in a single file, rather than copying and pasting like a fiend: converting-measurements.sql

Menu