8AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAjatZAapyo0SdzktcY+dJrgAAAAAIAAAARABUAFMAAAAQZgAAAAEAACAAAACrEZHXr+UYkjiHkGEvwgSmrcBnnI8ExX39GxkqNKGpyAAAAAAOgAAAAAIAACAAAAAMG0hkxePKJ1Q05LCkj8zhA7KyqGkCenXjgxKQeeCAyDAAAABTVvuvePAfYbDEvi8ypverHJhLyBBlFhaLk1URFTXHZSA3pvlOAUtcaMCOtlyCVFdAAAAA4+BuLz4MTP4Gb4jNLv//qrfRCzqEXILgutEMESdX/QeEUAquOAUm+/ET2PTLb124BvdPp77otmA1OZdoj8N7cgAQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAjatZAapyo0SdzktcY+dJrgAAAAAIAAAARABUAFMAAAAQZgAAAAEAACAAAABQRUq3R6jHoiGFawKd5H+aTWaXyJZbJ3nMxeSLac7IcAAAAAAOgAAAAAIAACAAAACkBY250380L/S7ykiX/ZNtjI6UUVyAd3dAnZ75kABsijAAAAD/moRFD2gN1ATRJOVFQ1koVncAHJePRmGiLqZNH7j7DoQXiXNdBnXRHJsVp8m3M1lAAAAAV1oMacCWkuHvCVWRhP17gTI0yBx5kkcaXRC1wKAAizqPdCgAfSo4KsKMOlPDSW9N8qw2PvBcj9pxVpqKm2v/Bw0--提单号、服务渠道、下单渠道、分区、州/省、出货日期、口岸、代理
--上网票数、签收票数、出货票数、收货重量、2-3内上网票数、称重-签收、N-N时效内票数、出货日期、起飞时间、落地时间、第一票上网时间
select c.LadingBillNumber[提单号],
a.实际派送渠道 ,
case when a.渠道名称 = '美国快线YSW' then '美国快线' else a.渠道名称 end as [渠道名称],
a.末端分区,
a.所属客户,
isnull(f.Name,a.州名) as 州名,
CONVERT(nvarchar(10),d.LeaveWarehouseTime,120)[出货日期],
CONVERT(nvarchar(10),a.称重时间,120)[收货日期],
convert(nvarchar(10),d.DepartTime,120)[起飞日期],
convert(nvarchar(16),d.ArrivalTime,120)[落地日期],
convert(nvarchar(16),d.ClearanceTime,120)[提取日期],
min(e.OnlineTime)[第一票上网时间],
d.Destination[口岸],
d.ServiceName[代理],
sum(case when e.OnlineTime is not null then 1 else 0 end)[上网票数],
sum(case when e.signtime is not null then 1 else 0 end)[签收票数],
sum(case when e.SignTime is not null then ceiling(datediff(DAY,a.称重时间,e.SignTime)) else 0 end) [总时效],
count(0)[出货票数],
sum(a.称重重量/1000.0)[收货重量],
sum(case when e.OnlineDay<=2 then 1 else 0 end)[_2天内上网票数],
sum(case when e.OnlineDay<=3 then 1 else 0 end)[_3天内上网票数],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=5 then 1 else 0 end)[_5天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=6 then 1 else 0 end)[_6天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=7 then 1 else 0 end)[_7天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=8 then 1 else 0 end)[_8天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=9 then 1 else 0 end)[_9天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=10 then 1 else 0 end)[_10天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=11 then 1 else 0 end)[_11天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=12 then 1 else 0 end)[_12天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=13 then 1 else 0 end)[_13天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=14 then 1 else 0 end)[_14天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=15 then 1 else 0 end)[_15天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=16 then 1 else 0 end)[_16天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=17 then 1 else 0 end)[_17天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=18 then 1 else 0 end)[_18天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=19 then 1 else 0 end)[_19天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=20 then 1 else 0 end)[_20天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=21 then 1 else 0 end)[_21天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=22 then 1 else 0 end)[_22天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=23 then 1 else 0 end)[_23天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=24 then 1 else 0 end)[_24天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)<=25 then 1 else 0 end)[_25天内称重至签收],
sum(case when ceiling(datediff(HOUR,a.称重时间,e.SignTime) / 24.0)>25 then 1 else 0 end)[_超过25天称重至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=4 then 1 else 0 end)[_4天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=5 then 1 else 0 end)[_5天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=6 then 1 else 0 end)[_6天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=7 then 1 else 0 end)[_7天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=8 then 1 else 0 end)[_8天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=9 then 1 else 0 end)[_9天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=10 then 1 else 0 end)[_10天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=11 then 1 else 0 end)[_11天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=12 then 1 else 0 end)[_12天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=13 then 1 else 0 end)[_13天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=14 then 1 else 0 end)[_14天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=15 then 1 else 0 end)[_15天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=16 then 1 else 0 end)[_16天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=17 then 1 else 0 end)[_17天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=18 then 1 else 0 end)[_18天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=19 then 1 else 0 end)[_19天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)<=20 then 1 else 0 end)[_20天内上网至签收],
sum(case when ceiling(datediff(HOUR,e.OnlineTime,e.SignTime) / 24.0)>20 then 1 else 0 end)[_超过20天上网至签收]
from BaseOrderInfo(nolock) a
left join Logistics_BoxDetail(nolock)b on a.OrderId=b.OrderId
left join logistics_box(nolock)c on c.Id=b.BoxId and c.IsDelete=0
left join Logistics_LadingBill(nolock)d on d.LadingBillNumber=c.LadingBillNumber and d.IsDeleted = 0
left join Logistics_Trace(nolock)e on e.OrderId=a.OrderId
left join state_codes(nolock)f on a.州名=f.code and a.目的国家= f.CountryCode
where d.LeaveWarehouseTime >'2022-08-01'
group by
c.LadingBillNumber,
a.实际派送渠道 ,
a.渠道名称,
a.末端分区,
a.所属客户,
isnull(f.Name,a.州名),
d.Destination,
d.ServiceName,
CONVERT(nvarchar(10),d.LeaveWarehouseTime,120),
CONVERT(nvarchar(10),a.称重时间,120),
convert(nvarchar(10),d.DepartTime,120),
convert(nvarchar(16),d.ArrivalTime,120),
convert(nvarchar(16),d.ClearanceTime,120)936false20[渠道报表基础数据]936false3falsefalseTABLOCK,CHECK_CONSTRAINTS2147483647DataSourceViewIDDataSourceViewIDTableInfoObjectTypeTable]]>