SQL acquires data
SELECT
COMPLAIN_CODE 'complaint order number'
ORDER_NO 'order number'
RESPONSIBILITY 'name of responsible unit'
CREATE_TIME 'creation time'
COMPLAIN_TYPE 'initiating work order type'
DUTY_COMPLAIN_TYPE 'type of complaint to be held accountable'
COMPLAIN_SOUND_TYPE 'sound classification'
COMPLAIN_DATE 'date of complaint'
MEMO 'fact sheet'
HANDLER1 "initiating manual number"
HANDLER2 "ending manual number"
HANDLER_DATE1 "complaint start time"
HANDLER_DATE2 "complaint end time"
HANDLER_TYPE 'processing type'
EOOR_SHIPPER_CONTACT 'ship to contact'
EOOR_SHIPPER_CITY_NAME 'Shipping address City'
EOOR_CONSIGNEE_CITY_NAME 'receiving address City'
EOOR_ORDER_TYPE 'order Type'
SEND_DATE 'delivery time'
EOOR_SERVICE_MODE 'Service Type'
Full name of EOOS_SUPPLIER_FULL_NAME1' supplier'
EOTS_LINK_TYPE 'match on arrival'
FROM
(
SELECT
T.COMPLAIN_CODE
T.ORDER_NO
T.RESPONSIBILITY
T.CREATE_TIME
CASE t.COMPLAIN_TYPE
WHEN 'SAFETY' THEN
'Security class'
WHEN 'CUSTOMER' THEN
'customer category'
WHEN 'AGEING' THEN
'limitation category'
WHEN 'ACTION' THEN
'customer experience category'
WHEN 'DELIVERY' THEN
'Distribution category'
ELSE
'Freight forwarder business online'
END 'COMPLAIN_TYPE'
T.COMPLAIN_SOUND_TYPE
T.COMPLAIN_DATE
T.MEMO
T.DUTY_COMPLAIN_TYPE
B. HANDLER 'HANDLER1'
B.HANDLER_TYPE
C.EOOR_SHIPPER_CONTACT
C.EOOR_SHIPPER_CITY_NAME
C.EOOR_CONSIGNEE_CITY_NAME
C.EOOR_ORDER_TYPE
C.SEND_DATE
C.EOOR_SERVICE_MODE
D.EOOS_SUPPLIER_FULL_NAME 'EOOS_SUPPLIER_FULL_NAME1'
CASE e.EOTS_LINK_TYPE
When '21' THEN' receiving'
When '22' THEN' matching order'
When '23' THEN' delivery'
When '24' THEN' installed'
When '51' THEN' pick-up Master Bill'
When '61' THEN' pick up order'
When '62' THEN' forwarder sign for receipt'
When '71' THEN' landing mating and handover'
When '72' THEN' Distribution on arrival'
ELSE
'Visa on arrival'
END 'EOTS_LINK_TYPE'
Min (b.HANDLER_DATE) 'HANDLER_DATE1'
FROM
Cs_complained_info t
LEFT JOIN cs_complained_handle_record b ON b. CODE = t.COMPLAIN_CODE
LEFT JOIN eo_order c ON c.EOOR_ORDER_NO = t.ORDER_NO
LEFT JOIN eo_order_supplier d ON d.EOOS_ORDER_NO = t.ORDER_NO
LEFT JOIN eo_order_task_status e ON e.EOOR_ORDER_NO = t.ORDER_NO
WHERE
T.HANDLE_STATUS = "DONE"
AND T.ORDER_NO! = ""
AND t.CREATE_TIME > "2017-1-1"
GROUP BY
T.COMPLAIN_CODE
) AS A
LEFT JOIN (
SELECT
T.COMPLAIN_CODE 'COMPLAIN_CODE2'
B. HANDLER 'HANDLER2'
Max (b.HANDLER_DATE) 'HANDLER_DATE2'
FROM
Cs_complained_info t
LEFT JOIN cs_complained_handle_record b ON b. CODE = t.COMPLAIN_CODE
LEFT JOIN eo_order c ON c.EOOR_ORDER_NO = t.ORDER_NO
LEFT JOIN eo_order_supplier d ON d.EOOS_ORDER_NO = t.ORDER_NO
LEFT JOIN eo_order_task_status e ON e.EOOR_ORDER_NO = t.ORDER_NO
WHERE
T.HANDLE_STATUS = "DONE"
AND b.HANDLER_RESULT = "FINISH"
AND T.ORDER_NO! = ""
AND t.CREATE_TIME > "2017-1-1"
GROUP BY
T.COMPLAIN_CODE
) AS B ON A.COMPLAIN_CODE = B.COMPLAIN_CODE2
ORDER BY COMPLAIN_CODE DESC
