About

A sample image of this SQL report run in the Database Viewer is shown below:

Caveats

None.

Code

To highlight and copy the code below to your clipboard, simply click the Copy button.

Copy

select a.*, status_pat from (
select patno, cat_name as task_status, department_name, task_name, date_created, staffname,  end_time, completed_by_staffid
from check_list_item cli
inner join check_list_entry cle on cle.cli_id = cli.cli_id
inner join check_list_cases clc on clc.case_id = cle.case_id
inner join staff1 on staff1.staffid = cle.created_by_staffid
left outer join (select * from op_categories where cat_group_id = 10) o on cle.status = o.cat_id
left outer join (select id, department_name from department) d on d.id = cli.department_id
) a
inner join register on register.patno = a.patno
where status_pat <> 'ACTIVE'
and task_status not in ( 'Completed','Canceled')