SQL Fetch Data

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 party 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

' Goods receipt '

When ' all ' then

' Matching order '

When ' all ' then

Delivery

When ' the ' then

Installed

When ' Wuyi ' then

' Shipper's list '

When ' I ' Then

' Pick up order '

When ' I ' Then

' Freight Forwarding sign '

When ' I ' Then

' Landing match handover '

When ' the '

' Allocation of ground distribution '

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 < Date_add (Curdate (), INTERVAL 0 day)

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 > Date_sub (curdate (), INTERVAL Day)

GROUP by

T.complain_code

) as B on a.complain_code = B.complain_code2

ORDER by

Complain_code DESC

SQL Fetch Data