tbl_Fuel #Temp1
CommodityID Commodity Fuel_Sector Fuel_Rate Fuel_Min Fuel_EffectiveDate ---------------------------------------------------------------------------------------------------- 1 General Cargo OTHERS 38.00 0.00 01/Apr/2019 1 General Cargo GULF+M.EAST 27.00 0.00 01/Apr/2019 1 General Cargo C/D 16.00 0.00 01/Apr/2019 2 Perishable - General OTHERS 7.00 0.00 15/Jun/2015 2 Perishable - General GULF+M.EAST 7.00 0.00 15/Jun/2015 2 Perishable - General C/D 7.00 0.00 15/Jun/2015
tbl_War #Temp2
CommodityID Commodity War_Sector War_Rate War_Min War_EffectiveDate -------------------------------------------------------------------------------------------- 1 General Cargo OTHERS 7.00 0.00 01/Apr/2019 1 General Cargo GULF+M.EAST 5.00 0.00 01/Apr/2019 1 General Cargo CD 5.00 0.00 01/Apr/2019 1 General Cargo BGW 5.00 0.00 01/Apr/2019 3 Cut Flowers OTHERS 2.00 0.00 15/Jun/2015 3 Cut Flowers GULF+M.EAST 2.00 0.00 15/Jun/2015
tbl_XRay #Temp3
CommodityID Commodity XRay_Sector XRay_Rate XRay_Min XRay_EffectiveDate -------------------------------------------------------------------------------------------- 1 General Cargo ALL 2.00 225.00 16/Nov/2019 9 Pharma & Chemicals ALL 2.00 225.00 01/Jun/2011
Desired output:
CommodityID Commodity | Fuel_Sector | Fuel_Rate | Fuel_Min | Fuel_EffectiveDate |War_Sector | War_Rate | War_Min | War_EffectiveDate | XRay_Sector | XRay_Rate | XRay_Min | XRay_EffectiveDate 1 General Cargo | OTHERS | 38.00 | 0.00 | 01/Apr/2019 | OTHERS | 7.00 | 0.00 | 01/Apr/2019 | ALL | 2.00 | 225.00 | 16/Nov/2019 1 General Cargo | GULF+M.EAST | 27.00 | 0.00 | 01/Apr/2019 | GULF+M.EAST| 5.00 | 0.00 | 01/Apr/2019 | | | | 1 General Cargo | C/D | 16.00 | 0.00 | 01/Apr/2019 | CD | 5.00 | 0.00 | 01/Apr/2019 | | | | 1 General Cargo | NULL | NULL | NULL | NULL | BGW | 5.00 | 0.00 | 01/Apr/2019 | | | | 2 Perishable - General | OTHERS | 7.00 | 0.00 | 15/Jun/2015 | | | | | | | | 2 Perishable - General | GULF+M.EAST | 7.00 | 0.00 | 15/Jun/2015 | | | | | | | | 2 Perishable - General | C/D | 7.00 | 0.00 | 15/Jun/2015 | | | | | | | | 3 Cut Flowers | | | | |OTHERS | 2.00 | 0.00 | 15/Jun/2015 | | | | 3 Cut Flowers | | | | |GULF+M.EAST | 2.00 | 0.00 | 15/Jun/2015 | | | | 9 Pharma & Chemicals | | | | | | | | | ALL | 2.00 | 225.00 | 01/Jun/2011
After so many Condn.. I get the data in three different temp tables… War, Fuel, XRay… I want to show the data commodity wise, if available….
I tried this query:
SELECT *
INTO #Temp4
FROM
(SELECT DISTINCT AirLine, CommodityId FROM #Temp1
UNION ALL
SELECT DISTINCT AirLine, CommodityId FROM #Temp2
UNION ALL
SELECT DISTINCT AirLine, CommodityId FROM #Temp3) a
SELECT *
FROM #Temp4
SELECT *
FROM #Temp4 a1
LEFT OUTER JOIN #Temp1 a2 ON a1.AirLine = a2.AirLine
AND a1.CommodityId = a2.CommodityId
LEFT OUTER JOIN #Temp2 a3 ON a1.AirLine = a3.AirLine
AND a1.CommodityId = a3.CommodityId
LEFT OUTER JOIN #Temp3 a4 ON a1.AirLine = a4.AirLine
AND a1.CommodityId = a4.CommodityId
This query first DISTINCT data hold in another temp table, then I’m doing a few Left Joins with all temp tables, but it’s not returning the correct output.
Advertisement
Answer
You can use full join, but to avoid a Cartesian product for each commodityID, you need a sequence number:
select coalesce(f.commodityID, w.commodityID, x.commodityID) as commodityID,
coalesce(f.commodity, w.commodity, x.commodity) as commodity,
f.fuel_sector, f.fule_rate, . . .
-- other columns from "tbl_fuel"
w.war_sector, . . .
x.xray_sector, . . .
from (select f.*,
row_number() over (partition by commodityID order by commodityID) as seqnum
from tbl_fuel f
) f full join
(select w.*,
row_number() over (partition by commodityID order by commodityID) as seqnum
from tbl_war w
) w
on w.commodityID = f.commodityID and
w.seqnum = f.seqnum full join
(select x.*,
row_number() over (partition by commodityID order by commodityID) as seqnum
from tbl_xray x
) x
on x.commodityID = coalesce(f.commodityID, w.commodityID) and
x.seqnum = coalesce(f.seqnum, w.seqnum);