ExpandedVenture
commented
2 years ago Research
use euler;
go
select i.create_time, i.inmessage_tries, p.name, u.username, u.delete_time, u.status, n.status, n.delete_time, count(distinct j.id) as canceled
--select count(h.id)
from [message] m with (nolock)
join lnchat h with (nolock) on ( m.id=h.id_message and h.direction=0 and h.inmessage_end_time is not null )
join lnchat i with (nolock) on ( i.id_result=h.id_result and i.id<>h.id and i.direction=0 and i.inmessage_end_time is null )
join result r with (nolock) on r.id=i.id_result
join [profile] p with (nolock) on p.id=r.id_profile
join lnuser u with (nolock) on u.id=i.id_lnuser
left join [message] n with (nolock) on n.id=i.id_message -- to know if the chat was canceled because because the message was deleted
left join lnchat j with (nolock) on ( -- canceled because a previous message?
j.id_result=i.id_result and
j.id<>i.id and
j.id<>h.id and
j.direction = 1 and
j.create_time > i.create_time -- el mensaje entrante se detecto luego de haber creado el mensaje saliente, y antes de haber enviado el mensaje saliente
)
where m.id_pipeline='1BC2A8E4-D860-4ECC-B336-2AC1F90F01FC'
and m.trigger_type=0
group by i.create_time, i.inmessage_tries, p.name, u.username, u.delete_time, u.status, n.status, n.delete_time
order by n.status desc, i.create_time
go
/*
12620 requests
1790 chats sent (automated followups, manual messages)
162 chats are still pending
17 chats are recently created messages, that are still in queue
The other 162-17=145 chats have been generated from deleted messages, so they are not elegible for delivery.
*/
Problem
Complaint from the Client: "Messages not getting sent".