I prefer data model solutions to set analysis solutions where possible. So for the solution here, I've added a new table. This new table has timestamps for every half hour, and then points to the key of your Datos table for which record (and thus status and other data) was current at that point in time. For records with a CT status, we keep that status for 24 hours, then have no more records in the new table. I also added a Group field to Datos for Completados, Pendientes, or Unknown, plus a CompletadosCount and a PendientesCount.
LEFT JOIN (Datos)
LOAD *
,if(Group='Completados',1) as CompletadosCount
,if(Group='Pendientes',1) as PendientesCount
;
LOAD
Key
,timestamp(if(ID_LZD=previous(ID_LZD),previous(FechaHoraHist)
,if(ID_TIPTR='CT',FechaHoraHist + 1
,num(makedate(2016,2,20))))) as Next
,if(ID_TIPTR='CT','Completados'
,if(match(ID_TIPTR,'AS','BC','DP','PP','PI'),'Pendientes'
,'Unknown')) as Group
RESIDENT Datos
ORDER BY Key desc
;
Datos2:
LOAD *
,timestamp( Thing /48) as FechaHora
,date(floor(Thing,48)/48) as Fecha
,time( mod(Thing,48)/48) as Hora
;
LOAD
Key
,ceil(FechaHoraHist*48)+iterno()-1 as Thing
RESIDENT Datos
WHILE FechaHoraHist*48+iterno()-1 < Next*48
;
You can then either have a table of count(distinct ID_LZD) by Group, or to keep your nice graphics, you can do a sum(CompletadosCount) and sum(PendientesCount).
I'm one under on the completed number. I'm missing 587373, which was completed at 9:25 then changed back to pending at 9:26. My approach only includes a single status for each half hour - at 9:30, we can't be BOTH completed and pending, so it treats it as pending. Maybe I should allow both for a half an hour in that case. I guess there's no need to keep only a single record for each ID and half hour.
But I can't spend any more time on this, I'm afraid. If you need that record counted, there's surely a small modification that can be made to add it to Datos2, and perhaps that would do the trick. I was surprised to see records changing from completed back to pending, or I'd have planned for it better.
I'm not sure if I've understood the requirement for Pendientes, since you only said it was wrong, not what the number should be. I get 3.
