Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.