DECLARE @StartDate DATETIME= '2017-12-20 00:00:00';
DECLARE @EndDate DATETIME= '2017-12-26 00:00:00';
DECLARE @Country NVARCHAR(20)= '';
DECLARE @到港单量 INT;
DECLARE @派送单量 INT;
DECLARE @妥投单量 INT;
DECLARE @累积在分拨仓单量 INT;
DECLARE @累积派送在途单量 INT;
DECLARE @累积未派送完成单量 INT;
DECLARE @自提已上架量 INT;
DECLARE @派送已签收量 INT;
DECLARE @派送异常单量 INT;
DECLARE @派送异常单量占比 VARCHAR(10);
SELECT @到港单量 = COUNT(DISTINCT a.TransportOrderID)
FROM dbo.TWX_TransportOrder a WITH ( NOLOCK )
LEFT JOIN dbo.Log_TransportOrderTrack b WITH ( NOLOCK ) ON a.TransportOrderID = b.TransportOrderID
WHERE a.Country = @Country
AND b.TrackActionCode IN ( 'CAI_CUSTOMS_CLR_SUC', 'OTMS_SENT_CITY' )
AND b.TrackTime >= @StartDate
AND b.TrackTime <= @EndDate;
SELECT @派送单量 = COUNT(DISTINCT a.TransportOrderID)
FROM dbo.TWX_TransportOrder a WITH ( NOLOCK )
LEFT JOIN dbo.Log_TransportOrderTrack b WITH ( NOLOCK ) ON a.TransportOrderID = b.TransportOrderID
WHERE a.Country = @Country
AND b.TrackActionCode IN ( 'GTMS_DELIVERING', 'SENT_SCAN',
'OTMS_DLV_ATTEMPT', 'GTMS_RE_DELIVERING',
'GTMS_DELIVERING' )
AND b.TrackTime >= @StartDate
AND b.TrackTime <= @EndDate;
SET @累积在分拨仓单量 = @到港单量 - @派送单量;
IF @累积在分拨仓单量 < 0
BEGIN
SET @累积在分拨仓单量 = 0;
END;
SELECT @派送异常单量 = COUNT(DISTINCT a.TransportOrderID)
FROM dbo.TWX_TransportOrder a WITH ( NOLOCK )
LEFT JOIN dbo.Log_TransportOrderTrack b WITH ( NOLOCK ) ON a.TransportOrderID = b.TransportOrderID
WHERE a.Country = @Country
AND b.TrackActionCode IN ( 'CAI_SIGN_IN_FAIL', 'FAILED',
'GTMS_DEL_FAILURE' )
AND b.TrackTime >= @StartDate
AND b.TrackTime <= @EndDate;
SET @累积派送在途单量 = @派送单量 + @派送异常单量;
SET @累积未派送完成单量 = @累积在分拨仓单量 + @累积派送在途单量;
SELECT @派送已签收量 = COUNT(DISTINCT a.TransportOrderID)
FROM dbo.TWX_TransportOrder a WITH ( NOLOCK )
LEFT JOIN dbo.Log_TransportOrderTrack b WITH ( NOLOCK ) ON a.TransportOrderID = b.TransportOrderID
WHERE a.Country = @Country
AND b.TrackCode IN ( 'GTMS_SIGNED', 'SIGNED', 'GSTA_SIGN',
'STATION_SIGNED' )
AND b.TrackTime >= @StartDate
AND b.TrackTime <= @EndDate;
SET @妥投单量 = @派送已签收量 + @累积派送在途单量;
IF ( @派送单量 = 0
AND @派送异常单量 = 0
)
BEGIN
SET @派送异常单量占比 = '0%';
END;
ELSE
BEGIN
SET @派送异常单量占比 = CAST(ROUND(( CONVERT(FLOAT, @派送异常单量)
/ CONVERT(FLOAT, @妥投单量) ) * 100, 2) AS VARCHAR(10))
+ '%';
END;
SELECT @StartDate '开始时间' ,
@EndDate '结束时间' ,
@Country '区域' ,
@到港单量 '到港单量' ,
@派送单量 '派送单量' ,
@累积在分拨仓单量 '累积在分拨仓单量' ,
@累积派送在途单量 '累积派送在途单量' ,
@累积未派送完成单量 '累积未派送完成单量' ,
@妥投单量 '妥投单量' ,
@派送异常单量 '派送异常单量' ,
@派送异常单量占比 '派送异常单量占比';