SELECT distinct ShippingOrder.ShippingOrderNo, ShippingOrder.BLNo,--ContainerBill.ContainerBillNo, -- (CASE ShippingOrder.ShippingOrderType WHEN 2 THEN ShippingOrder.MasterNo WHEN -- 3 THEN ShippingOrder.MasterNo ELSE ShippingOrder.DrNo END) AS DRNo, case when ShippingOrder.DrNo is null then Shippingorder.BLNO else Shippingorder.DrNo end AS DRNo, ShippingOrder.StockInNo, ShippingOrder.OutStockInNo, ShippingOrder.ConFrmNo, CustomBill.NewStatus, DBO.GET_STR(a.ObjectNameE) AS ObjectNameE, Port.PortNameC, (CASE ShippingOrder.ShippingOrderType WHEN 2 THEN ShippingOrder.VslVoyageNo WHEN 3 THEN ShippingOrder.VslVoyageNo ELSE Vslvoyage.VslvoyageName END) AS vessel, SubShippingOrder.Mark, Shippingorder.CUSTOMER_CODE, SubShippingOrder.BLExporter, --case when ShippingOrder.ConsineeNo is not null and Consignee.ISSTOP=0 and Consignee.IsDisabled=0 then ShippingOrder.ConsineeNo else '' end as ConsineeNo, case when ShippingOrder.ConsineeNo is not null and Consignee.ISSTOP=0 and Consignee.IsDisabled=0 then ShippingOrder.ConsineeNo -- else '' when ShippingOrder.ConsineeNo is not null and zilanhuo.ISSTOP=0 and zilanhuo.IsDisabled=0 then ShippingOrder.ConsineeNo else '' end as ConsineeNo, --如果是自揽货就读大框子数据20160930 --case when ShippingOrder.CARGO_TYPE=0 then Shippingorder.CONSIGNEE_ADDRESS else Consignee.NameE end as Consignee, --自揽货又改了，托单上添加自揽货收货人数据了，2016.12.2 case when ShippingOrder.CARGO_TYPE=0 then zilanhuo.NameE else Consignee.NameE end as Consignee, -- CASE WHEN (SubShippingOrder.Consignee like '%TO ORDER%' or SubShippingOrder.Consignee like '%TO SHIPPERS ORDER%' or SubShippingOrder.Consignee like '%TO THE ORDER%' ) then SubShippingOrder.Notifier else SubShippingOrder.Consignee end as Consignee, ShippingOrder.DischargePortName AS DischargePort, (CASE ShippingOrder.ShippingOrderType WHEN 2 THEN Port.PortNameC WHEN 3 THEN Port.PortNameC ELSE ShippingOrder.DeliveryPortName END) AS DestPort, replace(CONVERT(varchar(100), ShippingOrder.ShippingDate, 23),'1900-01-01','') AS ShippingDate, ShippingOrder.SubQuantity, ShippingOrder.Status, ShippingOrder.ShippingOrderType, ShippingOrder.AgentNo, ShippingOrder.RAgentno, -- ShippingOrder.AppLiedETD, -- (CASE shippingorder.customstatus WHEN - 1 THEN '' WHEN 0 THEN 'QingGuan' WHEN -- 1 THEN 'ZhuanGuan' END) AS CustomStatus, -- CustomBill.Status AS CustomBillStatus,-- ShippingOrder.OrderFlag, -- ShippingOrder.GetBook, ShippingOrder.BlBookFlag, ShippingOrder.TransModeNo, TransMode.TransModeName, SubShippingOrder.Destination, replace(CONVERT(varchar(100), ShippingOrder.ArriveDate, 101),'01/01/1900','') AS ArriveDate, -- ShippingOrder.HBNo, PayMode.PayModeName, -- (CASE ShippingOrder.ShippingOrderType WHEN 2 THEN Port.PortNameC WHEN 3 THEN -- Port.PortNameC ELSE SubShippingOrder.DeliveryPort END) AS DestinationCountry, case when ShippingOrder.ETA is null then '' else replace(CONVERT(varchar(100),ShippingOrder.ETA, 23),'1900-01-01','') end AS ETA, SubShippingOrder.LoadPortName, b.E_Mail, b.ObjectNameE AS ADV,SubShippingOrder.CargoName,Vslvoyage.VslvoyageNo,--ShippingOrder.masterdrno, ISNULL(ShippingOrder.MasterExNo, N'') AS MasterExNo , --ShippingOrder.BLNo, shipContainer.ContainerNo, case when shipContainer.ConQuantity=0 then ShippingOrder.Quantity else shipContainer.ConQuantity end as ConQuantity , case when shipContainer.ConWeight=0 then ShippingOrder.Weight else shipContainer.ConWeight end as ConWeight, case when shipContainer.ConVolumn=0 then ShippingOrder.Volumn else shipContainer.ConVolumn end as ConVolumn, --shipContainer.ConDspt, replace(Shippingorder.GOODS_DESC,char(13),',') as ConDspt, shipContainer.ContainerNo as sContainerBillNo, sum(ShippingOrder.Quantity) as TotalQty,ShipContainerDetail.PackageNo, sum(ShippingOrder.Weight) as TotalWeight,sum(ShippingOrder.Volumn) as TotalVolumn,--SubShippingOrder.CargoName, --ContainerType.ContainerTypeNo, --ContainerType.ContainerTypeName, ShipContainer.ConTypeNo, shipContainer.SealNo,SubShippingOrder.Notifier, --ShipContainerDetail.HSCode, dbo.FN_SplitHSCODEB((select distinct a.shipcontainerno from shipcontainerdetail a where a.shipcontainerno=ShipContainer.shipcontainerno)) as HSCode, dbo.f_strs(ShippingOrderPO.ShippingOrderNo) as PO, ShippingOrder.Quantity,ShippingOrder.Weight,ShippingOrder.Volumn, case when SubShippingOrder.spNum1=0 then 'N' ELSE 'Y' END AS ReleaseNo,--是否电放2014.3.21 case when ShippingOrder.PackingDate is null then '' else replace(CONVERT(varchar(100), ShippingOrder.PackingDate,23),'1900-01-01','') end as PackingDate,--装箱日期 ShippingOrder.MBLNO,c.ObjectNameE as Carrier,ShippingOrder.CarrierNo, --2014.6.13 SubShippingOrder.CargoSource, case when SubShippingOrder.CargoSourceDate is null then '' else REPLACE(CONVERT(varchar(100),SubShippingOrder.CargoSourceDate, 23),'1900-01-01','') end as CargoSourceDate, Shipcontainer.LoadConPlace, CASE WHEN Shipcontainer.LoadConDate IS NULL THEN '' ELSE REPLACE(CONVERT(varchar(100),Shipcontainer.LoadConDate, 23),'1900-01-01','') END as LoadConDate, CONVERT(VARCHAR(100),ShippingOrder.MakingDate,23) AS MakingDate,IsRepeatUploadForDT --agent.Email FROM ShippingOrder with (nolock) LEFT OUTER JOIN SubShippingOrder with (nolock) ON ShippingOrder.ShippingOrderNo = SubShippingOrder.ShippingOrderNo --LEFT JOIN ContainerDetail ON ContainerDetail.ShippingOrderNo = ShippingOrder.ShippingOrderNo --LEFT JOIN ContainerBill ON ContainerBill.ContainerBillNo = ContainerDetail.ContainerBillNo LEFT OUTER JOIN Vslvoyage with (nolock) ON ShippingOrder.VslVoyageNo = Vslvoyage.VslvoyageNo LEFT JOIN Vessle with (nolock) ON Vslvoyage.VslvoyageNo=Vessle.VessleNo LEFT OUTER JOIN CustomBill with (nolock) ON ShippingOrder.ShippingOrderNo = CustomBill.ShippingOrderNo LEFT OUTER JOIN Port with (nolock) ON ShippingOrder.DeliveryPortNo = Port.PortNo LEFT OUTER JOIN TransMode with (nolock) ON ShippingOrder.TransModeNo = TransMode.TransModeNo LEFT OUTER JOIN PayMode with (nolock) ON ShippingOrder.PayModeNo = PayMode.PayModeNo LEFT OUTER JOIN ShipContainer with (nolock) ON ShippingOrder.ShippingOrderNo = ShipContainer.ShippingOrderNo LEFT JOIN OperateObject a with (nolock) ON a.ObjectNo = Vessle.CarrierNo LEFT OUTER JOIN OperateObject AS b with (nolock)ON ShippingOrder.AgentNo = b.ObjectNo --LEFT JOIN ContainerType with (nolock) ON shipContainer.ConTypeNo=ContainerType.ContainerTypeNo LEFT OUTER JOIN ShipContainerDetail with (nolock) ON ShipContainer.ShipContainerNo = ShipContainerDetail.ShipContainerNo left JOIN ShippingOrderPO with (nolock) ON ShippingOrderPO.ShippingOrderNo = ShippingOrder.ShippingOrderNo LEFT JOIN OperateObject c with (nolock) ON ShippingOrder.CarrierNo=c.ObjectNo LEFT JOIN Consignee ON ShippingOrder.ConsineeNo=Consignee.ObjectNo and Consignee.Is_ZhiDingHuo=1--指定货 LEFT JOIN Consignee zilanhuo ON ShippingOrder.ConsineeNo=zilanhuo.ObjectNo and zilanhuo.Is_ZhiDingHuo=0--自揽货 --LEFT JOIN ConsigneeApp_Agent agent on ShippingOrder.agentno=agent.AgentNo WHERE (ShippingOrder.AgentNo IN ('DAVIES01', 'DAVIES09', 'DAVIES02', 'DAVIES03', 'DAVIES04', 'DAVIES05', 'DAVIES06', 'DAVIES07', 'DAVIES08', 'DAVIES10', 'DAVIES11', 'DAVIES12', 'DAVIES13', 'DAVIES14', 'DAVIES15', 'DAVIES16', 'DAVIES17', 'DAVIES18', 'DAVIES19', 'DAVIES20', 'DAVIES21', 'DAVIES22', 'DAVIES23', 'DAVIES24', 'DAVIES25', 'DAVIES26') OR ShippingOrder.RAgentno in ('DAVIES01','DAVIES09','DAVIES02','DAVIES03','DAVIES04', 'DAVIES05','DAVIES06', 'DAVIES07','DAVIES08', 'DAVIES10', 'DAVIES11','DAVIES12','DAVIES13','DAVIES14', 'DAVIES15','DAVIES16','DAVIES17', 'DAVIES18','DAVIES19', 'DAVIES20','DAVIES21', 'DAVIES22', 'DAVIES23', 'DAVIES24','DAVIES25', 'DAVIES26') ) --暂时去掉20150806 --AND ShippingOrder.ShippingDate>='2017-06-25' --AND ShippingOrder.ShippingDate<='2017-07-07' --and Shippingorder.ShippingDate<='2017-06-23'--> --AND ShippingOrder.ShippingDate= CONVERT(varchar(100), dateadd(d,-2,getdate()), 23)--2016.12.23改为ETD+2发送文件，即发送前两天的数据--CONVERT(varchar(100), GETDATE(), 101)--ETD=当天-->='2016-09-28' and ShippingOrder.ShippingDate<='2016-10-28'-- --AND ShippingOrder.Status<>5 --不包含退关 --AND (ShipContainer.ContainerNo is not null and ShipContainer.ContainerNo<>'') --去掉没有箱号的 --and (isnull(@ContainerNo,'')='' or ShipContainer.ContainerNo=@ContainerNo) --and ShipContainer.IsUploaded = 0 --未上传 DT要求只上传一次文件，有修改了也不要重新上传20170107 --and ShippingOrder.Status not in (0,5) --正常出运状态2015.1.13 2016.10.14排除0,5，其他都发 --and Consignee.ISSTOP=0 --CNEE未锁定 2016.4.14 --and Consignee.IsDisabled=0 --AND ShippingOrder.shippingorderno='EX28160902471' and shipcontainer.containerno='CAIU9601848' --and shipcontainer.containerno in ('TLLU4318320','CMAU1437920','TLLU4318146') --BMOU4340700 --ECMU8149220 --CMAU0708519 --APHU6408320 --APZU3358764 --GESU5596767 GROUP BY ShippingOrder.ShippingOrderNo, ShippingOrder.BLNo, -- ShippingOrder.ShippingOrderType, ShippingOrder.MasterNo, ShippingOrder.DRNo,ShippingOrder.ShippingDate, ShippingOrder.Quantity, ShippingOrder.Weight, ShippingOrder.Volumn, ShippingOrder.SubQuantity, ShippingOrder.Status, ShippingOrder.ShippingOrderType, ShippingOrder.AgentNo, -- ShippingOrder.AppLiedETD, --ShippingOrder.CustomStatus, CustomBill.Status, -- ShippingOrder.OrderFlag, ShippingOrder.GetBook, ShippingOrder.BlBookFlag, ShippingOrder.MasterExNo, ShippingOrder.TransModeNo, TransMode.TransModeName, SubShippingOrder.Destination, ShippingOrder.ArriveDate, ShippingOrder.StockInNo, ShippingOrder.OutStockInNo, ShippingOrder.ConFrmNo, CustomBill.NewStatus, a.ObjectNameE, ShippingOrder.VslVoyageNo, Vslvoyage.VslvoyageName, SubShippingOrder.Mark, SubShippingOrder.BLExporter, SubShippingOrder.Consignee, ShippingOrder.DischargePortName, Port.PortNameC, ShippingOrder.DeliveryPortName, PayMode.PayModeName, ShippingOrder.ETA, SubShippingOrder.LoadPortName, b.E_Mail, b.ObjectNameE,SubShippingOrder.CargoName,a.ObjectNameE,Vslvoyage.VslvoyageNo, --ShippingOrder.masterdrno, shipContainer.ContainerNo,ShipContainer.ShipContainerNo, shipContainer.ConQuantity,shipContainer.ConWeight, shipContainer.ConVolumn,Shippingorder.GOODS_DESC,shipContainer.ContainerNo, --SubShippingOrder.CargoName, --ContainerType.ContainerTypeNo, ShipContainer.ConTypeNo, ShipContainerDetail.PackageNo, --ContainerType.ContainerTypeName, shipContainer.SealNo,SubShippingOrder.Notifier,ShipContainerDetail.HSCode, --ContainerBill.ContainerBillNo ShippingOrderPO.PO,Consignee.NameE,zilanhuo.NameE, --ShippingOrder.ShippingOrderType, --ContainerBill.ContainerBillNo, ShippingOrder.Quantity,ShippingOrder.Weight,ShippingOrder.Volumn,ShippingOrder.RAgentno,SubShippingOrder.spNum1, ShippingOrder.PackingDate,ShippingOrder.MBLNO,ShippingOrder.ConsineeNo,c.ObjectNameE ,ShippingOrder.CarrierNo, ShippingOrderPO.ShippingOrderNo, SubShippingOrder.CargoSource,SubShippingOrder.CargoSourceDate, ShippingOrder.CARGO_TYPE,ShippingOrder.CONSIGNEE_ADDRESS, Shipcontainer.LoadConPlace,Shipcontainer.LoadConDate,ShippingOrder.MakingDate,IsRepeatUploadForDT--,agent.Email ,Consignee.ISSTOP,Consignee.IsDisabled, Shippingorder.CUSTOMER_CODE,zilanhuo.ISSTOP,zilanhuo.IsDisabled ORDER BY shipContainer.ContainerNo desc--shippingorder.shippingdate desc