过程需要花费太多时间来执行和返回表 [英] Procedure taking too much time to execute and return table

问题描述

我有一个程序，它花了太多时间来返回表数据。

任何人都可以帮助我提高我的程序性能。





提前致谢



我尝试过：



ALTER程序[dbo]。[Prc_GetEmissionsData_MEP_test]

@Vessel varchar（1000），

@DateFrom日期时间，

@Dateto日期时间



as

开始

声明@Vessel_local varchar（1000），@ dateFrom_local datetime ，@ DatetTo_local datetime

set @Vessel_local = @Vessel

set @dateFrom_local = @DateFrom

set @DatetTo_local = @Dateto

声明@CurrentRowVsl int，@ RowToProcessVsl int

声明@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），@ TotalDi stanceCovered 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）

声明@Draft_fwd十进制（19,2），@ Draft_Aft十进制（19,2），@ ROB_Dep_Ifo十进制（19,2），@ ROB_Dep_Ifols十进制（19,2），@ ROB_Dep_Mdo十进制（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）

创建表#temp

（

ID int，

ReportDate Datetime，

VesselType varchar （100），

VesselID int，

VesselSize varchar（100），

VoyageID int，

VoyageType varchar （5），

Ves selName varchar（100），

ReportType varchar（50），

[Days] decimal（19,2），

TotalDistanceCovered decimal（19 ，2），

CargoOnBoard十进制（19,2），

Tot_IFO十进制（19,2），

Tot_IFOLS十进制（19,2） ），

Tot_MDO十进制（19,2），

Tot_MDOLS十进制（19,2），

avg_sulphur_ifo十进制（19,2），

avg_sulphur_mdo小数（19,2），

avg_sulphur_ifols十进制（19,2），

avg_sulphur_mdols十进制（19,2），

CO2十进制（19,2），

EEOI十进制（19,2），

SOx十进制（19,2），

SOxEff十进制（19,2），

NOx十进制（19,2），

NOxEff十进制（19,2），

原因varchar（500），

MEP_vesselid int，

MEP_voyage_id int，

MEP_Date datetime，

MEP_Average十进制（19,2），

MEP_MEKW十进制（19,2），

MEP_MECC十进制（19,2），

MEP_MECYL十进制（ 19,2），

MEP_AECC十进制（19,2），

MEP_Hours1十进制（19,2），

MEP_Hours2十进制（19,2），

MEP_Hours3十进制（19,2），

MEP_KW1十进制（19,2），

MEP_KW2十进制（19,2），

MEP_KW3十进制（19,2），

MEP_Distance十进制（19,2），

MEP_MaxTemp十进制（19,2），

MEP_MaxNBR十进制（19 ，2），

MEP_MinTemp十进制（19,2），

MEP_MinNBR十进制（19,2），

MEP_Sea十进制（19,2） ），

MEP_ER十进制（19,2），

MEP_ScavTemp十进制（19,2），

MEP_ScavPress十进制（19,2），

MEP_AverageTC十进制（19,2），

MEP_BHP十进制（19,2），

MEP_Prod十进制（19,2），

MEP_Cons十进制（19,2），

MEP_ROB十进制（19,2），

MEP_AE十进制（19,2），

MEP_ME十进制（19,2），

MEP_Reason varchar（100），

MEP_SteamingHrs十进制（19,2），

MEP_AE_Other_Reason varchar（500），

Draft_fwd十进制（19,2），

Draft_Aft十进制（19,2），

ROB_Dep_Ifo十进制（19,2），

ROB_Dep_Ifols十进制（19,2），

ROB_Dep_Mdo十进制（19,2），

ROB_Dep_Mdols十进制（19,2），

ROB_Arr_Ifo十进制（19,2），

ROB_Arr_Ifols十进制（19,2），

ROB_Arr_Mdo十进制（19,2），

ROB_Arr_Mdols十进制（19,2）

）

set @ ID = 1

SELECT * into #tempVessels FROM dbo.Split（@Vessel_local）

while（@dateFrom_local< = @DatetTo_local）

开始





选择identity（int，1,1）为RowID，v.vesselid，v.vesselname，vt.vessel_type，v.summerDWT_MT

从容器v内连接到#tempvsl 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



选择@VesselID = vesselid，@ VesselSize = cast（COALESCE（NULLIF（LTRIM（RTRIM）（案例summerDWT_MT' - '然后'0'否则summerDWT_MT结束）），''），'0'）十进制（19,2）），@ VesselType = vessel_type，@ VesselName =来自#tempvsl的VesselName，其中RowID = @ CurrentRowVsl

if @VesselSize> ; 0和@VesselSize< = 5000

设置@SizeName ='0-5k'



如果@VesselSize> 5000和@VesselSize< = 15000

设置@SizeName ='5k-15k'



如果@VesselSize> 15000和@VesselSize< = 35000

设置@SizeName ='15k-35k'



如果@VesselSize> 35000和@VesselSize< = 50000

设置@SizeName = '35k-50k'



如果@VesselSize> 50000和@VesselSize< = 100000

设置@SizeName ='50k-100k'



如果@VesselSize> 100000和@VesselSize< = 200000

设置@SizeName ='100k-200k'



如果@VesselSize> 200000和@VesselSize< = 300000

设置@SizeName ='200k-400k'

其他

设置@SizeName ='无信息'



- 不久报告从这里开始

