app form is the value in one of the fields
what about running only this
SELECT isnull(d.lk_value, 'No District Captured') AS District, isnull(sd.lk_value, 'No Subdistrict Captured') as subdistrict, COUNT(*) AS 'Total Cancel', a.lk_value AS 'Reason', 382 AS 'document_type'
FROM tfs_reg_application ap
Left JOIN apl_generic_lookup d on d.lk_id = ap.district_code
Left JOIN tfs_town tn on ap.town_id = tn.town_id
Left JOIN apl_generic_lookup sd on tn.subdistrict_id = sd.lk_value
INNER JOIN dbo.tfs_reg_audit_log l ON ap.folder_id = l.folder_id
Left JOIN dbo.apl_generic_lookup a ON l.action_reason = a.lk_id
WHERE (ap.appl_cancelled = 1) AND (l.current_item = 'Y')
GROUP BY a.lk_value, d.lk_value, sd.lk_value
and then only this
select isnull(gl.lk_value, 'No District Captured') as District, isnull(b.lk_value, 'No Subdistrict Captured') as subdistrict, count(*) as 'Total Cancel', a.lk_value as 'Reason', 384 as 'document_type'
from tfs_wf_lc lc
Left JOIN apl_generic_lookup gl on gl.lk_id = lc.district
Left JOIN apl_generic_lookup b on b.lk_id = lc.subdistrict
Inner Join tfs_wf_lc_audit_log la on lc.lc_id = la.lc_id
LEFT Join apl_generic_lookup a on la.cancel_reason = a.lk_id
Where lc_cancelled = 1
and la.current_item = 'Y'
Group By a.lk_value, gl.lk_value, b.lk_value
If that doesn't give an error then that is your problem
If you have an error in one of these 2 then take a look at your joins maybe you are joining different datatypes
“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" -- http://sqlservercode.blogspot.com/