DECLARE @DateIn VARCHAR(21),
@DateOut VARCHAR(21);
SET @DateIn = '2024-06-01 00:00:00.000';
SET @DateOut = '2024-06-10 23:59:59.000';
SELECT
[tên tàu], [chuyến nhập], [chuyến xuất], [số cont], [hướng], [kích cỡ ISO], [ngày kiểm tra],
[0] as [0h], [4] as [4h], [8] as [8h], [12] as [12h], [16] as [16h], [20] as [20h]
FROM (
SELECT
v.ShipName as [tên tàu], vs.ImVoy as[chuyến nhập], vs.ExVoy as [chuyến xuất],
cd.CntrNo as [số cont], cd.CntrClass as [hướng], cd.ISO_SZTP as [kích cỡ ISO],
crd.CheckDate as [ngày kiểm tra], crd.Temperature, crd.Daily_Check_Hour
FROM CNTR_DETAILS cd
LEFT JOIN VESSEL_SCHEDULE vs ON cd.ShipKey = vs.ShipKey and cd.YARD_ID=vs.YARD_ID
LEFT JOIN VESSELS v ON vs.ShipID = v.ShipID and vs.YARD_ID=v.YARD_ID
LEFT JOIN RF_ONOFF r ON cd.rowguid = r.MASTER_ROWGUID and cd.YARD_ID=r.YARD_ID
RIGHT JOIN CNTR_RF_DTL crd ON crd.TB_RF_ONOFF_GUID = r.rowguid and r.YARD_ID= crd.YARD_ID
WHERE cd.CARGO_TYPE in ('RF' , 'RO' , 'DR')
AND crd.CheckDate BETWEEN @DateIn AND @DateOut
)AS S
PIVOT (
MAX (Temperature) FOR Daily_Check_Hour IN ([0], [4], [8], [12], [16], [20])
) AS PT;
In case CNTR_RF_DTL has data but RF_ONOFF does not, the data will still be retrieved.
If RF_ONOFF has no data, then get the data of CNTR_RF_DTL, and if RF_ONOFF has data, get the data of RF_ONOFF
If you don’t have it, don’t take it
New contributor
Huy Hoàng Lê is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.