select  
   r.[state] as s1,  
   r.activityid as s2,  
   r.id as s3,  
   r.trackingnumber as s4, 
   a.name as s5,  
   a.officialname as s6,  
   r.submittedon as s7,  
   r.updatedon as s8,  
   r.requesttype as s9,  
   (select count(*) from hib.review v where v.requestid=r.id) as cnt1, 
   (select count(*) from hib.review v where v.requestid=r.id and v.updatedon is not null) as cnt2,  
   inv.Fullname, 
     Case when r.State = 3 then 'Change Requested' 
						 when r.State = 4 then 'Approved' 
						 when r.State = 5 then 'Denied' 
						 when r.State = 6 then 'Closed'
						 when r.RequestType = 4 and r.State = 1 then 'Initiated' 
						 when r.RequestType = 4 and r.State = 2 and r.WorkflowState = 2 then '0%' 
						 when r.RequestType = 4 and r.State = 2 and r.WorkFlowState = 3 then '50%' 
						 when r.RequestTYpe = 4 and r.State = 16 then 'Approved'
						 when r.State = 1 then 'Initiated' 
						 when r.State = 2 then Cast(Case when totCount = 0 then 0 
													  when initNDS.initRev = 0 then 0
													  when totCount != 0 then ((apprejCount+ 1) * 100) / (totcount+2) end as Varchar(100))+'%' 
						 end as RequestStatus,
	 Case   
						 when r.RequestType = 4 and  r.WorkflowState = 1 then 1 
						 when r.State = 1 then 1 
						 when r.State = 3 then 1  
						 else 0 end as [editable], 
						 ammend.Ammendable as Ammendable, 
						 TD.TimeDiff as FinalEventTimeDiffInDays, 
	  Case when r.RequestType = 2 then 'Research Data Access'  
					when r.RequestType = 4 then 'Operations Data Access' end as RequestType, 
	  Case when r.RequestType = 2 then 1 else 0 end as isRes, 
	  Case when r.RequestType = 4 then 1 else 0 end as isOp                 
from hib.request r   
	   inner join hib.activity a on r.activityid=a.id  
	   --left join  (select part.RequestID, person.fullname, row_Number() over (partition by part.RequestID, Part.LocationID order by person.Name desc) RID from hib.participant part inner join hib.person person on part.PersonID = person.ID inner join hib.request r on part.RequestID = r.ID and part.LocationID = r.PrimarySiteID where part.principalInvestigator=1 )  
	 --inv on r.ID = inv.RequestID and inv.RID = 1  
	   left join hib.vwPrimarycontact inv on r.ID = inv.ID
	   left join (select r.ID, SUm(case when Coalesce(rev.approved,rev.rejected) is not null then 1 else 0 end) apprejCount, Count(rev.RequestID) as totcount 
										 from hib.Request R 
										 left join hib.Review  rev on r.ID = rev.RequestID  
										 where r.state = 2 
												group by r.ID) aggres on r.ID = aggRes.ID 
	  left join (select requestID, Max(Case when event.Name = 'Initial NDS Review Complete' then 1 else 0 end) as initRev
																 from hib.event
																group by requestID) initNDS on r.ID = initNDS.RequestID
	  left join (select r.ID,r.activityID, Case when mxHead.mxH = 0 and r.State in (4,5,6) then 1
				  when r.CreatedOn = mxCr.mxAM  and r.state in (4,5,6) then 1 
				  else 0 end as ammendable 
				  from hib.request r  
				  inner join (select ActivityID, Max(HeadID) mxH from hib.Request 
						  group by ActivityID) mxHead on r.ActivityID = mxHead.ActivityID 
				  inner join (select ActivityID, Max(CreatedOn) mxAm from hib.Request  
						  group by ActivityID) mxCr on r.ActivityID = mxCr.ActivityID 
	   ) ammend on r.ID = ammend.ID      
 
	   left join ( select r.ID
				  , Cast(Round(Cast(DateDiff(minute,r.submittedon,Coalesce(mxE.mxcr,getdate())) / 60.0 / 24.0 as Numeric(5,2)),0) as int) as TimeDiff   
			   from hib.Request r  
					left join (select requestID, Max(e.createdON) mxcr from hib.[event] e
									inner join hib.Request r on e.RequestID = r.ID
										where r.State in (4,5,6)
									 group by requestID) mxE on r.ID = mxE.RequestID ) TD on r.ID = TD.ID 
order by r.id ;