选择身份（int，1,1）为NoonRowID，vesselid，voyage_id，cast（COALESCE（ NULLIF（LTRIM（RTRIM（steaming_time）），''），'0'）十进制（19,2））为steaming_time，cast（COALESCE（NULLIF（LTRIM（RTRIM（distence_covered）），''），'0' ）as decimal（19,2））as distence_covered，cast（COALESCE（LIFIM（RTRIM（consume_aeifo）），''），'0'）十进制（19,2））as consume_aeifo，cast（COALESCE（NULLIF） （LTRIM（RTRIM（consume_aemdo）），''），'0'）十进制（19,2））作为consume_aemdo，cast（COALESCE（NULLIF（LTRIM（RTRIM（consume_aeifols）），''），'0'） as decimal（19,2））as consume_aeifols，cast（COALESCE（NULLIF（LTRIM（RTRIM（consume_aemdols）），''），'0'）十进制（19,2））consume_aemdols，cast（COALESCE（NULLIF（LTRIM） （RTRIM（tc_ifo）），''），'0'）十进制（19,2））为tc_ifo，cast（COALESC） E（NULLIF（LTRIM（RTRIM（tc_mdo）），''），'0'）为十进制（19,2））为tc_mdo，cast（COALESCE（NULLIF（LTRIM（RTRIM（tc_ifols）），''），' 0'）十进制（19,2））为tc_ifols，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（tc_mdols）），''），'0'）为十进制（19,2））为tc_mdols，cast（COALESCE） （NULLTR（LTRIM（RTRIM（inherting_ifo）），''），'0'）十进制（19,2））inherting_ifo，cast（COALESCE（NULLIF（LTRIM（RTRIM（inherting_mdo）），''），'0 '）as decimal（19,2））asherting_mdo，cast（COALESCE（NULLIF（LTRIM（RTRIM（inherting_ifols）），''），'0'）十进制（19,2））asherting_ifols，cast（COALESCE（ NULLIF（LTRIM（RTRIM（inherting_mdols）），''），'0'）为十进制（19,2））为inherting_mdols，cast（COALESCE（NULLIF（LTRIM（RTRIM（drifting_ifo）），''），'0' ）作为十进制（19,2））作为drifting_ifo，施放（COALESCE（NULLIF（LTRIM（RTRIM（drifting_mdo）），''），'0'）作为十进制（19,2））作为drifting_mdo，cast（COALESCE（NULLIF） （LTRIM（RTRIM（drifting_ifols）），''），'0'）十进制（19,2））作为drifting_ifols，cast（COALESCE（NULLIF（LTRIM（RTRIM） IM（drifting_mdols）），''），'0'）十进制（19,2））作为drifting_mdols，cast（COALESCE（NULLIF（LTRIM（RTRIM（other_ifo）），''），'0'）为十进制（ 19,2））as other_ifo，cast（COALESCE（LIFIM（RTRIM（other_mdo）），''），'0'）十进制（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'）为十进制（19 ，2））as other_mdols，cast（COALESCE（NULLIF（LTRIM（RTRIM（ifo_ch）），''），'0'）为十进制（19,2））ifo_ch，cast（COALESCE（NULLIF（LTRIM（RTRIM（ mdo_ch）），''），'0'）十进制（19,2））为mdo_ch，cast（COALESCE（NULLIF（LTRIM（RTRIM（ifols_ch）），''），'0'）为十进制（19， 2））asols_ch，cast（COALESCE（NULLIF（LTRIM（RTRIM（mdols_ch）），''），'0'）十进制（19,2））为mdols_ch，cast（COALESCE（NULLIF（LTRIM）（RTRIM（txt_AE_ifo） ）），''），'0'）十进制（19,2））为txt_AE_ifo，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（txt_AE_mdo）），''），'0'）为十进制（19,2） ））作为txt_AE_mdo，c ast（COALESCE（NULLIF（LTRIM（RTRIM（txt_AE_ifols）），''），'0'）为十进制（19,2））为txt_AE_ifols，cast（COALESCE（NULLIF（LTRIM（RTRIM（txt_AE_mdols）），''） ，'0'）为十进制（19,2））为txt_AE_mdols，local_date

从tbl_noonreport进入#tempNoon，其中vesselid = @ VesselID和local_datetime = @dateFrom_local





声明@CurrentRowNoon int，@ RowToProcessNoon int

set @ RowsToProcessNoon = @@ ROWCOUNT

SET @ CurrentRowNoon = 0

while（@ CurrentRowNoon< @RowsToProcessNoon）

开始



SET @ CurrentRowNoon = @ CurrentRowNoon + 1



