CREATE view  View_Audit_Status AS SELECT a.audobj_uniqueid, b.attr_after_val as from_val, a.attr_after_val as to_val, b.change_date as from_time, a.change_date as to_time FROM  audit_log a,  audit_log b WHERE a.audobj_uniqueid = b.audobj_uniqueid AND a.attr_name = 'status' AND a.attr_name = b.attr_name AND b.change_date = ( SELECT MAX(change_date) FROM  audit_log c WHERE c.change_date < a.change_date and c.audobj_uniqueid = a.audobj_uniqueid and c.attr_name = 'status') UNION SELECT a.audobj_uniqueid,
a.attr_after_val, '', a.change_date, null FROM  audit_log a WHERE a.attr_name = 'status' AND a.attr_after_val != '' AND a.change_date = ( SELECT MAX(change_date) FROM  audit_log b WHERE b.audobj_uniqueid = a.audobj_uniqueid AND b.attr_name = 'status') UNION SELECT a.audobj_uniqueid, b.attr_before_val, b.attr_after_val, a.change_date, b.change_date FROM  audit_log a,  audit_log b WHERE a.attr_name = '' AND b.attr_name = 'status' AND b.change_date = ( SELECT MIN(change_date)
FROM  audit_log c WHERE c.audobj_uniqueid = a.audobj_uniqueid AND c.attr_name = 'status') UNION SELECT distinct a.audobj_uniqueid, stat.sym, '', a.change_date, null FROM (( call_req cr inner join  audit_log a ON cr.persid = a.audobj_persid) inner join  cr_stat stat ON cr.status = stat.code) WHERE NOT EXISTS ( SELECT b.audobj_persid FROM  audit_log b WHERE b.attr_name = 'status' AND cr.persid = b.audobj_persid) AND a.change_date = ( SELECT MIN(change_date) FROM  audit_log c
WHERE a.audobj_persid = c.audobj_persid) UNION SELECT distinct a.audobj_uniqueid, chgstat.sym, '', a.change_date, null FROM (( chg inner join  audit_log a ON chg.persid = a.audobj_persid) inner join  chgstat ON chg.status = chgstat.code) WHERE NOT EXISTS ( SELECT b.audobj_persid FROM  audit_log b WHERE b.attr_name = 'status' AND chg.persid = b.audobj_persid)
AND a.change_date = ( SELECT MIN(change_date) FROM  audit_log c WHERE a.audobj_persid = c.audobj_persid)
GO
GRANT SELECT ON  [dbo].[View_Audit_Status] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[View_Audit_Status] TO [service_desk_ro_group]
GO