Created
November 9, 2015 15:28
-
-
Save peschkaj/98beff84aff0c9613c49 to your computer and use it in GitHub Desktop.
Converting units with TSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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