尝试一下:

SELECT tp.EMPCODE, tp.DEPARTMENT, d.dte, IFNULL(A.Totalnoofabsentdates , '') Totalnoofabsentdates
FROM test_prefixmaster tp 
JOIN daterange d
LEFT JOIN test_prefixtransactions tpt ON (tp.EMPCODE = tpt.empcode) AND DATE(S_DateTime) = d.dte
LEFT JOIN ( SELECT tp.EMPCODE, d.dte Absentddate, COUNT(tp.EMPCODE) Totalnoofabsentdates
            FROM test_prefixmaster tp
            JOIN daterange1 d
            LEFT JOIN test_prefixtransactions tpt ON (tp.EMPCODE = tpt.empcode) AND DATE(S_DateTime) = d.dte
            WHERE tpt.empcode IS NULL
            GROUP BY tp.EMPCODE) AS A ON tp.EMPCODE = A.EMPCODE AND d.dte = A.Absentddate;