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’;

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