CREATE view  View_Audit_Group 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 = 'group' 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 = 'group') UNION SELECT a.audobj_uniqueid, a.attr_after_val, '', a.change_date, null FROM  audit_log a WHERE a.attr_name = 'group' 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 = 'group')
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 = 'group' AND b.change_date = ( SELECT MIN(change_date) FROM  audit_log c WHERE c.audobj_uniqueid = a.audobj_uniqueid AND c.attr_name = 'group') UNION SELECT distinct a.audobj_uniqueid, VG.last_name, '', a.change_date, null
FROM (( call_req cr inner join  audit_log a ON cr.persid = a.audobj_persid) inner join  View_Group VG
ON cr.group_id = VG.contact_uuid) WHERE NOT EXISTS ( SELECT b.audobj_persid FROM  audit_log b
WHERE b.attr_name = 'group' 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,VG.last_name, '', a.change_date, null FROM (( chg inner join  audit_log a ON chg.persid = a.audobj_persid) inner join  View_Group VG ON chg.group_id = VG.contact_uuid)
WHERE NOT EXISTS ( SELECT b.audobj_persid FROM  audit_log b WHERE b.attr_name = 'group' 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_Group] TO [service_desk_admin_group]
GRANT SELECT ON  [dbo].[View_Audit_Group] TO [service_desk_ro_group]
GO