Better SQL statements

Batch Guide data table header

SELECT [waybill number],
[Waybill number] As [order number],
[Waybill number] As [order number],
[Waybill number],
SUM ([Price] * [Productnum]) as [total order],
SUM (CAST (temp.[ Weight] * (CAST (temp. Productnum as DECIMAL (18, 2))
/Temp. Productsumnum) as NUMERIC (2)) as [gross weight],
SUM (CAST (temp.[ Weight] * (CAST (temp. Productnum as DECIMAL (18, 2))
/Temp. Productsumnum) as NUMERIC (18, 2))
-0.01) as [net weight],
Temp2. Truename as [consignee],
Temp2. Adrress as [consignee address],
Case Temp2. Mobile
When "then Temp2. Phone
ELSE Temp2. Mobile
END as [phone],
Temp. Currency,
"As [OrderType],
"As [Trafmode],
"As [Portcode],
"As [Licenseno],
"As [Wraptype],
"As [freight],
"As [Insuredfee],
"As [Freightmark],
"As [Insuredfeemark],
"As [Goodsinfo],
"As [Consigneecountry],
"As [shipper],
"As [shipperaddress],
"As [Shippertelephone],
"As [Shippercountry],
"As [Ordernote],
"As [Logisticsnote],
"As [Inventorynote],
"As [Trafname],
"As [Voyageno],
"As [Billno],
' 1 ' as Packno
From (SELECT a.transportordercode as [waybill number],
MAX (ISNULL (D.catalogname,
SUBSTRING (Productcatalogname,
CHARINDEX ('-', productcatalogname)
+ 1, LEN (productcatalogname))) CatalogName,
D.itemname,
D.itemno,
D.gcode as Hscode,
Case if A.weight < 0.02 then 0.02
ELSE A.weight
END as [Weight],
SUM (C.productnum) as Productnum,
Cast (CAST (SUM (c.totalitemactualprice) * 0.1498 as DECIMAL (11,
2))
/SUM (C.productnum) as DECIMAL (one, 2)) [Price],
' USD ' as [currency],
Dbo. Fun_gettransportproductnum (A.transportorderid) as Productsumnum,
D.unit,
D.unit1
FROM dbo. Twx_transportorder A with (NOLOCK)
Left JOIN dbo. Twx_transportfrommst B with (NOLOCK) on a.transportorderid = B.transportorderid
Left JOIN dbo. Twx_transportfromdtl c with (NOLOCK) on b.transportformmstid = C.transportformmstid
Left JOIN dbo. Tbd_productcatalog_map_kjt d with (NOLOCK) on c.productcatalogid = D.catalogid
and C.deleteuser = 0
WHERE A.statusflag >= 20
and A.logisticstransferdtlid in (
SELECT Logisticstransferdtlid
FROM dbo. Twx_logisticstransferdtl with (NOLOCK)
WHERE Logisticstransferdtlcode in (
SELECT [Logisticstransferdtlcode]
From Twx_scanlogisticstransfer with (NOLOCK)
WHERE deleteuser = 0
and Batchno = ' 113020170828 '))
GROUP by A.transportordercode,
ItemName,
ItemNo,
D.gcode,
A.weight,
D.unit,
A.transportorderid,
D.unit1
) Temp
Left JOIN dbo. Twx_transportorder Temp2 on temp. [waybill number] = Temp2. Transportordercode
GROUP by Temp. [Waybill number],
Temp. [Currency],
Temp2. Truename,
Temp2. Adrress,
Temp2. Mobile,
Temp2. Phone;

-------------------------------------------------------------------.

(SELECT TOP 1 c.createtime from Twx_logisticstransferdtl A
Left JOIN Twx_scanlogisticstransfer C on A.logisticstransferdtlcode=c.logisticstransferdtlcode
WHERE A.logisticstransferdtlid=a.logisticstransferdtlid
) as ' Loading time ',
(
SELECT TOP 1 n.tracktime from
[dbo]. [Log_transportordertrack] N WHERE N.transportordercode=a.transportordercode and N.trackcode in (' cai_customs_clr_suc_00 ', ' cai_customs_clr_suc_ HK ')
As ' arrival time ',
(
SELECT TOP 1 n.tracktime from
[dbo]. [Log_transportordertrack] N WHERE N.transportordercode=a.transportordercode and n.trackcode= ' cai_sign_in '
)
As ' signature time '

Better SQL statements