update c
set isbatteryempty=1
from CpStatusLogLatest c
	join ActvtRtProcQueueTbl a on a.iCpId=c.CpId
	join (
		select icpid,max(serverutctime) md
		from ActvtRtProcQueueTbl 
		where EventCode='TEB'
		group by icpid
		) b on b.iCpId=a.iCpId and a.ServerUtcTime=b.md
where EventCode='TEB'
	and EventState='a'	

update c
set isbatterylow=1
from CpStatusLogLatest c
	join ActvtRtProcQueueTbl a on a.iCpId=c.CpId
	join (
		select icpid,max(serverutctime) md
		from ActvtRtProcQueueTbl 
		where EventCode='TLB'
		group by icpid
		) b on b.iCpId=a.iCpId and a.ServerUtcTime=b.md
where EventCode='TLB'
	and EventState='a'	


update c
set IsMainPowerFault=1
from CpStatusLogLatest c
	join ActvtRtProcQueueTbl a on a.iCpId=c.CpId
	join (
		select icpid,max(serverutctime) md
		from ActvtRtProcQueueTbl 
		where EventCode='TAC'
		group by icpid
		) b on b.iCpId=a.iCpId and a.ServerUtcTime=b.md
where EventCode='TAC'
	and EventState='a'	


update c
set IsEthernetDisconnected=1
from CpStatusLogLatest c
	join ActvtRtProcQueueTbl a on a.iCpId=c.CpId
	join (
		select icpid,max(serverutctime) md
		from ActvtRtProcQueueTbl 
		where EventCode='TCF'
			and substring(originalmsg,68,2)='03'
		group by icpid
		) b on b.iCpId=a.iCpId and a.ServerUtcTime=b.md
where EventCode='TCF'
	and EventState='A'	
	and substring(originalmsg,68,2)='03'

update c
set IsNoCellularCoverage=1
from CpStatusLogLatest c
	join ActvtRtProcQueueTbl a on a.iCpId=c.CpId
	join (
		select icpid,max(serverutctime) md
		from ActvtRtProcQueueTbl 
		where EventCode='TCF'
			and substring(originalmsg,68,2)='01'
		group by icpid
		) b on b.iCpId=a.iCpId and a.ServerUtcTime=b.md
where EventCode='TCF'
	and EventState='A'	
	and substring(originalmsg,68,2)='01'





