SELECT 'select'batch_no, '0'b_no, '5000-01-01'next_dt
UNION ALL SELECT if( next_dt IS NULL , concat( skey, '-', cast( substr( a.fil_no, 6, 5 ) AS UNSIGNED ) , '-', substr( a.fil_no, 11, 4 ) , '_', batch_no ) , concat( skey, '-', cast( substr( a.fil_no, 6, 5 ) AS UNSIGNED ) , '-', substr( a.fil_no, 11, 4 ) , '_', batch_no, '_', date_format( next_dt, '%d-%m-%Y' ) ) ) batch_no, concat( batch_no, '_', a.fil_no ) b_no, next_dt
FROM mp_indexing_batch a
JOIN casetype b ON substr( a.fil_no, 3, 3 ) = b.casecode
JOIN demoas.users u ON u.usercode = a.scaned_by
AND u.display = 'Y'
AND (
pa_ps = 'WSCAN'
)
AND u.branch =2
LEFT JOIN demoas.heardt ht ON a.fil_no = ht.fil_no
AND clno !=0
WHERE clean_file_dt = '0000-00-00 00:00:00'
AND scaned_by !=0
AND (
cleaned_by =0
OR cleaned_by =728
)
AND b.display = 'Y'
AND a.branch = '2'
ORDER BY CASE WHEN next_dt = ''
THEN 0
ELSE next_dt
END DESC