选择@ReportDate = @ dateFrom_local，@ VoyageID = voyage_id，@ ReportType ='中午报告'，@ Days = cast（COALESCE（NULLIF（LTRIM（Raming（steaming_time）） ），''），'0'）十进制（19,2））/ 24，@ TotalDistanceCovered = cast（COALESCE（NULLIF（LTRIM（RTRIM（distence_covered）），''），'0'）为十进制（19） ，2）），@ Tot_IFO =（演员（ COALESCE（NULLIF（LTRIM（RTRIM（consume_aeifo）），''），'0'）为十进制（19,2））+ cast（COALESCE（NULLIF（LTRIM（RTRIM（tc_ifo）），''），'0' ）作为十进制（19,2））+强制转换（COALESCE（NULLIF（LTRIM（RTRIM（inherting_ifo）），''），'0'）为十进制（19,2））+强制转换（COALESCE（NULLIF（LTRIM（RTRIM） （drifting_ifo）），''），'0'）十进制（19,2））+ cast（COALESCE（NULLIF（LTRIM（RTRIM（other_ifo）），''），'0'）为十进制（19,2） ））+ cast（COALESCE（NULLIF（LTRIM（RTRIM（ifo_ch）），''），'0'）为十进制（19,2））+ cast（COALESCE（NULLIF（LTRIM（RTRIM（txt_AE_ifo）），'' ），'0'）为十进制（19,2））），@ Tot_IFOLS =（cast（COALESCE（NULLIF（LTRIM（RTRIM（consume_aeifols）），''），'0'）为十进制（19,2）） + cast（COALESCE（NULLIF（LTRIM（RTRIM（tc_ifols）），''），'0'）为十进制（19,2））+ cast（COALESCE（NULLIF（LTRIM（RTRIM（inherting_ifols）），''）， '0'）十进制（19,2））+ cast（COALESCE（NULLIF（LTRIM（RTRIM（drifting_ifols）），''），'0'）十进制（19,2））+ cast（COALESCE（NULLIF（ LTRIM（RTRIM（other_ifols）），''），'0'）十进制（19,2））+ cast（ COALESCE（NULLIF（LTRIM（RTRIM（ifols_ch）），''），'0'）为十进制（19,2））+ cast（COALESCE（NULLIF（LTRIM（RTRIM（txt_AE_ifols）），''），'0' ）作为十进制（19,2））），@ Tot_MDO =（cast（COALESCE（NULLIF（LTRIM（RTRIM（consume_aemdo）），''），'0'）十进制（19,2））+ cast（COALESCE（ NULLIF（LTRIM（RTRIM（tc_mdo）），''），'0'）为十进制（19,2））+ cast（COALESCE（NULLIF（LTRIM（RTRIM（inherting_mdo）），''），'0'）as十进制（19,2））+强制转换（COALESCE（NULLIF（LTRIM（RTRIM（drifting_mdo）），''），'0'）为十进制（19,2））+强制转换（COALESCE（NULLIF（LTRIM（RTRIM（other_mdo） ）），''），'0'）十进制（19,2））+ cast（COALESCE（NULLIF（LTRIM（RTRIM（mdo_ch）），''），'0'）十进制（19,2）） + cast（COALESCE（LIFIM（RTRIM（txt_AE_mdo）），''），'0'）为十进制（19,2））），@ Tot_MDOLS =（cast（COALESCE（NULLIF（LTRIM（RTRIM（consume_aemdols）） ，''），'0'）十进制（19,2））+强制转换（COALESCE（NULLIF（LTRIM（RTRIM（tc_mdols）），''），'0'）十进制（19,2））+强制转换（COALESCE（NULLIF（LTRIM（RTRIM（inherting_mdols）），''），'0'）为十进制（19,2） ）+ cast（COALESCE（NULLIF（LTRIM（RTRIM（drifting_mdols）），''），'0'）十进制（19,2））+ cast（COALESCE（NULLIF（LTRIM（RTRIM（other_mdols）），''） ，'0'）十进制（19,2））+ cast（COALESCE（NULLIF（LTRIM（RTRIM（mdols_ch）），''），'0'）十进制（19,2））+ cast（COALESCE（NULLIF） （LTRIM（RTRIM（txt_AE_mdols）），''），'0'）十进制（19,2）））来自#tempNoon，其中NoonRowID = @ CurrentRowNoon



选择@VoyageType = voyage_type，@ CargoOnBoard = cast（COALESCE（NULLIF（LTRIM（RTRIM（total_cargo_onboard）），''），'0'）为十进制（19,2））来自tbl_fullaway，其中vesselid = @ VesselID和voyage_id = @ VoyageID

选择@ avg_sulphur_ifo =案例转换（COALESCE（NULLIF（LTRIM（RTRIM（avg_sulphur_ifo）），''），'0'）为十进制（19,2））当'0'然后' 3.3'else cast（COALESCE（LIFIM（RTRIM（avg_sulphur_ifo）），''），'3.3'）为十进制（19,2））end，@ avg_sulphur_mdo = case cast（COALESCE（NULLIF（LTRIM）（RTRIM（avg_sulphur_mdo） ）），''），'0'）为十进制（19,2））当'0'时'1.5'否则施放（COALESCE（LIFIM（RTRIM（avg_sulphur_mdo）），''），'1.5'）为十进制（19,2））end，@ avg_sulphur_ifols = case cast（COALESCE（NULLIF（LTRIM（RTRIM（ avg_sulphur_ifols）），''），'0'）为十进制（19,2））当'0'然后'0.4'否则施放（COALESCE（NULLIF（LTRIM（RTRIM（avg_sulphur_ifols）），''），'0.4' ）作为十进制（19,2））结束，@ avg_sulphur_mdols =案例强制转换（COALESCE（NULLIF（LTRIM（RTRIM（avg_sulphur_mdols）），''），'0'）为十进制（19,2））当'0'时'0.1'否则施放（COALESCE（LIFIM（RTRIM（avg_sulphur_mdols）），''），'0.1'）为十进制（19,2））从tbl_eosparrival结束，其中vesselid = @ VesselID和voyage_id = @ VoyageID
设置@CO2 =（（@ Toot_IFO + @Tot_IFOLS）* 3.114）+（（@ ToTot_MDO + @Tot_MDOLS）* 3.186）

设置@NOx =（（@Tot_IFO + @ Tot_IFOLS + @Tot_MDO + @Tot_MDOLS）* 84）* 0.001

设置@SOx =（（@Tot_IFO * @avg_sulphur_ifo）+（@ Toot_IFOLS * @avg_sulphur_ifols）+（@ ToTD_MDO * @avg_sulphur_mdo） +（@Tot_MDOLS * @avg_sulphur_ mdols））* 20）* 0.001



如果@CargoOnBoard = 0或@TotalDistanceCovered = 0或@ CO2 = 0或@NOx = 0或@SOx = 0

开始

设置@SOxEff = 0

设置@EEOI = 0

设置@NOxEff = 0


结束

其他

开始

设置@SOxEff =（@ SOx * 1000000） /（@CargoOnBoard * @TotalDistanceCovered）

set @EEOI = @ CO2 /（@ CaroOnBoard * @TotalDistanceCovered）* 1000000

set @NOxEff =（@NOx * 1000000） /（@ CargoOnBoard * @TotalDistanceCovered）

结束





set @Reason ='Correct'

如果@VoyageType ='L'

开始

如果@CargoOnBoard = 0

set @Reason ='CargoOnboard = 0'



如果@EEOI> 20

set @Reason ='EEOI> 20'



如果@ CO2< = 0

设置@Reason ='CO2< = 0'



如果@SOxEff> 2

设置@Reason ='SOxEff> 2'



如果@SOx< = 0

设置@Reason ='SOx< = 0'



如果@NOxEff> 10

set @Reason ='NOxEff> 10'



否则如果@NOx< = 0

set @Reason ='NOx< = 0'

结束





--- MEP报告从这里开始

