I have a procedure which is taking too much time to return the table data.
can anyone help me to improve my procedure performance.
Thanks in advance
What I have tried:
ALTER Procedure [dbo].[Prc_GetEmissionsData_MEP_test]
@Vessel varchar(1000),
@DateFrom Datetime,
@Dateto Datetime
as
begin
Declare @Vessel_local varchar(1000),@dateFrom_local datetime,@DatetTo_local datetime
set @Vessel_local = @Vessel
set @dateFrom_local = @DateFrom
set @DatetTo_local = @Dateto
declare @CurrentRowVsl int , @RowsToProcessVsl int
Declare @ID int ,@ReportDate Datetime,@VesselType varchar(100),@VesselID int,@VesselSize decimal(19,2),@SizeName varchar(100),@VoyageID int,@VoyageType varchar(5),@VesselName varchar(100),@ReportType varchar(50),@Days decimal(19,2),@TotalDistanceCovered decimal(19,2),@CargoOnBoard decimal(19,2),@Tot_IFO decimal(19,2),@Tot_IFOLS decimal(19,2),@Tot_MDO decimal(19,2),@Tot_MDOLS decimal(19,2),@avg_sulphur_ifo decimal(19,2),@avg_sulphur_mdo decimal(19,2),@avg_sulphur_ifols decimal(19,2),@avg_sulphur_mdols decimal(19,2),@CO2 decimal(19,2),@EEOI decimal(19,2),@SOx decimal(19,2),@SOxEff decimal(19,2),@NOx decimal(19,2),@NOxEff decimal(19,2),@Reason varchar(500)
Declare @Draft_fwd decimal(19,2),@Draft_Aft decimal(19,2),@ROB_Dep_Ifo decimal(19,2),@ROB_Dep_Ifols decimal(19,2),@ROB_Dep_Mdo decimal(19,2),@ROB_Dep_Mdols decimal(19,2),@ROB_Arr_Ifo decimal(19,2),@ROB_Arr_Ifols decimal(19,2),@ROB_Arr_Mdo decimal(19,2),@ROB_Arr_Mdols decimal(19,2)
Create table #temp
(
ID int ,
ReportDate Datetime,
VesselType varchar(100),
VesselID int,
VesselSize varchar(100),
VoyageID int,
VoyageType varchar(5),
VesselName varchar(100),
ReportType varchar(50),
[Days] decimal(19,2),
TotalDistanceCovered decimal(19,2),
CargoOnBoard decimal(19,2),
Tot_IFO decimal(19,2),
Tot_IFOLS decimal(19,2),
Tot_MDO decimal(19,2),
Tot_MDOLS decimal(19,2),
avg_sulphur_ifo decimal(19,2),
avg_sulphur_mdo decimal(19,2),
avg_sulphur_ifols decimal(19,2),
avg_sulphur_mdols decimal(19,2),
CO2 decimal(19,2),
EEOI decimal(19,2),
SOx decimal(19,2),
SOxEff decimal(19,2),
NOx decimal(19,2),
NOxEff decimal(19,2),
Reason varchar(500),
MEP_vesselid int,
MEP_voyage_id int,
MEP_Date datetime,
MEP_Average decimal(19,2),
MEP_MEKW decimal(19,2),
MEP_MECC decimal(19,2),
MEP_MECYL decimal(19,2),
MEP_AECC decimal(19,2),
MEP_Hours1 decimal(19,2),
MEP_Hours2 decimal(19,2),
MEP_Hours3 decimal(19,2),
MEP_KW1 decimal(19,2),
MEP_KW2 decimal(19,2),
MEP_KW3 decimal(19,2),
MEP_Distance decimal(19,2),
MEP_MaxTemp decimal(19,2),
MEP_MaxNBR decimal(19,2),
MEP_MinTemp decimal(19,2),
MEP_MinNBR decimal(19,2),
MEP_Sea decimal(19,2),
MEP_ER decimal(19,2),
MEP_ScavTemp decimal(19,2),
MEP_ScavPress decimal(19,2),
MEP_AverageTC decimal(19,2),
MEP_BHP decimal(19,2),
MEP_Prod decimal(19,2),
MEP_Cons decimal(19,2),
MEP_ROB decimal(19,2),
MEP_AE decimal(19,2),
MEP_ME decimal(19,2),
MEP_Reason varchar(100),
MEP_SteamingHrs decimal(19,2),
MEP_AE_Other_Reason varchar(500),
Draft_fwd decimal(19,2),
Draft_Aft decimal(19,2),
ROB_Dep_Ifo decimal(19,2),
ROB_Dep_Ifols decimal(19,2),
ROB_Dep_Mdo decimal(19,2),
ROB_Dep_Mdols decimal(19,2),
ROB_Arr_Ifo decimal(19,2),
ROB_Arr_Ifols decimal(19,2),
ROB_Arr_Mdo decimal(19,2),
ROB_Arr_Mdols decimal(19,2)
)
set @ID=1
SELECT * into #tempVessels FROM dbo.Split ( @Vessel_local )
while (@dateFrom_local <= @DatetTo_local)
begin
Select identity(int, 1,1) as RowID, v.vesselid,v.vesselname,vt.vessel_type ,v.summerDWT_MT
into #tempvsl from vessel v inner join tbl_vesselType vt On v.vesselID = vt.vesselid where cast(v.vesselid as nvarchar) in (select * from #tempVessels)
SET @RowsToProcessVsl=@@ROWCOUNT
SET @CurrentRowVsl=0
while(@CurrentRowVsl<@RowsToProcessVsl)
begin
SET @CurrentRowVsl=@CurrentRowVsl+1
select @VesselID = vesselid,@VesselSize =cast(COALESCE(NULLIF(LTRIM(RTRIM(case summerDWT_MT when '-' then '0' else summerDWT_MT end)),''),'0') as decimal(19,2)),@VesselType=vessel_type,@VesselName=VesselName from #tempvsl where RowID = @CurrentRowVsl
if @VesselSize > 0 and @VesselSize <= 5000
set @SizeName = '0-5k'
else if @VesselSize > 5000 and @VesselSize <= 15000
set @SizeName = '5k-15k'
else if @VesselSize > 15000 and @VesselSize <= 35000
set @SizeName = '15k-35k'
else if @VesselSize > 35000 and @VesselSize <= 50000
set @SizeName = '35k-50k'
else if @VesselSize > 50000 and @VesselSize <= 100000
set @SizeName = '50k-100k'
else if @VesselSize > 100000 and @VesselSize <= 200000
set @SizeName = '100k-200k'
else if @VesselSize > 200000 and @VesselSize <= 300000
set @SizeName = '200k-400k'
else
set @SizeName = 'No Info'
--Noon Report Starts here
select identity(int, 1,1) as NoonRowID,vesselid,voyage_id,cast(COALESCE(NULLIF(LTRIM(RTRIM(steaming_time)),''),'0') as decimal(19,2)) as steaming_time,cast(COALESCE(NULLIF(LTRIM(RTRIM(distence_covered)),''),'0') as decimal(19,2)) as distence_covered,cast(COALESCE(NULLIF(LTRIM(RTRIM(consumed_aeifo)),''),'0') as decimal(19,2)) as consumed_aeifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(consumed_aemdo)),''),'0') as decimal(19,2)) as consumed_aemdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(consumed_aeifols)),''),'0') as decimal(19,2)) as consumed_aeifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(consumed_aemdols)),''),'0') as decimal(19,2)) consumed_aemdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(tc_ifo)),''),'0') as decimal(19,2)) as tc_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(tc_mdo)),''),'0') as decimal(19,2)) as tc_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(tc_ifols)),''),'0') as decimal(19,2)) as tc_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(tc_mdols)),''),'0') as decimal(19,2)) as tc_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_ifo)),''),'0') as decimal(19,2)) as inherting_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_mdo)),''),'0') as decimal(19,2)) as inherting_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_ifols)),''),'0') as decimal(19,2)) as inherting_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_mdols)),''),'0') as decimal(19,2)) as inherting_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_ifo)),''),'0') as decimal(19,2)) as drifting_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_mdo)),''),'0') as decimal(19,2)) as drifting_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_ifols)),''),'0') as decimal(19,2)) as drifting_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_mdols)),''),'0') as decimal(19,2)) as drifting_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_ifo)),''),'0') as decimal(19,2)) as other_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_mdo)),''),'0') as decimal(19,2)) as other_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_ifols)),''),'0') as decimal(19,2)) as other_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_mdols)),''),'0') as decimal(19,2)) as other_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(ifo_ch)),''),'0') as decimal(19,2)) as ifo_ch,cast(COALESCE(NULLIF(LTRIM(RTRIM(mdo_ch)),''),'0') as decimal(19,2)) as mdo_ch,cast(COALESCE(NULLIF(LTRIM(RTRIM(ifols_ch)),''),'0') as decimal(19,2)) as ifols_ch,cast(COALESCE(NULLIF(LTRIM(RTRIM(mdols_ch)),''),'0') as decimal(19,2)) as mdols_ch,cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_AE_ifo)),''),'0') as decimal(19,2)) as txt_AE_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_AE_mdo)),''),'0') as decimal(19,2)) as txt_AE_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_AE_ifols)),''),'0') as decimal(19,2)) as txt_AE_ifols ,cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_AE_mdols)),''),'0') as decimal(19,2)) as txt_AE_mdols,local_date
into #tempNoon from tbl_noonreport where vesselid=@VesselID and local_datetime = @dateFrom_local
Declare @CurrentRowNoon int , @RowsToProcessNoon int
set @RowsToProcessNoon= @@ROWCOUNT
SET @CurrentRowNoon=0
while(@CurrentRowNoon<@RowsToProcessNoon)
begin
SET @CurrentRowNoon=@CurrentRowNoon+1
select @ReportDate =@dateFrom_local ,@VoyageID =voyage_id ,@ReportType ='Noon Report',@Days =cast(COALESCE(NULLIF(LTRIM(RTRIM(steaming_time)),''),'0') as decimal(19,2))/24,@TotalDistanceCovered =cast(COALESCE(NULLIF(LTRIM(RTRIM(distence_covered)),''),'0') as decimal(19,2)),@Tot_IFO =(cast(COALESCE(NULLIF(LTRIM(RTRIM(consumed_aeifo)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(tc_ifo)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_ifo)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_ifo)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(other_ifo)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(ifo_ch)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_AE_ifo)),''),'0') as decimal(19,2))),@Tot_IFOLS =(cast(COALESCE(NULLIF(LTRIM(RTRIM(consumed_aeifols)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(tc_ifols)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_ifols)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_ifols)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(other_ifols)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(ifols_ch)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_AE_ifols)),''),'0') as decimal(19,2))),@Tot_MDO =(cast(COALESCE(NULLIF(LTRIM(RTRIM(consumed_aemdo)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(tc_mdo)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_mdo)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_mdo)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(other_mdo)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(mdo_ch)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_AE_mdo)),''),'0') as decimal(19,2))) ,@Tot_MDOLS=(cast(COALESCE(NULLIF(LTRIM(RTRIM(consumed_aemdols)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(tc_mdols)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_mdols)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_mdols)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(other_mdols)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(mdols_ch)),''),'0') as decimal(19,2))+cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_AE_mdols)),''),'0') as decimal(19,2))) from #tempNoon where NoonRowID=@CurrentRowNoon
select @VoyageType = voyage_type,@CargoOnBoard= cast(COALESCE(NULLIF(LTRIM(RTRIM(total_cargo_onboard)),''),'0') as decimal(19,2)) from tbl_fullaway where vesselid=@VesselID and voyage_id=@VoyageID
select @avg_sulphur_ifo= case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifo)),''),'0') as decimal(19,2)) when '0' then '3.3' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifo)),''),'3.3') as decimal(19,2)) end,@avg_sulphur_mdo=case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdo)),''),'0') as decimal(19,2)) when '0' then '1.5' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdo)),''),'1.5') as decimal(19,2)) end,@avg_sulphur_ifols =case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifols)),''),'0') as decimal(19,2)) when '0' then '0.4' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifols)),''),'0.4') as decimal(19,2)) end,@avg_sulphur_mdols =case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdols)),''),'0') as decimal(19,2)) when '0' then '0.1' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdols)),''),'0.1') as decimal(19,2)) end from tbl_eosparrival where vesselid=@VesselID and voyage_id=@VoyageID
set @CO2 = ((@Tot_IFO + @Tot_IFOLS) * 3.114) + ((@Tot_MDO + @Tot_MDOLS) * 3.186)
set @NOx = ((@Tot_IFO + @Tot_IFOLS + @Tot_MDO + @Tot_MDOLS) * 84) * 0.001
set @SOx = (((@Tot_IFO * @avg_sulphur_ifo) + (@Tot_IFOLS * @avg_sulphur_ifols) + (@Tot_MDO * @avg_sulphur_mdo) + (@Tot_MDOLS * @avg_sulphur_mdols)) * 20) * 0.001
if @CargoOnBoard = 0 or @TotalDistanceCovered = 0 or @CO2 = 0 or @NOx = 0 or @SOx = 0
begin
set @SOxEff = 0
set @EEOI = 0
set @NOxEff = 0
end
else
begin
set @SOxEff = (@SOx * 1000000) / (@CargoOnBoard * @TotalDistanceCovered)
set @EEOI = @CO2 / (@CargoOnBoard * @TotalDistanceCovered) * 1000000
set @NOxEff = (@NOx * 1000000) / (@CargoOnBoard * @TotalDistanceCovered)
end
set @Reason ='Correct'
if @VoyageType = 'L'
begin
if @CargoOnBoard = 0
set @Reason ='CargoOnboard = 0'
else if @EEOI > 20
set @Reason ='EEOI > 20'
else if @CO2 <= 0
set @Reason ='CO2 <= 0'
else if @SOxEff > 2
set @Reason ='SOxEff > 2'
else if @SOx <= 0
set @Reason ='SOx <= 0'
else if @NOxEff > 10
set @Reason ='NOxEff > 10'
else if @NOx <= 0
set @Reason ='NOx <= 0'
end
--- MEP Report Starts here
Declare @MEP_vesselid int,@MEP_voyage_id int,@MEP_Date datetime,@MEP_Average decimal(19,2),@MEP_MEKW decimal(19,2),@MEP_MECC decimal(19,2),@MEP_MECYL decimal(19,2),@MEP_AECC decimal(19,2),@MEP_Hours1 decimal(19,2),@MEP_Hours2 decimal(19,2),@MEP_Hours3 decimal(19,2),@MEP_KW1 decimal(19,2),@MEP_KW2 decimal(19,2),@MEP_KW3 decimal(19,2), @MEP_Distance decimal(19,2),@MEP_MaxTemp decimal(19,2),@MEP_MaxNBR decimal(19,2),@MEP_MinTemp decimal(19,2),@MEP_MinNBR decimal(19,2),@MEP_Sea decimal(19,2),@MEP_ER decimal(19,2),@MEP_ScavTemp decimal(19,2),@MEP_ScavPress decimal(19,2),@MEP_AverageTC decimal(19,2),@MEP_BHP decimal(19,2),@MEP_Prod decimal(19,2),@MEP_Cons decimal(19,2),@MEP_ROB decimal(19,2),@MEP_AE decimal(19,2),@MEP_ME decimal(19,2),@MEP_SteamingHrs decimal(19,2),@MEP_Reason varchar(100),@MEP_AE_Other_Reason as varchar(500)
Declare @MEP_vesselid_EOSP int,@MEP_voyage_id_EOSP int,@MEP_Date_EOSP datetime,@MEP_Average_EOSP decimal(19,2),@MEP_MEKW_EOSP decimal(19,2),@MEP_MECC_EOSP decimal(19,2),@MEP_MECYL_EOSP decimal(19,2),@MEP_AECC_EOSP decimal(19,2),@MEP_Hours1_EOSP decimal(19,2),@MEP_Hours2_EOSP decimal(19,2),@MEP_Hours3_EOSP decimal(19,2),@MEP_KW1_EOSP decimal(19,2),@MEP_KW2_EOSP decimal(19,2),@MEP_KW3_EOSP decimal(19,2),@MEP_Distance_EOSP decimal(19,2),@MEP_MaxTemp_EOSP decimal(19,2),@MEP_MaxNBR_EOSP decimal(19,2),@MEP_MinTemp_EOSP decimal(19,2),@MEP_MinNBR_EOSP decimal(19,2),@MEP_Sea_EOSP decimal(19,2),@MEP_ER_EOSP decimal(19,2),@MEP_ScavTemp_EOSP decimal(19,2),@MEP_ScavPress_EOSP decimal(19,2),@MEP_AverageTC_EOSP decimal(19,2),@MEP_BHP_EOSP decimal(19,2),@MEP_Prod_EOSP decimal(19,2),@MEP_Cons_EOSP decimal(19,2),@MEP_ROB_EOSP decimal(19,2),@MEP_AE_EOSP decimal(19,2),@MEP_ME_EOSP decimal(19,2),@MEP_SteamingHrs_EOSP decimal(19,2),@MEP_Reason_EOSP varchar(100),@MEP_AE_Other_Reason_EOSP as varchar(500)
Declare @RowsToProcessMEP int,@CurrentRowMEP int
select identity(int, 1,1) as MEPRowID,vesselid,voyage_id,convert(datetime, [date]) as MEP_Date,cast(COALESCE(NULLIF(LTRIM(RTRIM(Average)),''),'0') as decimal(19,2)) as Average,cast(COALESCE(NULLIF(LTRIM(RTRIM(Distance)),''),'0') as decimal(19,2)) as Distance,cast(COALESCE(NULLIF(LTRIM(RTRIM(MaxTemp)),''),'0') as decimal(19,2)) as MaxTemp,cast(COALESCE(NULLIF(LTRIM(RTRIM(MaxNBR)),''),'0') as decimal(19,2)) as MaxNBR,cast(COALESCE(NULLIF(LTRIM(RTRIM(MinTemp)),''),'0') as decimal(19,2)) as MinTemp,cast(COALESCE(NULLIF(LTRIM(RTRIM(MinNBR)),''),'0') as decimal(19,2)) as MinNBR,cast(COALESCE(NULLIF(LTRIM(RTRIM(Sea)),''),'0') as decimal(19,2)) as Sea,cast(COALESCE(NULLIF(LTRIM(RTRIM(Er)),''),'0') as decimal(19,2)) as Er,cast(COALESCE(NULLIF(LTRIM(RTRIM(ScavTemp)),''),'0') as decimal(19,2)) as ScavTemp,cast(COALESCE(NULLIF(LTRIM(RTRIM(ScavPress)),''),'0') as decimal(19,2)) as ScavPress,cast(COALESCE(NULLIF(LTRIM(RTRIM(AverageTC)),''),'0') as decimal(19,2)) as AverageTC,cast(COALESCE(NULLIF(LTRIM(RTRIM(BHP)),''),'0') as decimal(19,2)) as BHP,cast(COALESCE(NULLIF(LTRIM(RTRIM(MEKW)),''),'0') as decimal(19,2)) as MEKW,cast(COALESCE(NULLIF(LTRIM(RTRIM(MECC)),''),'0') as decimal(19,2)) as MECC,cast(COALESCE(NULLIF(LTRIM(RTRIM(MECYL)),''),'0') as decimal(19,2)) as MECYL,cast(COALESCE(NULLIF(LTRIM(RTRIM(AECC)),''),'0') as decimal(19,2)) as AECC,cast(COALESCE(NULLIF(LTRIM(RTRIM(Hours1)),''),'0') as decimal(19,2)) as Hours1,cast(COALESCE(NULLIF(LTRIM(RTRIM(Hours2)),''),'0') as decimal(19,2)) as Hours2,cast(COALESCE(NULLIF(LTRIM(RTRIM(Hours3)),''),'0') as decimal(19,2)) as Hours3,cast(COALESCE(NULLIF(LTRIM(RTRIM(KW1)),''),'0') as decimal(19,2)) as KW1,cast(COALESCE(NULLIF(LTRIM(RTRIM(KW2)),''),'0') as decimal(19,2)) as KW2,cast(COALESCE(NULLIF(LTRIM(RTRIM(KW3)),''),'0') as decimal(19,2)) as KW3,Reason,cast(COALESCE(NULLIF(LTRIM(RTRIM(Prod)),''),'0') as decimal(19,2)) as Prod,cast(COALESCE(NULLIF(LTRIM(RTRIM(Cons)),''),'0') as decimal(19,2)) as Cons,cast(COALESCE(NULLIF(LTRIM(RTRIM(Rob)),''),'0') as decimal(19,2)) as Rob,cast(COALESCE(NULLIF(LTRIM(RTRIM(ae)),''),'0') as decimal(19,2)) as ae,cast(COALESCE(NULLIF(LTRIM(RTRIM(me)),''),'0') as decimal(19,2)) as me,cast(COALESCE(NULLIF(LTRIM(RTRIM(Steaming_Hrs)),''),'0') as decimal(19,2)) as Steaming_Hrs,AE_Other_Reason
into #TempMEP from tbl_mep where vesselid=@VesselID and voyage_id=@VoyageID and Datetime_new = @dateFrom_local order by id desc
set @RowsToProcessMEP = @@ROWCOUNT
SET @CurrentRowMEP=0
SET @CurrentRowMEP=@CurrentRowMEP+1
select @MEP_vesselid = vesselid,@MEP_voyage_id = voyage_id,@MEP_Date = convert(datetime,MEP_Date),@MEP_Average = Average,@MEP_MEKW = MEKW,@MEP_MECC = MECC,@MEP_MECYL = MECYL,@MEP_AECC = AECC,@MEP_Hours1 = Hours1,@MEP_Hours2 = Hours2,@MEP_Hours3 = Hours3,@MEP_KW1 = KW1,@MEP_KW2 = KW2,@MEP_KW3 = KW3, @MEP_Distance = Distance,@MEP_MaxTemp = MaxTemp ,@MEP_MaxNBR = MaxNBR,@MEP_MinTemp = MinTemp,@MEP_MinNBR = MinNBR,@MEP_Sea = Sea,@MEP_ER=ER,@MEP_ScavTemp=ScavTemp,@MEP_ScavPress=ScavPress,@MEP_AverageTC=AverageTC,@MEP_BHP=BHP,@MEP_Prod=Prod,@MEP_Cons=Cons,@MEP_ROB=ROB,@MEP_AE=AE,@MEP_ME=ME,@MEP_SteamingHrs=Steaming_Hrs,@MEP_Reason = Reason, @MEP_AE_Other_Reason = AE_Other_Reason from #TempMEP where MEPRowID=@CurrentRowMEP
if @RowsToProcessMEP = 2
begin
SET @CurrentRowMEP=@CurrentRowMEP+1
select @MEP_vesselid_EOSP = vesselid,@MEP_voyage_id_EOSP = voyage_id,@MEP_Date_EOSP = convert(datetime,MEP_Date),@MEP_Average_EOSP = Average,@MEP_MEKW_EOSP = MEKW,@MEP_MECC_EOSP = MECC,@MEP_MECYL_EOSP = MECYL,@MEP_AECC_EOSP = AECC,@MEP_Hours1_EOSP = Hours1,@MEP_Hours2_EOSP = Hours2,@MEP_Hours3_EOSP = Hours3,@MEP_KW1_EOSP = KW1,@MEP_KW2_EOSP = KW2,@MEP_KW3_EOSP = KW3, @MEP_Distance_EOSP=Distance,@MEP_MaxTemp_EOSP=MaxTemp ,@MEP_MaxNBR_EOSP=MaxNBR,@MEP_MinTemp_EOSP=MinTemp,@MEP_MinNBR_EOSP=MinNBR,@MEP_Sea_EOSP=Sea,@MEP_ER_EOSP=ER,@MEP_ScavTemp_EOSP=ScavTemp,@MEP_ScavPress_EOSP=ScavPress,@MEP_AverageTC_EOSP=AverageTC,@MEP_BHP_EOSP=BHP,@MEP_Prod_EOSP=Prod,@MEP_Cons_EOSP=Cons,@MEP_ROB_EOSP=ROB,@MEP_AE_EOSP=AE,@MEP_ME_EOSP=ME,@MEP_SteamingHrs_EOSP=Steaming_Hrs,@MEP_Reason_EOSP = Reason,@MEP_AE_Other_Reason_EOSP = AE_Other_Reason from #TempMEP where MEPRowID=@CurrentRowMEP
END
drop table #TempMEP
--- MEP Report Ends here
if @RowsToProcessNoon != 0
begin
insert into #temp(ID,ReportDate,VesselType,VesselID,VesselSize,VoyageID,VoyageType,VesselName,ReportType,[Days],TotalDistanceCovered,CargoOnBoard,Tot_IFO,Tot_IFOLS,Tot_MDO,Tot_MDOLS,avg_sulphur_ifo,avg_sulphur_mdo,avg_sulphur_ifols,avg_sulphur_mdols,CO2,EEOI,SOx,SOxEff,NOx,NOxEff,Reason, MEP_vesselid,MEP_voyage_id,MEP_Date,MEP_Average,MEP_MEKW,MEP_MECC,MEP_MECYL,MEP_AECC,MEP_Hours1,MEP_Hours2,MEP_Hours3,MEP_KW1,MEP_KW2,MEP_KW3,MEP_Distance,MEP_MaxTemp,MEP_MaxNBR,MEP_MinTemp,MEP_MinNBR,MEP_Sea,MEP_ER,MEP_ScavTemp,MEP_ScavPress,MEP_AverageTC,MEP_BHP,MEP_Prod,MEP_Cons,MEP_ROB,MEP_AE,MEP_ME,MEP_SteamingHrs,MEP_Reason,MEP_AE_Other_Reason)
values(@ID,@ReportDate,@VesselType,@VesselID,@SizeName,@VoyageID,@VoyageType,@VesselName,@ReportType,@Days,@TotalDistanceCovered,@CargoOnBoard,@Tot_IFO,@Tot_IFOLS,@Tot_MDO,@Tot_MDOLS,@avg_sulphur_ifo,@avg_sulphur_mdo,@avg_sulphur_ifols,@avg_sulphur_mdols,@CO2,@EEOI,@SOx,@SOxEff,@NOx,@NOxEff,@Reason,@MEP_vesselid,@MEP_voyage_id,@MEP_Date,@MEP_Average,@MEP_MEKW,@MEP_MECC,@MEP_MECYL,@MEP_AECC,@MEP_Hours1,@MEP_Hours2,@MEP_Hours3,@MEP_KW1,@MEP_KW2,@MEP_KW3,@MEP_Distance,@MEP_MaxTemp,@MEP_MaxNBR,@MEP_MinTemp,@MEP_MinNBR,@MEP_Sea,@MEP_ER,@MEP_ScavTemp,@MEP_ScavPress,@MEP_AverageTC,@MEP_BHP,@MEP_Prod,@MEP_Cons,@MEP_ROB,@MEP_AE,@MEP_ME,@MEP_SteamingHrs,@MEP_Reason,@MEP_AE_Other_Reason )
Set @ID = @ID+1
end
End
drop table #tempNoon
-- Noon Report Ends here
-- EOSP Report Starts here
select identity(int, 1,1) as EOSPRowID,voyage_id,vesselid,cast(COALESCE(NULLIF(LTRIM(RTRIM(streaming_days)),''),'0') as decimal(19,2)) as streaming_days,cast(COALESCE(NULLIF(LTRIM(RTRIM(distance_streamed_last_noon)),''),'0') as decimal(19,2)) as distance_streamed_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(meae_ifo)),''),'0') as decimal(19,2)) as meae_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(meae_mdo)),''),'0') as decimal(19,2)) as meae_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(meae_ifols)),''),'0') as decimal(19,2)) as meae_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(meae_mdols)),''),'0') as decimal(19,2)) as meae_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(tank_cleaning_ifo_last_noon)),''),'0') as decimal(19,2)) as tank_cleaning_ifo_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(tank_cleaning_mdo_last_noon)),''),'0') as decimal(19,2)) as tank_cleaning_mdo_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(tank_cleaning_ifols_last_noon)),''),'0') as decimal(19,2)) as tank_cleaning_ifols_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(tank_cleaning_mdols_last_noon)),''),'0') as decimal(19,2)) as tank_cleaning_mdols_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_ifo_last_noon)),''),'0') as decimal(19,2)) as inherting_ifo_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_mdo_last_noon)),''),'0') as decimal(19,2)) as inherting_mdo_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_ifols_last_noon)),''),'0') as decimal(19,2)) as inherting_ifols_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(inherting_mdols_last_noon)),''),'0') as decimal(19,2)) as inherting_mdols_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_ifo_last_noon)),''),'0') as decimal(19,2)) as drifting_ifo_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_mdo_last_noon)),''),'0') as decimal(19,2)) as drifting_mdo_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_ifols_last_noon)),''),'0') as decimal(19,2)) as drifting_ifols_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(drifting_mdols_last_noon)),''),'0') as decimal(19,2)) as drifting_mdols_last_noon,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_ifo)),''),'0') as decimal(19,2)) as other_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_mdo)),''),'0') as decimal(19,2)) as other_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_ifols)),''),'0') as decimal(19,2)) as other_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_mdols)),''),'0') as decimal(19,2)) as other_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_NoonBCifo)),''),'0') as decimal(19,2)) as txt_NoonBCifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_noonBCmdo)),''),'0') as decimal(19,2)) as txt_noonBCmdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_noonBCifols)),''),'0') as decimal(19,2)) as txt_noonBCifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(txt_noonBCmdols)),''),'0') as decimal(19,2)) as txt_noonBCmdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(lastnoonAE_ifo)),''),'0') as decimal(19,2)) as lastnoonAE_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(lastnoonAE_mdo)),''),'0') as decimal(19,2)) as lastnoonAE_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(lastnoonAE_ifols)),''),'0') as decimal(19,2)) as lastnoonAE_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(lastnoonAE_mdols)),''),'0') as decimal(19,2)) as lastnoonAE_mdols,date_sea_passage_end,cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifo)),''),'3.3') as decimal(19,2)) as avg_sulphur_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdo)),''),'1.5') as decimal(19,2)) as avg_sulphur_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifols)),''),'0.4') as decimal(19,2)) as avg_sulphur_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdols)),''),'0.1') as decimal(19,2)) as avg_sulphur_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(ROB_IFO)),''),'0') as decimal(19,2)) as ROB_IFO,cast(COALESCE(NULLIF(LTRIM(RTRIM(ROB_IFOLS)),''),'0') as decimal(19,2)) as ROB_IFOLS,cast(COALESCE(NULLIF(LTRIM(RTRIM(ROB_MDO)),''),'0') as decimal(19,2)) as ROB_MDO,cast(COALESCE(NULLIF(LTRIM(RTRIM(ROB_MDOLS)),''),'0') as decimal(19,2)) as ROB_MDOLS
into #tempEOSP from tbl_eosparrival where vesselid = @VesselID and Datetime_SeaPassage_end = @dateFrom_local
Declare @CurrentRowEOSP int , @RowsToProcessEOSP int
set @RowsToProcessEOSP= @@ROWCOUNT
SET @CurrentRowEOSP=0
while(@CurrentRowEOSP<@RowsToProcessEOSP)
begin
SET @CurrentRowEOSP=@CurrentRowEOSP+1
select @ReportDate =@dateFrom_local ,@VoyageID =voyage_id ,@ReportType ='EOSP',@Days = streaming_days/24,@TotalDistanceCovered =distance_streamed_last_noon,@Tot_IFO =(meae_ifo+tank_cleaning_ifo_last_noon+inherting_ifo_last_noon+drifting_ifo_last_noon+other_ifo+txt_NoonBCifo+lastnoonAE_ifo),@Tot_IFOLS =(meae_ifols+tank_cleaning_ifols_last_noon+inherting_ifols_last_noon+drifting_ifols_last_noon+other_ifols+txt_NoonBCifols+lastnoonAE_ifols),@Tot_MDO =(meae_mdo+tank_cleaning_mdo_last_noon+inherting_mdo_last_noon+drifting_mdo_last_noon+other_mdo+txt_NoonBCmdo+lastnoonAE_mdo) ,@Tot_MDOLS=(meae_mdols+tank_cleaning_mdols_last_noon+inherting_mdols_last_noon+drifting_mdols_last_noon+other_mdols+txt_NoonBCmdols+lastnoonAE_mdols),@avg_sulphur_ifo= case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifo)),''),'0') as decimal(19,2)) when '0' then '3.3' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifo)),''),'3.3') as decimal(19,2)) end,@avg_sulphur_mdo=case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdo)),''),'0') as decimal(19,2)) when '0' then '1.5' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdo)),''),'1.5') as decimal(19,2)) end,@avg_sulphur_ifols =case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifols)),''),'0') as decimal(19,2)) when '0' then '0.4' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifols)),''),'0.4') as decimal(19,2)) end,@avg_sulphur_mdols =case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdols)),''),'0') as decimal(19,2)) when '0' then '0.1' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdols)),''),'0.1') as decimal(19,2)) end,@ROB_Arr_Ifo = ROB_IFO ,@ROB_Arr_Ifols = ROB_IFOLS,@ROB_Arr_Mdo = ROB_MDO,@ROB_Arr_Mdols= ROB_MDOLS from #tempEOSP where EOSPRowID=@CurrentRowEOSP
select @VoyageType = voyage_type,@CargoOnBoard= cast(COALESCE(NULLIF(LTRIM(RTRIM(total_cargo_onboard)),''),'0') as decimal(19,2)),@Draft_fwd=cast(COALESCE(NULLIF(LTRIM(RTRIM(dep_draftFWD)),''),'0') as decimal(19,2)),@Draft_Aft=cast(COALESCE(NULLIF(LTRIM(RTRIM(dep_draftAFT)),''),'0') as decimal(19,2)),@ROB_Dep_Ifo=cast(COALESCE(NULLIF(LTRIM(RTRIM(Rob_Dep_ifo)),''),'0') as decimal(19,2)),@ROB_Dep_Ifols=cast(COALESCE(NULLIF(LTRIM(RTRIM(Rob_Dep_ifols)),''),'0') as decimal(19,2)),@ROB_Dep_Mdo=cast(COALESCE(NULLIF(LTRIM(RTRIM(Rob_Dep_mdo)),''),'0') as decimal(19,2)),@ROB_Dep_Mdols=cast(COALESCE(NULLIF(LTRIM(RTRIM(Rob_Dep_mdols)),''),'0') as decimal(19,2)) from tbl_fullaway where vesselid=@VesselID and voyage_id=@VoyageID
set @CO2 = ((@Tot_IFO + @Tot_IFOLS) * 3.114) + ((@Tot_MDO + @Tot_MDOLS) * 3.186)
set @NOx = ((@Tot_IFO + @Tot_IFOLS + @Tot_MDO + @Tot_MDOLS) * 84) * 0.001
set @SOx = (((@Tot_IFO * @avg_sulphur_ifo) + (@Tot_IFOLS * @avg_sulphur_ifols) + (@Tot_MDO * @avg_sulphur_mdo) + (@Tot_MDOLS * @avg_sulphur_mdols)) * 20) * 0.001
if @CargoOnBoard = 0 or @TotalDistanceCovered = 0 or @CO2 = 0 or @NOx = 0 or @SOx = 0
begin
set @SOxEff = 0
set @EEOI = 0
set @NOxEff = 0
end
else
begin
set @SOxEff = (@SOx * 1000000) / (@CargoOnBoard * @TotalDistanceCovered)
set @EEOI = @CO2 / (@CargoOnBoard * @TotalDistanceCovered) * 1000000
set @NOxEff = (@NOx * 1000000) / (@CargoOnBoard * @TotalDistanceCovered)
end
set @Reason ='Correct'
if @VoyageType = 'L'
begin
if @CargoOnBoard = 0
set @Reason ='CargoOnboard = 0'
else if @EEOI > 20
set @Reason ='EEOI > 20'
else if @CO2 <= 0
set @Reason ='CO2 <= 0'
else if @SOxEff > 2
set @Reason ='SOxEff > 2'
else if @SOx <= 0
set @Reason ='SOx <= 0'
else if @NOxEff > 10
set @Reason ='NOxEff > 10'
else if @NOx <= 0
set @Reason ='NOx <= 0'
end
if @RowsToProcessEOSP != 0
begin
insert into #temp(ID,ReportDate,VesselType,VesselID,VesselSize,VoyageID,VoyageType,VesselName,ReportType,[Days],TotalDistanceCovered,CargoOnBoard,Tot_IFO,Tot_IFOLS,Tot_MDO,Tot_MDOLS,avg_sulphur_ifo,avg_sulphur_mdo,avg_sulphur_ifols,avg_sulphur_mdols,CO2,EEOI,SOx,SOxEff,NOx,NOxEff,Reason,MEP_vesselid,MEP_voyage_id,MEP_Date,MEP_Average,MEP_MEKW,MEP_MECC,MEP_MECYL,MEP_AECC,MEP_Hours1,MEP_Hours2,MEP_Hours3,MEP_KW1,MEP_KW2,MEP_KW3,MEP_Distance,MEP_MaxTemp,MEP_MaxNBR,MEP_MinTemp,MEP_MinNBR,MEP_Sea,MEP_ER,MEP_ScavTemp,MEP_ScavPress,MEP_AverageTC,MEP_BHP,MEP_Prod,MEP_Cons,MEP_ROB,MEP_AE,MEP_ME,MEP_SteamingHrs,MEP_Reason,MEP_AE_Other_Reason,ROB_Arr_IFO,ROB_Arr_IFOLS,ROB_Arr_MDO,ROB_Arr_MDOLS,Draft_fwd,Draft_AFT,Rob_Dep_ifo,Rob_Dep_ifols,Rob_Dep_mdo,Rob_Dep_mdols)
values(@ID,@ReportDate,@VesselType,@VesselID,@SizeName,@VoyageID,@VoyageType,@VesselName,@ReportType,@Days,@TotalDistanceCovered,@CargoOnBoard,@Tot_IFO,@Tot_IFOLS,@Tot_MDO,@Tot_MDOLS,@avg_sulphur_ifo,@avg_sulphur_mdo,@avg_sulphur_ifols,@avg_sulphur_mdols,@CO2,@EEOI,@SOx,@SOxEff,@NOx,@NOxEff,@Reason,@MEP_vesselid_EOSP,@MEP_voyage_id_EOSP,@MEP_Date_EOSP,@MEP_Average_EOSP,@MEP_MEKW_EOSP,@MEP_MECC_EOSP,@MEP_MECYL_EOSP,@MEP_AECC_EOSP,@MEP_Hours1_EOSP,@MEP_Hours2_EOSP,@MEP_Hours3_EOSP,@MEP_KW1_EOSP,@MEP_KW2_EOSP,@MEP_KW3_EOSP,@MEP_Distance_EOSP,@MEP_MaxTemp_EOSP,@MEP_MaxNBR_EOSP,@MEP_MinTemp_EOSP,@MEP_MinNBR_EOSP,@MEP_Sea_EOSP,@MEP_ER_EOSP,@MEP_ScavTemp_EOSP,@MEP_ScavPress_EOSP,@MEP_AverageTC_EOSP,@MEP_BHP_EOSP,@MEP_Prod_EOSP,@MEP_Cons_EOSP,@MEP_ROB_EOSP,@MEP_AE_EOSP,@MEP_ME_EOSP,@MEP_SteamingHrs_EOSP,@MEP_Reason_EOSP,@MEP_AE_Other_Reason_EOSP,@ROB_Arr_Ifo,@ROB_Arr_Ifols,@ROB_Arr_Mdo,@ROB_Arr_Mdols,@Draft_fwd,@Draft_Aft,@ROB_Dep_Ifo,@ROB_Dep_Ifols,@ROB_Dep_Mdo,@ROB_Dep_Mdols)
Set @ID = @ID+1
end
End
drop table #tempEOSP
-- EOSP Report Ends here
-- IDLE Report Starts here
select identity(int, 1,1) as IDLERowID,record_id,vesselid,voyage_id,cast(COALESCE(NULLIF(LTRIM(RTRIM(ME_ifo)),''),'0') as decimal(19,2)) as ME_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(ME_mdo)),''),'0') as decimal(19,2)) as ME_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(ME_ifols)),''),'0') as decimal(19,2)) as ME_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(ME_mdols)),''),'0') as decimal(19,2)) as ME_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(AE_ifo)),''),'0') as decimal(19,2)) as AE_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(AE_mdo)),''),'0') as decimal(19,2)) as AE_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(AE_ifols)),''),'0') as decimal(19,2)) as AE_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(AE_mdols)),''),'0') as decimal(19,2)) as AE_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(TC_ifo)),''),'0') as decimal(19,2)) as TC_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(TC_mdo)),''),'0') as decimal(19,2)) as TC_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(TC_ifols)),''),'0') as decimal(19,2)) as TC_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(TC_mdols)),''),'0') as decimal(19,2)) as TC_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(Inherting_ifo)),''),'0') as decimal(19,2)) as Inherting_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(Inherting_mdo)),''),'0') as decimal(19,2)) as Inherting_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(Inherting_ifols)),''),'0') as decimal(19,2)) as Inherting_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(Inherting_mdols)),''),'0') as decimal(19,2)) as Inherting_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(Heating_ifo)),''),'0') as decimal(19,2)) as Heating_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(Heating_mdo)),''),'0') as decimal(19,2)) as Heating_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(Heating_ifols)),''),'0') as decimal(19,2)) as Heating_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(Heating_mdols)),''),'0') as decimal(19,2)) as Heating_mdols,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_ifo)),''),'0') as decimal(19,2)) as other_ifo,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_mdo)),''),'0') as decimal(19,2)) as other_mdo,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_ifols)),''),'0') as decimal(19,2)) as other_ifols,cast(COALESCE(NULLIF(LTRIM(RTRIM(other_mdols)),''),'0') as decimal(19,2)) as other_mdols, port_date,port_time
into #tempIDLE from tbl_idlereport where vesselid = @VesselID and Port_Datetime = @dateFrom_local
Declare @CurrentRowIDLE int , @RowsToProcessIDLE int
set @RowsToProcessIDLE= @@ROWCOUNT
SET @CurrentRowIDLE=0
while(@CurrentRowIDLE<@RowsToProcessIDLE)
begin
SET @CurrentRowIDLE=@CurrentRowIDLE+1
Declare @port_date as datetime,@Pre_port_date as datetime ,@EOSP_Date as datetime
select @ReportDate =@dateFrom_local ,@VoyageID =voyage_id ,@ReportType ='IDLE',@TotalDistanceCovered =0,@Tot_IFO =(ME_ifo+AE_ifo+TC_ifo+Inherting_ifo+Heating_ifo+other_ifo),@Tot_IFOLS =(ME_ifols+AE_ifols+TC_ifols+Inherting_ifols+Heating_ifols+other_ifols),@Tot_MDO =(ME_mdo+AE_mdo+TC_mdo+Inherting_mdo+Heating_mdo+other_mdo) ,@Tot_MDOLS=(ME_mdols+AE_mdols+TC_mdols+Inherting_mdols+Heating_mdols+other_mdols),@port_date=convert(datetime, replace(port_date,'-','/')+' '+ port_time) from #tempIDLE where IDLERowID=@CurrentRowIDLE
declare @ppp nvarchar(30)
set @Pre_port_date=''
set @EOSP_Date=''
select @avg_sulphur_ifo= case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifo)),''),'0') as decimal(19,2)) when '0' then '3.3' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifo)),''),'3.3') as decimal(19,2)) end,@avg_sulphur_mdo=case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdo)),''),'0') as decimal(19,2)) when '0' then '1.5' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdo)),''),'1.5') as decimal(19,2)) end,@avg_sulphur_ifols =case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifols)),''),'0') as decimal(19,2)) when '0' then '0.4' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_ifols)),''),'0.4') as decimal(19,2)) end,@avg_sulphur_mdols =case cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdols)),''),'0') as decimal(19,2)) when '0' then '0.1' else cast(COALESCE(NULLIF(LTRIM(RTRIM(avg_sulphur_mdols)),''),'0.1') as decimal(19,2)) end ,@EOSP_Date =convert(datetime,replace(date_sea_passage_end,'-','/')+ ' ' + time_sea_passage_end) from tbl_eosparrival where vesselid=@VesselID and voyage_id=@VoyageID
select top 1 @Pre_port_date = convert(datetime, replace(port_date,'-','/')+' '+ port_time) from tbl_idlereport where voyage_id =@VoyageID and vesselid=@VesselID and convert(datetime, replace(port_date,'-','/')+' '+ port_time) < convert(datetime,@port_date) order by convert(datetime, replace(port_date,'-','/')+' '+ port_time) desc
select top 1 @ppp = (replace(port_date,'-','/')+' '+ port_time) from tbl_idlereport where voyage_id =@VoyageID and vesselid=@VesselID and convert(datetime, replace(port_date,'-','/')+' '+ port_time) < convert(datetime,@port_date) order by convert(datetime, replace(port_date,'-','/')+' '+ port_time) desc
if @Pre_port_date != ''
begin
set @Days = DATEDIFF(hour,@Pre_port_date,@port_date)
if @Days = 0
begin
if @EOSP_Date = ''
set @Days = -1
else
set @Days = DATEDIFF(hour,@EOSP_Date,@port_date)
end
end
else
begin
if @EOSP_Date = ''
set @Days = -1
else
set @Days = DATEDIFF(hour,@EOSP_Date,@port_date)
end
select @VoyageType = voyage_type,@CargoOnBoard= cast(COALESCE(NULLIF(LTRIM(RTRIM(total_cargo_onboard)),''),'0') as decimal(19,2)) from tbl_fullaway where vesselid=@VesselID and voyage_id=@VoyageID
set @CO2 = ((@Tot_IFO + @Tot_IFOLS) * 3.114) + ((@Tot_MDO + @Tot_MDOLS) * 3.186)
set @NOx = ((@Tot_IFO + @Tot_IFOLS + @Tot_MDO + @Tot_MDOLS) * 84) * 0.001
set @SOx = (((@Tot_IFO * @avg_sulphur_ifo) + (@Tot_IFOLS * @avg_sulphur_ifols) + (@Tot_MDO * @avg_sulphur_mdo) + (@Tot_MDOLS * @avg_sulphur_mdols)) * 20) * 0.001
if @CargoOnBoard = 0 or @TotalDistanceCovered = 0 or @CO2 = 0 or @NOx = 0 or @SOx = 0
begin
set @SOxEff = 0
set @EEOI = 0
set @NOxEff = 0
end
else
begin
set @SOxEff = (@SOx * 1000000) / (@CargoOnBoard * @TotalDistanceCovered)
set @EEOI = @CO2 / (@CargoOnBoard * @TotalDistanceCovered) * 1000000
set @NOxEff = (@NOx * 1000000) / (@CargoOnBoard * @TotalDistanceCovered)
end
set @Reason ='Correct'
if @VoyageType = 'L'
begin
if @CargoOnBoard = 0
set @Reason ='CargoOnboard = 0'
else if @EEOI > 20
set @Reason ='EEOI > 20'
else if @CO2 <= 0
set @Reason ='CO2 <= 0'
else if @SOxEff > 2
set @Reason ='SOxEff > 2'
else if @SOx <= 0
set @Reason ='SOx <= 0'
else if @NOxEff > 10
set @Reason ='NOxEff > 10'
else if @NOx <= 0
set @Reason ='NOx <= 0'
end
if @RowsToProcessIDLE != 0
begin
if @Days = -1
set @Reason ='Date Not Available'
else
set @Days = @Days/24
insert into #temp(ID,ReportDate,VesselType,VesselID,VesselSize,VoyageID,VoyageType,VesselName,ReportType,[Days],TotalDistanceCovered,CargoOnBoard,Tot_IFO,Tot_IFOLS,Tot_MDO,Tot_MDOLS,avg_sulphur_ifo,avg_sulphur_mdo,avg_sulphur_ifols,avg_sulphur_mdols,CO2,EEOI,SOx,SOxEff,NOx,NOxEff,Reason)
values(@ID,@ReportDate,@VesselType,@VesselID,@SizeName,@VoyageID,@VoyageType,@VesselName,@ReportType,@Days,@TotalDistanceCovered,@CargoOnBoard,@Tot_IFO,@Tot_IFOLS,@Tot_MDO,@Tot_MDOLS,@avg_sulphur_ifo,@avg_sulphur_mdo,@avg_sulphur_ifols,@avg_sulphur_mdols,@CO2,@EEOI,@SOx,@SOxEff,@NOx,@NOxEff,@Reason)
Set @ID = @ID+1
end
End
drop table #tempIDLE
-- IDLE Report Ends here
end
drop table #tempvsl
set @dateFrom_local = DATEADD(day,1,@dateFrom_local)
end
drop table #tempVessels
select * from #temp
end
Submit an article or tip