select rr.* from (SELECT zz. * , bb.ssss
FROM (

SELECT a.usercode, a.branch, cl_date, filno, court, uid, ''fday, ''lday, SUM( total ) total_a, SUM( total_upload ) total_upload_a, GROUP_CONCAT( g_s ) g_s, SUM( sss ) sss, SUBSTRING_INDEX( court, ',', 1 ) senior_jcode
FROM (

SELECT a.usercode, a.branch, cl_date, filno, court, uid, ''fday, ''lday, COUNT( cl_date ) total, SUM(
CASE WHEN pdfname IS NULL
THEN 0
ELSE 1
END ) total_upload, GROUP_CONCAT( c.pdfname ) g_s, ''sss
FROM jo_alottment_paps a
LEFT JOIN ordernet c ON a.filno = c.fil_no
AND cl_date = orderdate
AND a.branch = c.branch
WHERE cl_date
BETWEEN '2020-03-20'
AND '2022-08-18'
AND a.display = 'Y'
AND SUBSTR(
a.filno, 1, 2
) = '01'
AND (
a.mainhead = 'M'
OR a.mainhead = 'L'
)
AND a.branch =1
AND (
(
pdfname IS NULL
AND fil_no IS NULL
)
OR (
fil_no IS NOT NULL
AND orderdate IS NOT NULL
AND c.usercode = a.usercode
)
)
GROUP BY a.court
UNION ALL SELECT a. * , COUNT( cl_date ) total, SUM(
CASE WHEN pdfname IS NULL
THEN 0
ELSE 1
END ) total_upload, GROUP_CONCAT( c.pdfname ) g_s, ''sss
FROM (

SELECT DISTINCT usercode, branch, cl_date, filno, court, uid, DATE_ADD( cl_date, INTERVAL( - WEEKDAY( cl_date ) )
DAY ) fday, DATE_ADD( DATE_ADD( cl_date, INTERVAL( - WEEKDAY( cl_date ) )
DAY ) , INTERVAL 5
DAY ) lday
FROM jo_alottment_paps a
WHERE cl_date
BETWEEN '2020-03-20'
AND '2022-08-18'
AND display = 'Y'
AND SUBSTR(
a.filno, 1, 2
) = '01'
AND mainhead = 'F'
AND a.branch =1
)a
LEFT JOIN ordernet c ON a.filno = c.fil_no
AND a.branch = c.branch
AND a.usercode = c.usercode
AND orderdate
BETWEEN fday
AND lday
WHERE (
(
pdfname IS NULL
AND fil_no IS NULL
)
OR (
fil_no IS NOT NULL
AND orderdate IS NOT NULL
AND c.usercode = a.usercode
)
)
GROUP BY a.court
UNION ALL SELECT o.usercode, o.branch, cl_date, o.fil_no, court, ''uid, ''fday, ''lday, ''total, ''total_upload, ''g_s, COUNT( DISTINCT o.fil_no, o.type, o.orderdate ) sss
FROM jo_alottment_paps a
JOIN ordernet o ON a.filno = o.fil_no
AND a.branch = o.branch
AND a.usercode = o.usercode
WHERE a.display = 'Y'
AND (
(
pdf_generated_date = '0000-00-00 00:00:00'
AND pdfname != ''
AND pdfname IS NOT NULL
AND prnt_dt = '0000-00-00 00:00:00'
)
OR (
pdf_generated_date != '0000-00-00 00:00:00'
AND pdfname != ''
AND pdfname IS NOT NULL
)
)
AND (
cl_date
BETWEEN '2020-03-20'
AND '2022-08-18'
)
AND SUBSTR(
o.fil_no, 1, 2
) = '01'
AND IF (
(
mainhead = 'M'
OR mainhead = 'L'
), (
orderdate = cl_date
), (
orderdate
BETWEEN DATE_ADD( cl_date, INTERVAL( - WEEKDAY( cl_date ) )
DAY )
AND DATE_ADD( DATE_ADD( cl_date, INTERVAL( - WEEKDAY( cl_date ) )
DAY ) , INTERVAL 5
DAY )
)
)
GROUP BY a.court
)a
GROUP BY court
ORDER BY (
SUM( total ) - SUM( total_upload )
) DESC
)zz
LEFT JOIN (

SELECT COUNT( aa.filno ) ssss, aa.court
FROM jo_alottment_paps aa
LEFT JOIN ordernet o ON aa.filno = o.fil_no
AND aa.branch = o.branch
AND IF (
(
mainhead = 'M'
OR mainhead = 'L'
), (
orderdate = cl_date
), (
orderdate
BETWEEN DATE_ADD( cl_date, INTERVAL( - WEEKDAY( cl_date ) )
DAY )
AND DATE_ADD( DATE_ADD( cl_date, INTERVAL( - WEEKDAY( cl_date ) )
DAY ) , INTERVAL 5
DAY )
AND o.orderdate NOT
IN (

SELECT cl_date
FROM jo_alottment_paps zz
WHERE zz.cl_date >= DATE_ADD( zz.cl_date, INTERVAL( - WEEKDAY( zz.cl_date ) )
DAY )
AND zz.cl_date <= DATE_ADD( DATE_ADD( zz.cl_date, INTERVAL( - WEEKDAY( zz.cl_date ) )
DAY ) , INTERVAL 5
DAY )
AND zz.filno = aa.filno
AND zz.display = 'Y'
AND o.orderdate != aa.cl_date
)
)
)
AND SUBSTR(
o.fil_no, 1, 2
) = '01'
WHERE aa.display = 'Y'
AND cl_date >= '2020-03-20'
AND cl_date <= '2022-08-18'
AND SUBSTR(
aa.filno, 1, 2
) = '01'
AND (
(
o.fil_no IS NULL
AND o.branch IS NULL
AND o.usercode IS NULL
AND o.orderdate IS NULL
)
OR (
o.fil_no IS NOT NULL
AND o.orderdate IS NOT NULL
AND o.ent_dt = '0000-00-00 00:00:00'
AND o.usercode = aa.usercode
)
)
GROUP BY aa.court
)bb ON bb.court = zz.court
JOIN judge j_d ON j_d.jcode = zz.senior_jcode
AND j_d.branch = zz.branch
AND j_d.display = 'Y'
ORDER BY jsen) rr join (SELECT * ,
CASE WHEN judge2_id =0
THEN judge1_id
WHEN (
judge2_id !=0
)
THEN concat( judge1_id, ',', judge2_id )
WHEN judge3_id !=0
THEN concat( judge1_id, ',', judge2_id, ',', judge3_id )
END gg
FROM `roster`
WHERE (
to_date = '0000-00-00'
OR (
to_date >= ( DATE_ADD( CURDATE( ) , INTERVAL - WEEKDAY( CURDATE( ) )
DAY ) )
AND to_date <= DATE_ADD( DATE_ADD( CURDATE( ) , INTERVAL - WEEKDAY( CURDATE( ) )
DAY ) , INTERVAL 5
DAY )
)
)
AND display = 'Y'
AND branch =1) tt on rr.cl_date<=tt.from_date and rr.court=tt.gg