声明@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十进制（19,2），@ MEP_Hours2十进制（19,2），@ MEP_Hours3十进制（19,2），@ MEP_KW1十进制（19,2），@ MEP_KW2十进制（19,2），@ MEP_KW3十进制（ 19,2），@ MEP_Distance十进制（19,2），@ MEP_MaxTemp十进制（19,2），@ MEP_MaxNBR十进制（19,2），@ MEP_MinTemp十进制（19,2），@ MEP_MinNBR十进制（19,2）， @MEP_Sea十进制（19,2），@ MEP_ER十进制（19,2），@ MEP_ScavTemp十进制（19,2），@ MEP_ScavPress十进制（19,2），@ MEP_AverageTC十进制（19,2），@ MEP_BHP十进制（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_Reaso n varchar（100），@ MEP_AE_Other_Reason as varchar（500）

声明@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十进制（19,2），@ MEP_MECYL_EOSP十进制（19,2），@ MEP_AECC_EOSP十进制（19,2），@ MEP_Hours1_EOSP十进制（19,2），@ MEP_Hours2_EOSP十进制（19,2），@ MEP_Hours3_EOSP十进制（19,2），@ MEP_KW1_EOSP十进制（19,2），@ MEP_KW2_EOSP十进制（19,2），@ MEP_KW3_EOSP十进制（19,2），@ MEP_Distance_EOSP十进制（19,2），@ MEP_MaxTemp_EOSP十进制（19,2） ，@ MEP_MaxNBR_EOSP十进制（19,2），@ MEP_MinTemp_EOSP十进制（19,2），@ MEP_MinNBR_EOSP十进制（19,2），@ MEP_Sea_EOSP十进制（19,2），@ MEP_ER_EOSP十进制（19,2），@ MEP_ScavTemp_EOSP十进制（ 19,2），@ MEP_ScavPress_EOSP十进制（19,2），@ MEP_AverageTC_EOSP十进制（19,2），@ MEP_BHP_EOSP十进制（19,2），@ MEP_Prod_EOSP十进制（19,2），@ MEP_Cons_EOSP十进制（19,2）， @MEP_ROB_EOSP十进制（19,2），@ MEP_AE_EOSP十进制（19,2），@ MEP_ME_EOSP十进制（19,2），@ MEP_SteamingHrs_EOSP十进制（19,2），@ MEP_Rea son_EOSP varchar（100），@ MEP_AE_Other_Reason_EOSP as varchar（500）

声明@RowsToProcessMEP int，@ CurrentRowMEP int



select identity（int， 1,1）作为MEPRowID，vesselid，voyage_id，转换（datetime，[date]）为MEP_Date，cast（COALESCE（NULLIF（LTRIM（RTRIM（平均值）），''），'0'）为十进制（19,2） ））作为平均值，施放（COALESCE（NULLIF（LTRIM（RTRIM（距离）），''），'0'）作为小数（19,2））作为距离，施法（COALESCE（NULLIF（LTRIM（RTRIM（MaxTemp）） ），''），'0'）十进制（19,2））作为MaxTemp，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（MaxNBR）），''），'0'）为十进制（19,2） ）作为MaxNBR，铸造（COALESCE（NULLIF（LTRIM（RTRIM（MinTemp）），''），'0'）作为十进制（19,2））作为MinTemp，施放（COALESCE（NULLIF（LTRIM（RTRIM（MinNBR）） ，''），'0'）十进制（19,2））作为MinNBR，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（海）），''），'0'）为十进制（19,2））作为Sea，演员（COALESCE（NULLIF（LTRIM（RTRIM（Er）），''），'0'）为十进制（19,2））为Er，cast（COALESCE（NULLIF（LTRIM（RTRIM（ScavTemp））， ''） ，'0'）十进制（19,2））为ScavTemp，强制转换（COALESCE（LIFIM（RTRIM（ScavPress）），''），'0'）为十进制（19,2））为ScavPress，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（AverageTC）），''），'0'）十进制（19,2））作为AverageTC，cast（COALESCE（NULLIF（LTRIM（RTRIM（BHP）），''）， '0'）为十进制（19,2））为BHP，铸造（COALESCE（NULLIF（LTRIM（RTRIM（MEKW）），''），'0'）为十进制（19,2））为MEKW，cast（ COALESCE（NULLIF（LTRIM（RTRIM（MECC）），''），'0'）为小数（19,2））为MECC，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（MECYL）），''），' 0'）十进制（19,2））作为MECYL，铸造（COALESCE（NULLIF（LTRIM（RTRIM（AECC）），''），'0'）作为十进制（19,2））作为AECC，施放（COALESCE） （NULLIF（LTRIM（RTRIM（Hours1）），''），'0'）为十进制（19,2））为Hours1，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（Hours2）），''），'0 '）作为十进制（19,2））作为Hours2，施放（COALESCE（NULLIF（LTRIM（RTRIM（Hours3）），''），'0'）作为小数（19,2））作为Hours3，施放（COALESCE（ NULLIF（LTRIM（RTRIM（KW1）），''），'0'）为十进制（19,2））为KW1，强制转换（COALESCE（NUL） LIF（LTRIM（RTRIM（KW2）），''），'0'）为十进制（19,2））为KW2，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（KW3）），''），'0' ）作为十进制（19,2））为KW3，原因，施放（COALESCE（NULLIF（LTRIM（RTRIM（Prod）），''），'0'）为十进制（19,2））为Prod，cast（COALESCE） （NULLIF（LTRIM（RTRIM（Cons）），''），'0'）十进制（19,2））为Cons，cast（COALESCE（NULLIF（LTRIM（RTRIM（Rob）），''），'0 '）作为十进制（19,2））作为Rob，施放（COALESCE（NULLIF（LTRIM（RTRIM（ae）），''），'0'）作为小数（19,2））作为ae，cast（COALESCE（ NULLIF（LTRIM（RTRIM（me）），''），'0'）十进制（19,2））和我一样，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（Steaming_Hrs）），''），'0' ）作为十进制（19,2））作为Steaming_Hrs，AE_Other_Reason

