CREATE view  View_Audit_Priority AS SELECT a.audobj_uniqueid, b.attr_after_val from_val, a.attr_after_val to_val, b.change_date from_time, a.change_date to_time FROM  audit_log a,  audit_log b WHERE a.audobj_uniqueid = b.audobj_uniqueid AND a.attr_name = 'priority'
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 = 'priority') UNION SELECT a.audobj_uniqueid, a.attr_after_val, '', a.change_date, null
FROM  audit_log a WHERE a.attr_name = 'priority' AND a.change_date = ( SELECT MAX(change_date) FROM  audit_log b WHERE b.audobj_uniqueid = a.audobj_uniqueid AND b.attr_name = 'priority') 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 = 'priority' AND b.change_date = ( SELECT MIN(change_date)
FROM  audit_log c WHERE c.audobj_uniqueid = a.audobj_uniqueid AND c.attr_name = 'priority') UNION SELECT distinct a.audobj_uniqueid, pri.sym, '', a.change_date, null FROM (( call_req cr inner join  audit_log a ON cr.persid = a.audobj_persid) inner join  pri ON cr.priority = pri.enum) WHERE NOT EXISTS ( SELECT b.audobj_persid FROM  audit_log b WHERE b.attr_name = 'priority' 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, pri.sym, '', a.change_date, null FROM (( chg inner join  audit_log a ON chg.persid = a.audobj_persid) inner join  pri ON chg.priority = pri.enum) WHERE NOT EXISTS ( SELECT b.audobj_persid FROM  audit_log b WHERE b.attr_name = 'priority' 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_Priority] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[View_Audit_Priority] TO [service_desk_ro_group]
GO