27,579
社区成员
发帖
与我相关
我的任务
分享
SET @cursorBody = 'DECLARE temp_cursor CURSOR FOR select
ID
,length
FROM
(
SELECT
ERPDispatch.id AS ID
,ERPDispatch.length
,CASE WHEN leaveTime IS NULL --离港近港时间
THEN ArrivalTime
ELSE leaveTime
END AS ShipStartTime
FROM
ERPDispatch
INNER JOIN
ERPInvoiceDetail ON ERPInvoiceDetail.dispatchid = ERPDispatch.id
INNER JOIN
ERPInvoice ON ERPInvoiceDetail.invoiceid = ERPInvoice.id
WHERE
(ERPDispatch.status = ''0'')
AND
(ERPDispatch.dispatchend = ''1'' OR ERPDispatch.dispatchend = ''2'')
';
IF @shipName != ''
BEGIN
SET @cursorBody = @cursorBody+' AND ERPInvoiceDetail.chinaname IN('+@shipName+')';
END;
SET @cursorBody = @cursorBody+' AND
(ERPInvoice.rateitemid1 = ''LNG''
OR ERPInvoice.rateitemid1 = ''集装箱''
OR ERPInvoice.rateitemid1 = ''油船''
OR ERPInvoice.rateitemid1 = ''滚装船'')
AND
(ERPInvoiceDetail.code !=''SH01'')
AND
(ERPInvoiceDetail.character != ''05'')
) TMP1
WHERE
CONVERT(VARCHAR(10),ShipStartTime,23)>='''+@starttime+'''
AND
CONVERT(VARCHAR(10),ShipStartTime,23)<='''+@endtime+'''';
EXEC (@cursorBody);
OPEN temp_cursor;
FETCH NEXT FROM temp_cursor INTO @ID, @Lenght;
WHILE(@@FETCH_STATUS = 0)
BEGIN
--游标用于第二层循环,用于寻找船号@code 船名@chinaname 开始时间@starttime 结束时间@endtime
SET @cursorShipBody = 'DECLARE temp_cursorship CURSOR FOR
select code,chinaname from dbo.ERPInvoiceDetail where dispatchid='+CONVERT(VARCHAR(10), @ID);
IF @shipName != ''
BEGIN
SET @cursorShipBody = @cursorShipBody+' AND ERPInvoiceDetail.chinaname IN('+@shipName+')';
END;
EXEC (@cursorShipBody);
OPEN temp_cursorship;
FETCH NEXT FROM temp_cursorship INTO @code, @chinaname;
WHILE @@FETCH_STATUS = 0
BEGIN
--艘次查询
--查询该拖轮的MMSI 号
--计算能耗值
BEGIN
--插入表中数据 -- 更新能耗报表里能耗值
END;
FETCH NEXT FROM temp_cursorship INTO @code, @chinaname;
END;
CLOSE temp_cursorship;
DEALLOCATE temp_cursorship;
FETCH NEXT FROM temp_cursor INTO @ID, @Lenght;
END;
CLOSE temp_cursor;
DEALLOCATE temp_cursor;