从tbl_mep到#TempMEP，其中vesselid = @ VesselID和voyage_id = @ VoyageID和Datetime_new = @dateFrom_local命令由id desc



设置@RowsToProcessMEP = @@ ROWCOUNT

SET @ CurrentRowMEP = 0





SET @ CurrentRowMEP = @ CurrentR owMEP + 1

选择@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 =距离，@ 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 =缺点，@ MEP_ROB = ROB，@ MEP_AE = AE，@ MEP_ME = ME，@ MEP_SteamingHrs = Steaming_Hrs，@ MEP_Reason = Reason，@ MEP_AE_Other_Reason = AE_Other_Reason来自#TempMEP，其中MEPRowID = @ CurrentRowMEP

如果@RowsToProcessMEP = 2

开始

SET @ CurrentRowMEP = @ CurrentRowMEP + 1

选择@MEP_vesselid_EOSP = vesselid，@ MEP_voyage_id_EOSP = voyage_id，@ MEP_Date_EOSP = convert （日期时间，MEP_Date），@ MEP _Average_EOSP =中等，@ MEP_MEKW_EOSP = MEKW，@ MEP_MECC_EOSP = MECC，@ MEP_MECYL_EOSP = MECYL，@ MEP_AECC_EOSP = AECC，@ MEP_Hours1_EOSP =小时1，@ MEP_Hours2_EOSP =小时2，@ MEP_Hours3_EOSP = Hours3，@ MEP_KW1_EOSP = KW1，@ MEP_KW2_EOSP = KW2，@ MEP_KW3_EOSP = KW3，@ MEP_Distance_EOSP =距离，@ MEP_MaxTemp_EOSP = MaxTemp，@ MEP_MaxNBR_EOSP = MaxNBR，@ MEP_MinTemp_EOSP = MinTemp，@ MEP_MinNBR_EOSP = MinNBR，@ MEP_Sea_EOSP =海，@ MEP_ER_EOSP = ER，@ MEP_ScavTemp_EOSP = ScavTemp，@ MEP_ScavPress_EOSP = ScavPress，@ MEP_AverageTC_EOSP = AverageTC，@ MEP_BHP_EOSP = BHP，@ MEP_Prod_EOSP =产品，@ MEP_ROC_EOSP =缺点，@ MEP_AE_EOSP = AE，@ MEP_ME_EOSP = ME，@ MEP_SteamingHrs_EOSP = Steaming_Hrs，@ MEP_Reason_EOSP =原因，@ MEP_AE_Other_Reason_EOSP = AE_Other_Reason来自＃ TempMEP，其中MEPRowID = @ CurrentRowMEP

结束





drop table #TempMEP

--- MEP报告在这里结束





如果@RowsToProcessNoon！= 0

begin



插入#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，硫氧化物，SOxEff，氮氧化物，NOxEff，原因，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）

值（@ ID，@ ReportDate @ VesselType，@ VesselID，@ SizeName，@ VoyageID，@ VoyageType，@ VesselName，@ REPORTTYPE，@天，@ TotalDistanceCovered，@ CargoOnBoard，@ Tot_IFO，@ Tot_IFOLS，@ Tot_MDO，@ Tot_MDOLS，@ avg_sulphur_ifo，@ avg_sulphur_mdo，@ avg_sulphur_ifols，@ avg_sulphur_mdols，@二氧化碳，@ EEOI，@硫氧化物，@ SOxEff，@氮氧化物，@ NOxEff，@原因，@ MEP_vesselid，@ MEP_voya ge_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）



设置@ID = @ ID + 1

结束



结束

drop table #tempNoon

- 中午报告在此结束



- EOSP报告从这里开始



