Date wise Ascending or Descending Order (AND) Order by multiple different columns

Current month lead first then previous months leads and then "follow_up_date_time" null leads:

SELECT `lead_id`, `lead_cre_type`, `lead_cre_date`, `lead_status`, `agmt_no`, `cus_name`, `cus_mobile`, `state`, `ms`.`city_name`, `agmt_no`, `veh_reg_no`, `mfcw_id`, `branch`, `branch_code`, `mmv`, `given_for`, `cc_remark`, `remarks`, `reason`, `assigned_on`, `final_agmt_no`, `follow_up_date_time`, `inprgs_datetime`, `lead_date_time`, `application_no` as `emlap_id`, `mf_lead_id` as `crm_id`, `final_agmt_no` as `contract_number`, `contract_date`, `sub_stage`, `appl_no`, `lead_from`
FROM `custom_leads`
LEFT JOIN `master_cities` as `ms` ON `ms`.`city_id` = `custom_leads`.`city_id`
WHERE `assigned_to` = '4900'
AND `lead_status` = '3'
AND (`follow_up_date_time` IS NULL OR  date_format(substr(follow_up_date_time,1,10),'%Y-%m-%d') <= '2019-01-31' OR   month(date_format(substr(follow_up_date_time,1,10),'%Y-%m-%d')) <= month(CURRENT_DATE())) ORDER BY
date_format(substr(follow_up_date_time,1,10),'%Y-%m-%d')  >= CURRENT_DATE() DESC,
date_format(substr(follow_up_date_time,1,10),'%Y-%m-%d')  ASC,
date_format(substr(follow_up_date_time,1,10),'%Y-%m-%d')  < CURRENT_DATE() DESC

Order by multiple different columns(Set of leads one after another with descending order by date):


SELECT
  `ui`.`uid`,
    `ui`.`user_fname` AS `exec_name`,
    `l`.`mf_lead_id`,
    `ul`.`sap_code`,
    `ui`.`user_mobile` AS `exec_mobile`,
    `l`.`lead_id`,
    DATE_FORMAT(SUBSTR(l.lead_cre_date, 1, 10),
            '%d/%m/%Y') AS lead_cre_date,
    `l`.`lead_cre_type`,
    DATE_FORMAT(SUBSTR(l.last_modified_on, 1, 10),
            '%d/%m/%Y') AS last_modified_on,
     l.lead_category,     
    `l`.`lead_status`,
    `l`.`abm_lead_status`,
    `l`.`cus_name`,
    `l`.`cus_mobile`,
    `l`.`veh_reg_no`,
    `l`.`mfcw_id`,
    `l`.`agmt_no`,
    `l`.`final_agmt_no`,
    `l`.`branch`,
    `l`.`branch_id`,
    `l`.`mmv`,
    `l`.`given_for`,
    `l`.`assigned_to`,
    `l`.`cc_remark`,
    `l`.`remarks`,
    DATE_FORMAT(SUBSTR(l.do_date, 1, 10), '%d/%m/%Y') AS do_date,
    `l`.`abm_cnc_remarks`,
    DATE_FORMAT(SUBSTR(l.follow_up_date_time, 1, 10),
            '%d/%m/%Y') AS follow_up_date_time,
    DATE_FORMAT(SUBSTR(l.inprgs_datetime, 1, 10),
            '%d/%m/%Y') AS inprgs_datetime,
    `C`.`city_name`,
    `l`.`lead_for`,
    `l`.`mmfsl_stage`,
    `l`.`mmfsl_code`,
    `l`.`appl_no`,
    `l`.`final_agmt_no`,
    `l`.`veh_fin_amt`,
    `l`.`emlap_user_id`,
    `l`.`emlap_date_time`,
    (CASE
        WHEN l.lead_from = 'referral-app' THEN T.state_name
        ELSE S.state_name
    END) AS state_name,
    `l`.`application_no`,
    DATE_FORMAT(SUBSTR(l.first_contacted_date, 1, 10),
            '%d/%m/%Y') AS first_contacted_date,
    (CASE
        WHEN A.user_type = '1' THEN 'Super Admin'
        WHEN A.user_type = '3' THEN 'Executive'
        WHEN A.user_type = '5' THEN 'ABM'
        WHEN A.user_type = '16' THEN 'Mini User'
    END) AS user_type,
    `A`.`username` AS `lead_created_username`,
    `l`.`lead_type`,
    (CASE
        WHEN l.workable_month = '1' THEN 'Workable This Month'
        WHEN l.workable_month = '2' THEN 'Workable Next Month'
    END) AS workable_month
FROM
`custom_leads` AS `l`
LEFT JOIN
`user_login` AS `ul` ON `ul`.`id` = `l`.`assigned_to`
LEFT JOIN
`user_login` AS `A` ON `A`.`id` = `l`.`lead_cre_user_id`
LEFT JOIN
`user_info` AS `ui` ON `ui`.`uid` = `ul`.`id`
LEFT JOIN
`master_cities` AS `C` ON `C`.`city_id` = `l`.`city_id`
LEFT JOIN
`master_states` AS `S` ON `S`.`state_id` = `l`.`state_id`
LEFT JOIN
`refertowin_master_states` AS `T` ON `T`.`id` = `l`.`state_id`
WHERE
`l`.`lead_status` != '8'
ORDER BY
   (CASE
      WHEN lead_category = 'CLMS' AND lead_cre_type='Bulk' THEN 0
      WHEN lead_category IS NULL AND lead_cre_type='Bulk' THEN 1
       WHEN lead_from = 'referral-app' AND lead_cre_type='Single' THEN 2
       WHEN lead_from IS NULL AND lead_cre_type='Single' THEN 3
       ELSE 4   
    END),
   date(lead_cre_date) desc