what will be the change in sql query TO change status of the Requirement if Occured Second Time ?

See more:

C#

ASP.NET

SQL

SQL-Server-2008R2

public DataSet getAuditLog(int secreqid,int secid)
{
StringBuilder SQL = new StringBuilder(); SQL.Append(" select convert(varchar, dateadd(hour, " + (-Tzfactor) + ", au.date),101) as [Date], au.newvalue,au.userid,d.deptname,fu.firmusername, ");
SQL.Append(" case when au.newvalue like '%StatusID=2%' then '2' else case when au.newvalue like 'True%' or newvalue like 'false%' or au.newvalue like '' then '0' else au.newvalue end end as [Status1],st.statusname as [Status], ");
SQL.Append(" Requirement=(select top 1 firmcompliancerequirement from firmcompliancerequirement where sectionrequirementid=").Append(secreqid).Append(" and firmid=").Append(this.FirmID).Append(" and sectionid=").Append(secid).Append(" and isactive=1 and isdelete=0 ) ");
SQL.Append(" from audit_new au join firmuser fu on fu.firmuserid=au.userid join department d on d.deptid=fu.DepartmentID ");
SQL.Append(" join status st on st.statusid=(case when au.newvalue like '%StatusID=2%' then '2' else case when au.newvalue like 'True%' or newvalue like 'false%' or au.newvalue like '' then '0' else au.newvalue end end) ");
SQL.Append(" where au.entityid=(select requirementcertificationid from requirementcertification where firmid=").Append(this.FirmID).Append(" and sectionid=").Append(secid).Append(" and firmcompliancerequirementid=(select firmcompliancerequirementid from firmcompliancerequirement where sectionrequirementid=").Append(secreqid).Append(" and ");
SQL.Append(" firmid=").Append(this.FirmID).Append(" and sectionid=").Append(secid).Append(" and isactive=1 and isdelete=0)) ");
SQL.Append(" and au.modulename like '% RequirementCertification%' ");
SQL.Append(" and case when au.newvalue like '%StatusID=2%' then '2' else case when au.newvalue like 'True%' or newvalue like 'false%' or au.newvalue ");
SQL.Append(" like '' then '0' else au.newvalue end end between '1' and'5' and (case when au.newvalue like '%StatusID=2%' then '2' else case when au.newvalue like 'True%' or newvalue like 'false%' or au.newvalue like '' then '0' else au.newvalue end end not like '%:%') ");
SQL.Append(" order by au.date desc ");
DataSet ds=DAL.SelectRecords(SQL.ToString());
return ds;
}




Result of the Query

01/29/2015 5 10215 Compliance Manthan R 5 Certification Approved Requirement for new
01/29/2015 4 10215 Compliance Manthan R 4 Certified Requirement for new
01/29/2015 2 10215 Compliance Manthan R 2 Pending Documentation Requirement for new
01/29/2015 4 10215 Compliance Manthan R 4 Certified Requirement for new
01/29/2015 3 10215 Compliance Manthan R 3 Pending Certification Requirement for new
01/29/2015 PolicyConfirmation=False,GeneralConfirmation=False,
Comment=,StatusID=2,FCRID=322555
10215 Compliance Manthan R 2 Pending Documentation Requirement for new


i want This

if pending documentation occured more than once then the status will be ...
Pending Documentation(Rejected by Compliance User)

plz help