SELECT
Complain_code ' complaint number ',
Order_no ' order number ',
RESPONSIBILITY ' responsibility Unit name ',
Create_time ' creation time ',
Complain_type ' initiating work order type ',
Duty_complain_type ' liability complaint type ',
Complain_sound_type ' Sound classification ',
Complain_date ' complaint date ',
MEMO ' case Description ',
HANDLER1 " Initiator number ",
HANDLER2 " End artificial number ",
Handler_date1 " complaint start time ",
Handler_date2 " complaint end Time ",
Handler_type ' processing type ',
Eoor_shipper_contact ' receiving contact ',
Eoor_shipper_city_name ' shipping address City ',
Eoor_consignee_city_name ' delivery address city ',
Eoor_order_type ' order type ',
Send_date ' delivery time ',
Eoor_service_mode ' service type ',
Eoos_supplier_full_name1 ' supplier full name ',
Eots_link_type ' landing match '
From
(
SELECT
T.complain_code,
T.order_no,
T.responsibility,
T.create_time,
Case T.complain_type
When the ' SAFETY ' then
' Security Class '
When the ' CUSTOMER ' then
' Customer class '
When the ' ageing ' then
' Aging Class '
When the ' ACTION ' then
' Customer Experience Class '
When the ' DELIVERY ' then
' Distribution Class '
ELSE
' forwarder ' business line-up '
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 ' I ' then ' receiving goods '
When ' I ' Then ' a single '
When ' all ' then ' shipped '
When ' the ' then ' installed '
When ' the ' then ' Shipper's list '
When ' I ' Then ' pick up order '
When ' I ' Then ' freight forwarding sign '
When ' I ' Then ' landed with the handover '
When ' a ' and ' then ' on the ground allocation '
ELSE
' landed with sign '
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
SQL Fetch Data