选择身份（int，1,1）为EOSPRowID，voyage_id，vesselid，cast（COALESCE（NULLIF（LTRIM（RTRIM（streaming_days）），''），'0'）为十进制（ 19,2））作为streaming_days，施放（COALESCE（NULLIF（LTRIM（RTRIM（distance_streamed_last_noon），''），'0'）为十进制（19,2））为distance_streamed_last_noon，cast（COALESCE（NULLIF（LTRIM（RTRIM） （meae_ifo）），''），'0'）十进制（19,2）） meae_ifo，cast（COALESCE（NULLIF（LTRIM（RTRIM（meae_mdo）），''），'0'）十进制（19,2））meae_mdo，cast（COALESCE（NULLIF（LTRIM（RTRIM（meae_ifols）），' '），'0'）十进制（19,2））meae_ifols，cast（COALESCE（NULLIF（LTRIM（RTRIM（meae_mdols）），''），'0'）十进制（19,2））meae_mdols ，施放（COALESCE（NULLIF（LTRIM（RTRIM（tank_cleaning_ifo_last_noon）），''），'0'）为十进制（19,2））as tank_cleaning_ifo_last_noon，cast（COALESCE（NULLIF（LTRIM（RCRIM（tank_cleaning_mdo_last_noon）），'' ），'0'）为十进制（19,2））as tank_cleaning_mdo_last_noon，cast（COALESCE（NULLIF（LTRIM（RTRIM（tank_cleaning_ifols_last_noon）），''），'0'）为十进制（19,2））为tank_cleaning_ifols_last_noon， cast（COALESCE（NULLIF（LTRIM（RTRIM（tank_cleaning_mdols_last_noon）），''），'0'）为十进制（19,2））as tank_cleaning_mdols_last_noon，cast（COALESCE（NULLIF（LTRIM（RTRIM（inherting_ifo_last_noon）），''） ，'0'）为十进制（19,2））为inherting_ifo_last_noon，cast（COALESCE（NULLIF（LTRIM（RTRIM（inherting_mdo_last_noon）），' ），'0'）为十进制（19,2））为inherting_mdo_last_noon，cast（COALESCE（NULLIF（LTRIM（RTRIM（inherting_ifols_last_noon）），''），'0'）为十进制（19,2））为inherting_ifols_last_noon， cast（COALESCE（NULLIF（LTRIM（RTRIM（inherting_mdols_last_noon）），''），'0'）为十进制（19,2））为inherting_mdols_last_noon，cast（COALESCE（NULLIF（LTRIM（RIFT（漂移_纬度）），''） ，'0'）十进制（19,2））作为drifting_ifo_last_noon，cast（COALESCE（NULLIF（LTRIM（RTRIM（drifting_mdo_last_noon）），''），'0'）十进制（19,2））drifting_mdo_last_noon，cast （COALESCE（NULLIF（LTRIM（RTRIM（drifting_ifols_last_noon）），''），'0'）为十进制（19,2））为drifting_ifols_last_noon，cast（COALESCE（NULLIF（LTRIM（RTRIM（drifting_mdols_last_noon）），''）， '0'）十进制（19,2））作为drifting_mdols_last_noon，施放（COALESCE（NULLIF（LTRIM（RTRIM（other_ifo）），''），'0'）为十进制（19,2））as other_ifo，cast（ COALESCE（NULLIF（LTRIM（RTRIM（other_mdo）），''），'0'）十进制（19,2））as other_mdo，cast（COALESCE（NULLIF（LT） RIM（RTRIM（other_ifols）），''），'0'）为十进制（19,2））as_ifols，cast（COALESCE（NULLIF（LTRIM（RTRIM（other_mdols）），''），'0'）as decimal（19,2））as other_mdols，cast（COALESCE（NULLIF（LTRIM（RTRIM（txt_NoonBCifo）），''），'0'）为十进制（19,2））为txt_NoonBCifo，cast（COALESCE（NULLIF（LTRIM） （RTRIM（txt_noonBCmdo）），''），'0'）为十进制（19,2））为txt_noonBCmdo，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（txt_noonBCifols）），''），'0'）为十进制（19,2））作为txt_noonBCifols，施放（COALESCE（NULLIF（LTRIM（RTRIM（txt_noonBCmdols）），''），'0'）为十进制（19,2））为txt_noonBCmdols，cast（COALESCE（NULLIF（LTRIM（ RTRIM（lastnoonAE_ifo）），''），'0'）十进制（19,2））作为lastnoonAE_ifo，施放（COALESCE（NULLIF（LTRIM（RTRIM（lastnoonAE_mdo）），''），'0'）为十进制（ 19,2））作为lastnoonAE_mdo，施放（COALESCE（NULLIF（LTRIM（RTRIM（lastnoonAE_ifols）），''），'0'）作为十进制（19,2））作为lastnoonAE_ifols，cast（COALESCE（NULLIF（LTRIM（RTRIM） （lastnoonAE_mdols）），''），'0'）十进制（19,2））作为lastnoonAE_mdo ls，date_sea_passage_end，cast（COALESCE（NULLIF（LTRIM（RTRIM（avg_sulphur_ifo）），''），'3.3'）为十进制（19,2））为avg_sulphur_ifo，cast（COALESCE（NULLIF（LTRIM（avg_sulphur_mdo）） ，''），'1.5'）为十进制（19,2））为avg_sulphur_mdo，cast（COALESCE（NULLIF（LTRIM（RTRIM（avg_sulphur_ifols）），''），'0.4'）为十进制（19,2）） as avg_sulphur_ifols，cast（COALESCE（NULLIF（LTRIM（RTRIM（avg_sulphur_mdols）），''），'0.1'）为十进制（19,2））为avg_sulphur_mdols，cast（COALESCE（NULLIF（LTRIM（RTRIM（ROB_IFO））， ''），'0'）十进制（19,2））作为ROB_IFO，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（ROB_IFOLS）），''），'0'）为十进制（19,2））as ROB_IFOLS，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（ROB_MDO）），''），'0'）为十进制（19,2））为ROB_MDO，强制转换（COALESCE（NULLIF（LTRIM（RTRIM（ROB_MDOLS）），' '），'0'）十进制（19,2））作为ROB_MDOLS

来自tbl_eosparrival的#tempEOSP，其中vesselid = @VesselID和Datetime_SeaPassage_end = @dateFrom_local





声明@CurrentRowEOSP int，@ RowToProcessEOSP int

set @ RowsToProcessEOSP = @@ ROWCOUNT

SET @ CurrentRowEOSP = 0

while（@ CurrentRowEOSP< @RowsToProcessEOSP ）

开始



SET @ CurrentRowEOSP = @ CurrentRowEOSP + 1



选择@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_no on + other_mdols + txt_NoonBCmdols + lastnoonAE_mdols），@ avg_sulphur_ifo = case cast（COALESCE（NULLIF（LTRIM（RTRIM（avg_sulphur_ifo）），''），'0'）十进制（19,2））当'0'然后'3.3 'else cast（COALESCE（LIFIM（RTRIM（avg_sulphur_ifo）），''），'3.3'）十进制（19,2））end，@ avg_sulphur_mdo = case cast（COALESCE（NULLIF）（LTRIM（RgIM（avg_sulphur_mdo）） ），''），'0'）为十进制（19,2））当'0'然后'1.5'否则施放（COALESCE（NULLIF（LTRIM（RTRIM（avg_sulphur_mdo）），''），'1.5'）十进制（19,2））结束，@ avg_sulphur_ifols =案例强制转换（COALESCE（NULLIF（LTRIM（RTRIM（avg_sulphur_ifols）），''），'0'）为十进制（19,2））当'0'然后'0.4 'else cast（COALESCE（LIFIM（RTRIM（avg_sulphur_ifols）），''），'0.4'）十进制（19,2））end，@ avg_sulphur_mdols = case cast（COALESCE（NULLIF（LTRIM（avg_sulphur_mdols）） ），''），'0'）为十进制（19,2））当'0'然后'0.1'否则施放（COALESCE（NULLIF（LTRIM（RTRIM（avg_sulphur_mdols）），''），'0.1'）十进制（19,2））结束，@ ROB _Arr_Ifo = ROB_IFO，@ ROB_Arr_Ifols = ROB_IFOLS，@ ROB_Arr_Mdo = ROB_MDO，@ ROB_Arr_Mdols =来自#tempEOSP的ROB_MDOLS，其中EOSPRowID = @ CurrentRowEOSP



