SELECT
CAST(COMP_INFO.GBS_CASEID AS int),
COMP_INFO.NAME,
dbo.GBS_CASEDATES.APPOINTMENTDATE1,
dbo.GBS_CASETYPE.CASETYPEDESC,
APPOINT_PARTNER.OFFICERNAME,
JOINT_PARTNER.OFFICERNAME,
JOB_CONTROLLER.OFFICERNAME,
CASE_ADMINSTRS.OFFICERNAME,
CASE_MANAGER.OFFICERNAME,

case
when APPOINT_PARTNER.TERMINATEDATE <> '01/01/1900 00:00:00' then APPOINT_PARTNER.TERMINATEDATE
end,

case
when JOINT_PARTNER.TERMINATEDATE <> '01/01/1900 00:00:00' then JOINT_PARTNER.TERMINATEDATE
end,

case
when JOB_CONTROLLER.TERMINATEDATE <> '01/01/1900 00:00:00' then JOB_CONTROLLER.TERMINATEDATE
end,

case
when CASE_MANAGER.TERMINATEDATE <> '01/01/1900 00:00:00' then CASE_MANAGER.TERMINATEDATE
end,

case
when CASE_ADMINSTRS.TERMINATEDATE <> '01/01/1900 00:00:00' then CASE_ADMINSTRS.TERMINATEDATE
end,
GROUP_MANAGER.OFFICERNAME,

case
when GROUP_MANAGER.TERMINATEDATE <> '01/01/1900 00:00:00' then GROUP_MANAGER.TERMINATEDATE
end,
CASHIERS.OFFICERNAME,

case
when CASHIERS.TERMINATEDATE <> '01/01/1900 00:00:00' then CASHIERS.TERMINATEDATE
end,
COMP_INFO.GBS_JOBCODE,
ADMIN_OFFICE.OFFICENAME,
dbo.GBS_CASEOFFICERS.OFFICERNAME,

case
when dbo.GBS_CASEDATES.INSOLVENCYDATE <> '1900-01-01 00:00:00' then dbo.GBS_CASEDATES.INSOLVENCYDATE
end,

case
when dbo.GBS_CASEDATES.APPOINTMENTDATE2 <> '1900-01-01 00:00:00' then dbo.GBS_CASEDATES.APPOINTMENTDATE2
end,

case
when dbo.GBS_CASEDATES.APPOINTMENTDATE3 <> '1900-01-01 00:00:00' then dbo.GBS_CASEDATES.APPOINTMENTDATE3
end
FROM dbo.COMPANYINFO COMP_INFO
INNER JOIN dbo.GBS_CASEOFFICERS 
ON COMP_INFO.DATAAREAID = dbo.GBS_CASEOFFICERS.DATAAREAID
LEFT OUTER JOIN dbo.GBS_CASEOFFICERS APPOINT_PARTNER ON COMP_INFO.DATAAREAID = APPOINT_PARTNER.DATAAREAID LEFT OUTER JOIN dbo.GBS_CASEOFFICERS JOB_CONTROLLER ON JOB_CONTROLLER.DATAAREAID = COMP_INFO.DATAAREAID LEFT OUTER JOIN dbo.GBS_CASEOFFICERS CASE_MANAGER ON COMP_INFO.DATAAREAID = CASE_MANAGER.DATAAREAID LEFT OUTER JOIN dbo.GBS_CASEOFFICERS JOINT_PARTNER ON COMP_INFO.DATAAREAID = JOINT_PARTNER.DATAAREAID LEFT OUTER JOIN dbo.GBS_CASEOFFICERS CASE_ADMINSTRS ON CASE_ADMINSTRS.DATAAREAID = COMP_INFO.DATAAREAID LEFT OUTER JOIN dbo.GBS_CASEOFFICERS GROUP_MANAGER ON COMP_INFO.DATAAREAID = GROUP_MANAGER.DATAAREAID LEFT OUTER JOIN dbo.GBS_CASEOFFICERS CASHIERS ON COMP_INFO.DATAAREAID = CASHIERS.DATAAREAID ,
dbo.PWCASPCLIENTTABLE,
dbo.GBS_CASEDATES,
dbo.COMPANYINFO COMP_INFO,
dbo.GBS_CASETYPE,
dbo.GBS_PWCADDRESSES ADMIN_OFFICE 
WHERE	 (APPOINT_PARTNER.TASKID = 'APP')
AND	(CASE_MANAGER.TASKID = 'CM')
AND	(JOB_CONTROLLER.TASKID = 'JC')
AND	(JOINT_PARTNER.TASKID = 'JT APP')
AND	(CASE_ADMINSTRS.TASKID = 'ADMIN')
AND	(GROUP_MANAGER.TASKID = 'GM')
AND	(CASHIERS.TASKID = 'CFM')
AND	(
case
when APPOINT_PARTNER.TERMINATEDATE <> '01/01/1900 00:00:00' then APPOINT_PARTNER.TERMINATEDATE
end Is Null
AND	
case
when JOINT_PARTNER.TERMINATEDATE <> '01/01/1900 00:00:00' then JOINT_PARTNER.TERMINATEDATE
end Is Null
AND	
case
when JOB_CONTROLLER.TERMINATEDATE <> '01/01/1900 00:00:00' then JOB_CONTROLLER.TERMINATEDATE
end Is Null
AND	
case
when CASE_MANAGER.TERMINATEDATE <> '01/01/1900 00:00:00' then CASE_MANAGER.TERMINATEDATE
end Is Null
AND	
case
when CASE_ADMINSTRS.TERMINATEDATE <> '01/01/1900 00:00:00' then CASE_ADMINSTRS.TERMINATEDATE
end Is Null
AND	
case
when GROUP_MANAGER.TERMINATEDATE <> '01/01/1900 00:00:00' then GROUP_MANAGER.TERMINATEDATE
end Is Null
AND	
case
when CASHIERS.TERMINATEDATE <> '01/01/1900 00:00:00' then CASHIERS.TERMINATEDATE
end Is Null
AND	
case
when dbo.GBS_CASEOFFICERS.TERMINATEDATE <> '01/01/1900 00:00:00' then dbo.GBS_CASEOFFICERS.TERMINATEDATE
end Is Null
AND	
case dbo.PWCASPCLIENTTABLE.STATUS 
when 1 then 'Operating'
when 2 then 'Suspended'
when 3 then 'Suspended'
else 'Evaluating'
end = 'Operating')
AND	COMP_INFO.GBS_JURISDICTIONGROUP = dbo.GBS_CASETYPE.JURISDICTIONGROUP
AND	COMP_INFO.GBS_CASETYPEID = dbo.GBS_CASETYPE.CASETYPEID
AND	COMP_INFO.DATAAREAID = dbo.GBS_CASEDATES.DATAAREAID
AND	COMP_INFO.GBS_CASEID = dbo.PWCASPCLIENTTABLE.ID