Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Converting units with TSQL
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;
SELECT *
FROM measurements AS m
INNER JOIN conversions AS c
ON m.m_type = c.convert_from
DECLARE @source AS NVARCHAR(50);
DECLARE @target AS NVARCHAR(50);
SET @source = '1,2';
SET @target = '2,3';
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
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.