选择@VoyageType = voyage_type，@ CargoOnBoard = cast（COALESCE（LIFIM（RTRIM（total_cargo_onboard）），''），'0'）为十进制（19,2）），@ Draft_fwd = cast（COALESCE（NULLIF（LTRIM（RTRIM（dep_draftFWD）），' '），'0'）为十进制（19,2）），@ Draft_Aft = cast（COALESCE（NULLIF（LTRIM（RTRIM（dep_draftAFT）），''），'0'）为十进制（19,2））， @ ROB_Dep_Ifo = cast（COALESCE（LIFIM（RTRIM（Rob_Dep_ifo）），''），'0'）为十进制（19,2）），@ ROB_Dep_Ifols = cast（COALESCE（NULLIF（LTRIM（RTRIM（Rob_Dep_ifols）） ，''），'0'）为十进制（19,2）），@ ROB_Dep_Mdo = cast（COALESCE（NULLIF（LTRIM（RTRIM（Rob_Dep_mdo）），''），'0'）为十进制（19,2） ），@ ROB_Dep_Mdols =来自tbl_fullaway的COALESCE（NULL（LTRIM（RTRIM（Rob_Dep_mdols）），''），'0'）十进制（19,2））其中vesselid = @ VesselID和voyage_id = @ VoyageID


设定@ CO2 =（（@ Toot + IFO + @Tot_IFOLS）* 3.114）+（（@ ToTot_MDO + @Tot_MDOLS）* 3.186）

设置@NOx =（（@Tot_IFO + @Tot_IFOLS + @Tot_MDO + @ Tot_MDOLS）* 84）* 0.001

设置@SOx =（（@Tot_IFO * @avg_sulphur_ifo）+（@ Toot_IFOLS * @avg_sulphur_ifols）+（@ ToTD_MDO * @avg_sulphur_mdo）+（@ ToTM_MDOLS * @ avg_sulphur_mdols））* 20）* 0.001



如果@CargoOnBoard = 0或@TotalDistanceCovered = 0或@ CO2 = 0或@NOx = 0或@SOx = 0

\t\t\t\t\tbegin

\t\t\t\t\t\tset @SOxEff = 0

\t\t\t\t\t\tset @EEOI = 0

\t\t\t\t\t\tset @NOxEff = 0

\t\t\t\t\t

\t\t\t\t\tend

\t\t\t\t\telse

\t\t\t\t\tbegin

\t\t\t\t\t\tset @SOxEff = (@SOx * 1000000) / (@CargoOnBoard * @TotalDistanceCovered)

\t\t\t\t\t\tset @EEOI = @CO2 / (@CargoOnBoard * @TotalDistanceCovered) * 1000000

\t\t\t\t\t\tset @NOxEff = (@NOx * 1000000) / (@CargoOnBoard * @TotalDistanceCovered)

\t\t\t\t\tend

\t\t\t\t

\t\t\t\t

\t\t\t\t\tset @Reason =’Correct’

\t\t\t\t\tif @VoyageType = ’L’

\t\t\t\t\tbegin

\t\t\t\t\t\tif @CargoOnBoard = 0

\t\t\t\t\t\tset @Reason =’CargoOnboard = 0’

\t\t\t\t

\t\t\t\t\telse if @EEOI > 20

\t\t\t\t\t\tset @Reason =’EEOI > 20’

\t\t\t\t

\t\t\t\t\telse if @CO2 <= 0

\t\t\t\t\t\tset @Reason =’CO2 <= 0’

\t\t\t\t

\t\t\t\t\telse if @SOxEff > 2

\t\t\t\t\t\tset @Reason =’SOxEff > 2’

\t\t\t

\t\t\t\t\telse if @SOx <= 0

\t\t\t\t\t\tset @Reason =’SOx <= 0’

\t\t\t\t

\t\t\t\t\telse if @NOxEff > 10

\t\t\t\t\t\tset @Reason =’NOxEff > 10’

\t\t\t\t

\t\t\t\t\telse if @NOx <= 0

\t\t\t\t\t\tset @Reason =’NOx <= 0’

\t\t\t\t\tend

\t\t\t\t

\t\t\t\t\tif @RowsToProcessEOSP != 0

\t\t\t\t\tbegin

\t\t\t\t\t\tinsert 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)
$b$ b \t\t\t\t\t\tvalues(@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)
$ b$b \t\t\t\t\t\tSet @ID = @ID+1

\t\t\t\t\tend

\t\t\t\t\t

\t\t\t\tEnd

\t\t\t\tdrop table #tempEOSP



\t\t\t\t-- EOSP Report Ends here

\t\t\t\t-- IDLE Report Starts here



