I am a beginner SQL user. I wrote a query to extract several Fuel emissions columns and wrote a display message to show if it meets the requirements (Previous emission – Fuel Emission = Fuel consumption). This resulted in many columns based on the 8 different fuel types and Google suggested using UNPIVOT. I want to convert the many columns to just Fuel Type, Previous Emission, Fuel Emission, Fuel Consumption, and Message (which I call Miscellaneous here).
The following is my attempt:
With FuelCheckResults AS (
select *,
case
when t3.total_HSFO_emission is null or t3.previous_HFO_emission is null or t3.total_HFO is null then 'NULL VALUE ERROR'
when t3.previous_HFO_emission - t3.total_HSFO_emission = t3.total_HFO then 'valid'
else CONCAT('not valid emission, ', 'previous HFO emission: ', FORMAT(t3.previous_HFO_emission,'N2'), '; total HFO: ', FORMAT(t3.total_HFO,'N2'), '; total HSFO emission: ', FORMAT(t3.total_HSFO_emission,'N2'),'; Variance in HSFO emission ', FORMAT((t3.previous_HFO_emission - t3.total_HSFO_emission - t3.total_HFO),'N2') )
end as fuel_HSFO_emission_check,
case
when t3.total_LSFO_emission is null or t3.previous_LSFO_emission is null or t3.total_LSFO is null then 'NULL VALUE ERROR'
when t3.previous_LSFO_emission - t3.total_LSFO_emission = t3.total_LSFO then 'valid'
else CONCAT('not valid emission, ', 'previous LSFO emission: ', FORMAT(t3.previous_LSFO_emission,'N2'), '; total LSFO: ', FORMAT(t3.total_LSFO,'N2'), '; total LSFO emission: ', FORMAT(t3.total_LSFO_emission,'N2'), '; Variance in LSFO emission ', FORMAT((t3.previous_LSFO_emission - t3.total_LSFO_emission - t3.total_LSFO),'N2'))
end as fuel_LSFO_emission_check,
case
when t3.total_VLSFO_emission is null or t3.previous_VLSFO_emission is null or t3.total_VLSFO is null then 'NULL VALUE ERROR'
when t3.previous_VLSFO_emission - t3.total_VLSFO_emission = t3.total_VLSFO then 'valid'
else CONCAT('not valid emission, ', 'previous VLSFO emission: ', FORMAT(t3.previous_VLSFO_emission,'N2'), '; total VLSFO: ', FORMAT(t3.total_VLSFO,'N2'), '; total VLSFO emission: ', FORMAT(t3.total_VLSFO_emission,'N2'), '; Variance in VLSFO emission ', FORMAT(( t3.previous_VLSFO_emission - t3.total_VLSFO_emission - t3.total_VLSFO),'N2'))
end as fuel_VLSFO_emission_check,
case
when t3.total_MGO_emission is null or t3.previous_MGO_emission is null or t3.total_MGO is null then 'NULL VALUE ERROR'
when t3.previous_MGO_emission - t3.total_MGO_emission = t3.total_MGO then 'valid'
else CONCAT('not valid emission, ', 'previous MGO emission: ', FORMAT(t3.previous_MGO_emission,'N2'), '; total MGO: ', FORMAT(t3.total_MGO,'N2'), '; total MGO emission: ', FORMAT(t3.total_MGO_emission,'N2'), '; Variance in MGO emission ', FORMAT((t3.previous_MGO_emission - t3.total_MGO_emission - t3.total_MGO),'N2'))
end as fuel_MGO_emission_check,
case
when t3.total_MDO_emission is null or t3.previous_MDO_emission is null or t3.total_MDO is null then 'NULL VALUE ERROR'
when t3.previous_MDO_emission - t3.total_MDO_emission = t3.total_MDO then 'valid'
else CONCAT('not valid emission, ', 'previous MDO emission: ', FORMAT(t3.previous_MDO_emission,'N2'), '; total MDO: ', FORMAT(t3.total_MDO,'N2'), '; total MDO emission: ', FORMAT(t3.total_MDO_emission,'N2'), '; Variance in MDO emission ', FORMAT((t3.previous_MDO_emission - t3.total_MDO_emission - t3.total_MDO),'N2'))
end as fuel_MDO_emission_check,
case
when t3.total_co2_emission is null or t3.previous_co2_emission is null or t3.total_co2 is null then 'NULL VALUE ERROR'
when t3.previous_co2_emission - t3.total_co2_emission = t3.total_co2 then 'valid'
else CONCAT('not valid emission, ', 'previous co2 emission: ', FORMAT(t3.previous_co2_emission,'N2'), '; total co2: ', FORMAT(t3.total_co2,'N2'), '; total co2 emission: ', FORMAT(t3.total_co2_emission,'N2'), '; Variance in co2 emission ', FORMAT((t3.previous_co2_emission - t3.total_co2_emission - t3.total_co2),'N2'))
end as fuel_co2_emission_check,
case
when t3.total_no2_emission is null or t3.previous_no2_emission is null or t3.total_no2 is null then 'NULL VALUE ERROR'
when t3.previous_no2_emission - t3.total_no2_emission = t3.total_no2 then 'valid'
else CONCAT('not valid emission, ', 'previous no2 emission: ', FORMAT(t3.previous_no2_emission,'N2'), '; total no2: ', FORMAT(t3.total_no2,'N2'), '; total no2 emission: ', FORMAT(t3.total_no2_emission,'N2'), '; Variance in no2 emission ', FORMAT((t3.previous_no2_emission - t3.total_no2_emission - t3.total_no2),'N2'))
end as fuel_no2_emission_check,
case
when t3.total_LNG_emission is null or t3.previous_LNG_emission is null or t3.total_LNG is null then 'NULL VALUE ERROR'
when t3.previous_LNG_emission - t3.total_LNG_emission = t3.total_LNG then 'valid'
else CONCAT('not valid emission, ', 'previous LNG emission: ', FORMAT(t3.previous_LNG_emission,'N2'), '; total LNG: ', FORMAT(t3.total_LNG,'N2'), '; total LNG emission: ', FORMAT(t3.total_LNG_emission,'N2'), '; Variance in LNG emission ', FORMAT((t3.previous_LNG_emission - t3.total_LNG_emission - t3.total_LNG),'N2'))
end as fuel_LNG_emission_check
from (
select *,
lag(t2.total_HSFO_emission) over (partition by t2.car_carPlate order by t2.Report_DateTime_UTC) as previous_HFO_emission,
lag(t2.total_LSFO_emission) over (partition by t2.car_carPlate order by t2.Report_DateTime_UTC) as previous_LSFO_emission,
lag(t2.total_VLSFO_emission) over (partition by t2.car_carPlate order by t2.Report_DateTime_UTC) as previous_VLSFO_emission,
lag(t2.total_MGO_emission) over (partition by t2.car_carPlate order by t2.Report_DateTime_UTC) as previous_MGO_emission,
lag(t2.total_MDO_emission) over (partition by t2.car_carPlate order by t2.Report_DateTime_UTC) as previous_MDO_emission,
lag(t2.total_co2_emission) over (partition by t2.car_carPlate order by t2.Report_DateTime_UTC) as previous_co2_emission,
lag(t2.total_no2_emission) over (partition by t2.car_carPlate order by t2.Report_DateTime_UTC) as previous_no2_emission,
lag(t2.total_LNG_emission) over (partition by t2.car_carPlate order by t2.Report_DateTime_UTC) as previous_LNG_emission
from (
select t1.id, t1.car_carPlate, t1.car_carName, t1.Report_DateTime_UTC,t1.Report_Type_Desc, t1.Master_Report_ID,
t1.total_HFO, t1.total_HSFO_emission,
t1.total_LSFO, t1.total_LSFO_emission,
t1.total_VLSFO, t1.total_VLSFO_emission,
t1.total_MGO, t1.total_MGO_emission,
t1.total_MDO, t1.total_MDO_emission,
t1.total_co2, t1.total_co2_emission,
t1.total_no2, t1.total_no2_emission,
t1.total_LNG, t1.total_LNG_emission,
ROW_NUMBER() over (partition by t1.car_carPlate order by t1.Report_DateTime_UTC desc) as RN
from (
select t.id, t.car_carPlate, t.car_carName, t.Report_DateTime_UTC,t.Report_Type_Desc, t.Master_Report_ID,
sum(COALESCE(t.FuelType_HFO, 0)) as total_HFO, avg(COALESCE(t.HSFO_emission_ton, 0)) as total_HSFO_emission,
sum(COALESCE(t.FuelType_LSFO, 0)) as total_LSFO, avg(COALESCE(t.LSFO_emission_ton, 0)) as total_LSFO_emission,
sum(COALESCE(t.FuelType_VLSFO, 0)) as total_VLSFO, avg(COALESCE(t.VLSFO_emission_ton, 0)) as total_VLSFO_emission,
sum(COALESCE(t.FuelType_MGO, 0)) as total_MGO, avg(COALESCE(t.MGO_emission_ton, 0)) as total_MGO_emission,
sum(COALESCE(t.FuelType_MDO, 0)) as total_MDO, avg(COALESCE(t.MDO_emission_ton, 0)) as total_MDO_emission,
sum(COALESCE(t.FuelType_co2, 0)) as total_co2, avg(COALESCE(t.co2_emission_ton, 0)) as total_co2_emission,
sum(COALESCE(t.FuelType_no2, 0)) as total_no2, avg(COALESCE(t.no2_emission_ton, 0)) as total_no2_emission,
sum(COALESCE(t.FuelType_LNG, 0)) as total_LNG, avg(COALESCE(t.LNG_emission_ton, 0)) as total_LNG_emission
from (
select d.id, d.car_carPlate,
d.car_carName, d.Report_DateTime_UTC,d.Report_Type_Desc, d.Master_Report_ID,
c.FuelType_HFO, d.HSFO_emission_ton,
c.FuelType_LSFO, d.LSFO_emission_ton,
c.FuelType_VLSFO, c.VLSFO_emission_ton,
c.FuelType_MGO, d.MGO_emission_ton,
c.FuelType_MDO, d.MDO_emission_ton,
c.FuelType_co2, c.co2_emission_ton,
c.FuelType_no2, c.no2_emission_ton,
c.FuelType_LNG, c.LNG_emission_ton
from DailyReport d
left join DailyReport_Consumption c
on d.Master_Report_ID=c.Master_Report_ID
where d.Report_DateTime_UTC BETWEEN '2024-05-01' AND '2024-05-31'
--d.Report_DateTime_UTC between DATEADD(day, -30, CURRENT_TIMESTAMP) and CURRENT_TIMESTAMP
and d.Deleted <> 1
and c.master_report_id LIKE '%EFOB%'
) t
group by t.id, t.car_carPlate, t.car_carName, t.Report_DateTime_UTC,t.Report_Type_Desc, t.Master_Report_ID
) t1
) t2
where t2.RN<3
) t3
where t3.RN=1
)
SELECT
82 AS Check_Number,
'Check the inconsistency of fuel consumption' AS Check_Description,
car_carName AS carName,
car_carPlate AS carPlate,
NULL AS Calendar_Date,
Report_DateTime_UTC AS DateTime_UTC,
Report_Type_Desc AS Report_Type_Desc,
id,
Master_Report_ID AS Master_Report_ID,
NULL AS GrossTonnage,
NULL AS Cargo_Onboard_Ton,
NULL AS Volume_Onboard_m3,
NULL AS Engine_Type,
CASE
WHEN Fuel_Type = 'fuel_HSFO_emission_check' THEN 'HSFO'
WHEN Fuel_Type = 'fuel_LNG_emission_check' THEN 'LNG'
WHEN Fuel_Type = 'fuel_co2_emission_check' THEN 'co2'
WHEN Fuel_Type = 'fuel_no2_emission_check' THEN 'no2'
WHEN Fuel_Type = 'fuel_LSFO_emission_check' THEN 'LSFO'
WHEN Fuel_Type = 'fuel_MDO_emission_check' THEN 'MDO'
WHEN Fuel_Type = 'fuel_MGO_emission_check' THEN 'MGO'
WHEN Fuel_Type = 'fuel_VLSFO_emission_check' THEN 'VLSFO'
END AS Fuel_Type,
Total_Consumption AS Total_Consumption,
Emission AS Emission,
NULL AS Previous_Total_Consumption,
NULL AS Sulphur_Content,
NULL AS NOX_Emission,
NULL AS SOX_Emission,
NULL AS CO2_Emission,
Previous_emission AS Previous_emission,
NULL AS Previous_DateTime_UTC,
Miscellaneous AS Miscellaneous
FROM
FuelCheckResults
UNPIVOT
(Miscellaneous FOR Fuel_Type IN (
fuel_HSFO_emission_check,
fuel_LSFO_emission_check,
fuel_VLSFO_emission_check,
fuel_MGO_emission_check,
fuel_MDO_emission_check,
fuel_co2_emission_check,
fuel_no2_emission_check,
fuel_LNG_emission_check
)) AS UnpivotedResults
UNPIVOT
(Emission FOR Fuel_Type IN (
total_HSFO_emission,
total_LSFO_emission,
total_VLSFO_emission,
total_MGO_emission,
total_MDO_emission,
total_co2_emission,
total_no2_emission,
total_LNG_emission
)) AS UnpivotedEmissionResults
UNPIVOT
(Previous_emission FOR Fuel_Type IN (
previous_HFO_emission,
previous_LSFO_emission,
previous_VLSFO_emission,
previous_MGO_emission,
previous_MDO_emission,
previous_co2_emission,
previous_no2_emission,
previous_LNG_emission
)) AS UnpivotedPrevEmissionResults
UNPIVOT
(Total_Consumption FOR Fuel_Type IN (
total_HFO,
total_LSFO,
total_VLSFO,
total_MGO,
total_MDO,
total_co2,
total_no2,
total_LNG
)) AS UnpivotedConsumptionResults
However, I am met with the following error:
The column ‘Fuel_Type’ was specified multiple times for ‘UnpivotedResults’.
Msg 265, Level 16, State 1, Line 190
The column name “Fuel_Type” specified in the UNPIVOT operator conflicts with the existing column name in the UNPIVOT argument.
*Same error message for all the UNPIVOT lines
Visually, this is the issue I’m facing:
Current Output:
| ID | Total_HFO | Total_HSFO_Emission | Previous_HFO_Emission | Fuel_HSFO_Emission_Check | Total_LSFO | Total_LSFO_Emission | Previous_LSFO_Emission | Fuel_LSFO_Emission_Check |
|----|-----------|----------------------|------------------------|---------------------------|------------|----------------------|-------------------------|---------------------------|
| A | 0 | 1 | 2 | Invalid | 1 | 1 | 2 | Valid |
| B | 1 | 1 | 2 | Valid | 2 | 2 | 4 | Valid |
Desired Output:
| ID | Fuel_Type | Total_Consumption | Emission | Previous_Emission | Miscellaneous |
|----|-----------|-------------------|----------|-------------------|---------------|
| A | HSFO | 0 | 1 | 2 | Invalid |
| A | LSFO | 1 | 1 | 2 | Valid |
| B | HSFO | 1 | 1 | 2 | Valid |
| B | LSFO | 2 | 2 | 4 | Valid |
3