I have this current query (more info than really needed to show):
SELECT DISTINCT
IBX_Name AS "IBX",
Tank_ID AS "Main Tank ID",
Main_Tank_Index AS "Tank Index",
AST_UST_Level1 AS "Latest Main Tank 1 Level",
AST_UST_Level2 AS "Latest Main Tank 2 Level",
AST_UST_Level3 AS "Latest Main Tank 3 Level",
RunData_Date AS "Last Date"
FROM
RunData
INNER JOIN
IBXs ON IBXs.IBX_ID = RunData.fk_IBX_ID
INNER JOIN
Tanks ON Tanks.fk_IBX_ID = IBX_ID
WHERE
([RunData_Date] = (SELECT MAX([RunData_Date])
FROM RunData AS T1
WHERE T1.fk_IBX_ID = IBX_ID
AND (T1.AST_UST_Level1 > 0 OR T1.AST_UST_Level2 > 0 OR T1.AST_UST_Level3 > 0)))
The query returns this output:
IBX Main Tank ID Tank Index Latest Main Tank 1 Level Latest Main Tank 2 Level Latest Main Tank 3 Level Last Date ---------- ------------ ----------- ------------------------ ------------------------ ------------------------ ---------- DC2 1 1 16128 16192 NULL 2018-11-08 DC2 2 2 16128 16192 NULL 2018-11-08 DC2 38 NULL 16128 16192 NULL 2018-11-08 DC2 39 NULL 16128 16192 NULL 2018-11-08 DC2 40 NULL 16128 16192 NULL 2018-11-08 DC2 41 NULL 16128 16192 NULL 2018-11-08 DC2 42 NULL 16128 16192 NULL 2018-11-08 DC2 43 NULL 16128 16192 NULL 2018-11-08 DC4 3 1 25881 26007 NULL 2020-03-18 DC4 4 2 25881 26007 NULL 2020-03-18 DC4 5 NULL 25881 26007 NULL 2020-03-18 DC4 6 NULL 25881 26007 NULL 2020-03-18 DC4 7 NULL 25881 26007 NULL 2020-03-18 DC4 8 NULL 25881 26007 NULL 2020-03-18 DC4 9 NULL 25881 26007 NULL 2020-03-18 DC4 10 NULL 25881 26007 NULL 2020-03-18 DC4 11 NULL 25881 26007 NULL 2020-03-18 DC5 12 1 24079 23774 NULL 2020-03-13 DC5 13 2 24079 23774 NULL 2020-03-13 DC5 14 NULL 24079 23774 NULL 2020-03-13 DC5 15 NULL 24079 23774 NULL 2020-03-13 DC5 17 NULL 24079 23774 NULL 2020-03-13 DC5 18 NULL 24079 23774 NULL 2020-03-13 DC5 19 NULL 24079 23774 NULL 2020-03-13 DC5 20 NULL 24079 23774 NULL 2020-03-13 DC5 21 NULL 24079 23774 NULL 2020-03-13
I need to modify this query so that it only shows IBX, Tanks ID (Where Index != 0) and its associated Level.
Level 1 goes to Index 1, Level 2 goes to Index 2, and Level 3 goes to Index 3
I’ve tried all sorts of variations, but I cannot get it to work.
Desired output:
IBX Main Tank ID Tank Index Latest Main Tank Level Last Date ---------- ------------ ----------- ---------------------- ---------- DC2 1 1 16128 2018-11-08 DC2 2 2 16192 2018-11-08 DC4 3 1 25881 2020-03-18 DC4 4 2 26007 2020-03-18 DC5 12 1 24079 2020-03-13 DC5 13 2 23774 2020-03-13
Advertisement
Answer
SELECT IBX_Name, Tank_ID, Main_Tank_Index, CASE Main_Tank_Index WHEN 1 THEN AST_UST_Level1 WHEN 2 THEN AST_UST_Level2 WHEN 3 THEN AST_UST_Level3 END AS LatestMainTankLevel, RunData_Date FROM ...