\t\t\t\tselect 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(NULLI F(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

\t\t\t\tinto #tempIDLE from tbl_idlereport where vesselid = @VesselID and Port_Datetime = @dateFrom_local



\t\t\t\t

\t\t\t\tDeclare @CurrentRowIDLE int , @RowsToProcessIDLE int

\t\t\t\tset @RowsToProcessIDLE= @@ROWCOUNT

\t\t\t\tSET @CurrentRowIDLE=0

\t\t\t\twhile(@CurrentRowIDLE<@RowsToProcessIDLE)

\t\t\t\tbegin

\t\t\t\t

\t\t\t\t\tSET @ CurrentRowIDLE=@CurrentRowIDLE+1

\t\t\t\t\tDeclare @port_date as datetime,@Pre_port_date as datetime ,@EOSP_Date as datetime

\t\t\t\t\tselect @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

\t\t\t\t\t

\t\t\t\t\t

\t\t\t\t\tdeclare @ppp nvarchar(30)

\t\t\t\t\tset @Pre_port_date=’’

\t\t\t\t\tset @EOSP_Date=’’

\t\t\t\tselect @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

\t\t\t\tselect 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

\t\t\t\tselect 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

\t\t\t\t\t

\t\t\t\t\tif @Pre_port_date != ’’

\t\t\t\t\tbegin

\t\t\t\t\t\tset @Days = DATEDIFF(hour,@Pre_port_date,@port_date)

\t\t\t\t\t\t

\t\t\t\t\t\tif @Days = 0

\t\t\t\t\t\tbegin

\t\t\t\t\t\t\tif @EOSP_Date = ’’

\t\t\t\t\t\t\t\tset @Days = -1

\t\t\t\t\t\t\telse

\t\t\t\t\t\t\t\tset @Days = DATEDIFF(hour,@EOSP_Date,@port_date)

\t\t\t\t\t\t\t\t

\t\t\t\t\t\tend

\t\t\t\t\tend

\t\t\t\t\telse

\t\t\t\t\tbegin

\t\t\t\t\t\tif @EOSP_Date = ’’

\t\t\t\t\t\t\t\tset @Days = -1

\t\t\t\t\t\t\telse

\t\t\t\t\t\t\t\tset @Days = DATEDIFF(hour,@EOSP_Date,@port_date)

\t\t\t\t\t\t\t\t

\t\t\t\t\tend

\t\t\t\t\t

\t\t\t\t\tselect @VoyageType = voyage_type,@CargoOnBoard= cast(COALESCE(NULLIF(LTRIM(RTRIM(total_cargo_onboard)),’’),’0’) as decimal(19,2)) from tbl_full away where vesselid=@VesselID and voyage_id=@VoyageID

\t\t\t\t\t

\t\t\t\t\tset @CO2 = ((@Tot_IFO + @Tot_IFOLS) * 3.114) + ((@Tot_MDO + @Tot_MDOLS) * 3.186)

\t\t\t\t\tset @NOx = ((@Tot_IFO + @Tot_IFOLS + @Tot_MDO + @Tot_MDOLS) * 84) * 0.001

\t\t\t\t\tset @SOx = (((@Tot_IFO * @avg_sulphur_ifo) + (@Tot_IFOLS * @avg_sulphur_ifols) + (@Tot_MDO * @avg_sulphur_mdo) + (@Tot_MDOLS * @avg_sulphur_mdols)) * 20) * 0.001

\t\t\t\t

\t\t\t\t\tif @CargoOnBoard = 0 or @TotalDistanceCovered = 0 or @CO2 = 0 or @NOx = 0 or @SOx = 0

\t\t\t\t\tbegin

\t\t\t\t\t\tset @SOxEff = 0

\t\t\t\t\t\tset @EEOI = 0

\t\t\t\t\t\tset @NOxEff = 0

\t\t\t\t\t

\t\t\t\t\tend

\t\t\t\t\telse

\t\t\t\t\tbegin

\t\t\t\t\t\tset @SOxEff = (@SOx * 1000000) / (@CargoOnBoard * @TotalDistanceCovered)

\t\t\t\t\t\tset @EEOI = @CO2 / (@CargoOnBoard * @TotalDistanceCovered) * 1000000

\t\t\t\t\t\tset @NOxEff = (@NOx * 1000000) / (@CargoOnBoard * @To talDistanceCovered)

\t\t\t\t\tend

\t\t\t\t

\t\t\t\t

\t\t\t\t\tset @Reason =’Correct’

\t\t\t\t\tif @VoyageType = ’L’

\t\t\t\t\tbegin

\t\t\t\t\t\tif @CargoOnBoard = 0

\t\t\t\t\t\tset @Reason =’CargoOnboard = 0’

\t\t\t\t

\t\t\t\t\telse if @EEOI > 20

\t\t\t\t\t\tset @Reason =’EEOI > 20’

\t\t\t\t

\t\t\t\t\telse if @CO2 <= 0

\t\t\t\t\t\tset @Reason =’CO2 <= 0’

\t\t\t\t

\t\t\t\t\telse if @SOxEff > 2

\t\t\t\t\t\tset @Reason =’SOxEff > 2’

\t\t\t

\t\t\t\t\telse if @SOx <= 0

\t\t\t\t\t\tset @Reason =’SOx <= 0’

\t\t\t\t

\t\t\t\t\telse if @NOxEff > 10

\t\t\t\t\t\tset @Reason =’NOxEff > 10’

\t\t\t\t

\t\t\t\t\telse if @NOx <= 0

\t\t\t\t\t\tset @Reason =’NOx <= 0’

\t\t\t\t\tend

\t\t\t\t

\t\t\t\t\tif @RowsToProcessIDLE != 0

\t\t\t\t\tbegin

\t\t\t\t\t\tif @Days = -1

\t\t\t\t\t\t\tset @Reason =’Date Not Available’

\t\t\t\t\t\telse

\t\t\t\t\t\t\tset @Days = @Days/24

\t\t\t\t\t\tinsert 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)

\t\t\t\t\t\tvalues(@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)

\t\t\t\t\t\tSe t @ID = @ID+1

\t\t\t\t\tend

\t\t\t\t\t

\t\t\t\tEnd

\t\t\t\t

\t\t\t\tdrop table #tempIDLE



\t\t\t\t-- IDLE Report Ends here

\t\t\tend

\t\t\tdrop table #tempvsl

\t\t\t

\t\tset @dateFrom_local = DATEADD(day,1,@dateFrom_local)

\t end

\t drop table #tempVessels

select * from #temp

end

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

推荐答案

Hi,



You are the only one who can help yourself. Check below link which will help you to speed up your queries.



How Do I Speed Up My Select * Query Of \"530,000\" Rows[^]





Hope this will help you.



Cheers

Hi,

You are the only one who can help yourself. Check below link which will help you to speed up your queries.

How Do I Speed Up My Select * Query Of "530,000" Rows[^]


Hope this will help you.

Cheers

这篇关于过程需要花费太多时间来执行和返回表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！