ExpandedVenture
commented
2 years ago Open ExpandedVenture opened 2 years ago
ExpandedVenture
commented
2 years ago ExpandedVenture
commented
2 years ago Use this query to find duplications happened:
select y.id_lnuser, x.*
from drl_minutely_stats x with (nolock)
/*left*/ join drl_minutely_stats y with (nolock) on (x.id_result=y.id_result and x.id_lnuser<>y.id_lnuser)
where x.ininvite_hit_time > dateadd(hh,-24*1,getdate())drop trigger tr_profile_after_insert_update;
go
drop index IX_lisearchresult_for_pending_append_liprofile_1 on result;
drop index IX_lisearchresult_for_relaunch_append_liprofile_4 on result;
drop index IX_lisearchresult_for_relaunch_count_append_liprofile_4 on result;
drop index IX_lisearchresult_for_pending_append_liprofile_4 on result;
drop index IX_result_for_pending_append_profile_4 on result;
go
alter table result drop column stat_profile_append1_reservation_id;
alter table result drop column stat_profile_append1_reservation_time;
alter table result drop column stat_profile_append1_reservation_times;
alter table result drop column stat_profile_append1_start_time;
alter table result drop column stat_profile_append1_end_time;
alter table result drop column stat_profile_append4_reservation_id;
alter table result drop column stat_profile_append4_reservation_time;
alter table result drop column stat_profile_append4_reservation_times;
alter table result drop column stat_profile_append4_start_time;
alter table result drop column stat_profile_append4_end_time;
alter table result drop column stat_profile_append7_reservation_id;
alter table result drop column stat_profile_append7_reservation_time;
alter table result drop column stat_profile_append7_reservation_times;
alter table result drop column stat_profile_append7_start_time;
alter table result drop column stat_profile_append7_end_time;
go
drop index IX_liprofile__append1_reservation_id__append1_reservation_times__id__id_lncompany_from_title on [profile];
drop index IX_liprofile__append1_reservation_id__append1_reservation_times__id__id_lncompany_from_headline on [profile];
drop index IX_liprofile__append1_reservation_id__append1_start_time__append1_reservation_time on [profile];
go
drop index IX_liprofile__append4_reservation_id__append4_reservation_times__id__id_lncompany_from_headline on [profile];
drop index IX_liprofile__append4_reservation_id__append4_reservation_times__id__id_lncompany_from_title on [profile];
drop index IX_liprofile__append4_reservation_id__append4_start_time on [profile];
drop index IX_liprofile__append4_reservation_id__append4_start_time__append4_reservation_time on [profile];
go
drop index IX_LIPROFILE__APPEND7_RESERVATION_ID__APPEND7_START_TIME on [profile];
go
alter table [profile] drop column append1_reservation_id;
alter table [profile] drop column append1_reservation_time;
alter table [profile] drop column append1_reservation_times;
alter table [profile] drop column append1_start_time;
alter table [profile] drop column append1_end_time;
go
alter table [profile] drop column append4_reservation_id;
alter table [profile] drop column append4_reservation_time;
alter table [profile] drop column append4_reservation_times;
alter table [profile] drop column append4_start_time;
alter table [profile] drop column append4_end_time;
go
alter table [profile] drop column append7_reservation_id;
alter table [profile] drop column append7_reservation_time;
alter table [profile] drop column append7_reservation_times;
alter table [profile] drop column append7_start_time;
alter table [profile] drop column append7_end_time;
go
/*
create trigger [dbo].[tr_profile_after_insert_update] on [dbo].[profile] after insert,update
as
begin
declare @n as integer;
-- obtengo la cantidad de registros que se estan insertando - se inserta mas de uno cuando se hace un bulk-insert o un insert-select
select @n=count(*) from inserted;
-- valido que solo se inserte un registro
if @n>5000
begin
ROLLBACK TRANSACTION
raiserror('Cannot insert or update more than 5000 record at once in the table profile.', 16, 1);
return;
end
-- actualizo los campos snapshot en la tabla lisearchresult
update result
set
stat_profile_append1_reservation_id = p.append1_reservation_id,
stat_profile_append1_reservation_time = p.append1_reservation_time,
stat_profile_append1_reservation_times = p.append1_reservation_times,
stat_profile_append1_start_time = p.append1_start_time,
stat_profile_append1_end_time = p.append1_end_time,
stat_profile_append4_reservation_id = p.append4_reservation_id,
stat_profile_append4_reservation_time = p.append4_reservation_time,
stat_profile_append4_reservation_times = p.append4_reservation_times,
stat_profile_append4_start_time = p.append4_start_time,
stat_profile_append4_end_time = p.append4_end_time,
stat_profile_append7_reservation_id = p.append7_reservation_id,
stat_profile_append7_reservation_time = p.append7_reservation_time,
stat_profile_append7_reservation_times = p.append7_reservation_times,
stat_profile_append7_start_time = p.append7_start_time,
stat_profile_append7_end_time = p.append7_end_time
from result r
join inserted p on p.id=r.id_profile;
end;
*/drop trigger tr_append_after_insert;
go
alter table search drop column stat_results_appended;
go
DROP INDEX IX_lisearchresult_for_relaunch_append_liprofile_1 ON result;
DROP INDEX IX_lisearchresult_for_relaunch_append_liprofile_7 ON result;
DROP INDEX IX_lisearchresult_for_relaunch_count_append_liprofile_7 ON result;
DROP INDEX IX_lisearchresult_for_pending_append_liprofile_7 ON result;
DROP INDEX IX_lisearchresult__id_lnsearch__stat_appended__append4_allocation_to_em_time ON result;
DROP INDEX IX_lisearchresult__id_lnsearch__stat_appended__id_liprofile ON result;
DROP INDEX IX_lisearchresult__id_lnsearch__stat_appended__stat_lncompany_has_website__id_liprofile ON result;
DROP INDEX IX_result__id_search__stat_appended__append4_allocation_to_crm_time ON result;
DROP INDEX IX_lisearchresult__stat_appended__id_liprofile ON result;
alter table result drop column stat_appended;
go
/*
create trigger [dbo].[tr_append_after_insert] on [dbo].[append] after insert
as
begin
declare @n as integer;
declare @m as integer;
declare @pid as uniqueidentifier;
-- obtengo la cantidad de registros que se estan insertando - se inserta mas de uno cuando se hace un bulk-insert o un insert-select
select @n=count(*) from inserted;
-- valido que solo se inserte un registro
if @n<>1
begin
ROLLBACK TRANSACTION;
raiserror('Cannot update more than 1 record at once in the table append.', 16, 1);
return;
end
--
select @pid=id_profile from inserted;
-- inicio la transaccion
begin transaction;
-- bloqueo los registros result vinculado a este perfil, que no tengan el flag de appended
select @m = COUNT(distinct s.id)
from search s with (xlock) -- bloqueo el registro
join result r on s.id=r.id_search
where r.id_profile=@pid
and isnull(r.stat_appended,0)=0;
select @n = COUNT(r.id)
from result r with (xlock) -- bloqueo el registro
where r.id_profile=@pid
and isnull(r.stat_appended,0)=0;
if (@n>0)
begin
update search
set stat_results_appended=isnull(stat_results_appended,0)+1
where id in (
select distinct r.id_search
from result r
where r.id_profile=@pid
and isnull(r.stat_appended,0)=0
);
update result
set stat_appended=1
where id_profile=@pid
and isnull(stat_appended,0)=0;
end;
-- finalizo la transaccion
commit transaction;
end
GO
*/[tr_result_after_insert]
stat_results
stat_results_appended
(DELETED) [tr_profile_after_insert_update]
stat_profile_append1_reservation_id
[tr_lnchat_before_insert]
updateDrlMinutelyStatsOnChatModification
[tr_lnchat_before_update]
updateDrlMinutelyStatsOnChatModification
(DELETED) [tr_append_after_insert]
search.stat_results_appended
result.stat_appended
[tr_append_before_insert]
updateDrlMinutelyStatsOnProfileModification
[tr_append_before_update]
updateDrlMinutelyStatsOnProfileModificationALTER TABLE drl_minutely_stats ADD create_time DATETIME NULL;
GO
/* use this query to find recently created invitation messages,
* who should have a value in `drl_minutely_stats.create_time`.
*
select h.id_result, h.body
from [message] m
join lnchat h on (m.id=h.id_message and h.direction=0 and h.create_time>dateadd(mi,-10,getdate()))
where m.trigger_type=0
*/
DROP PROCEDURE updateDrlMinutelyStatsOnChatModification;
GO
CREATE PROCEDURE [dbo].[updateDrlMinutelyStatsOnChatModification](@id_result UNIQUEIDENTIFIER, @id_lnuser UNIQUEIDENTIFIER, @id_message UNIQUEIDENTIFIER)
AS
BEGIN
DECLARE @sql NVARCHAR(4000)
DECLARE @n NUMERIC(18,0);
DECLARE @m NUMERIC(18,0);
DECLARE @dt DATETIME;
DECLARE @dt1 DATETIME;
DECLARE @dt2 DATETIME;
DECLARE @gct BIT;
DECLARE @gct_id_user UNIQUEIDENTIFIER;
DECLARE @id UNIQUEIDENTIFIER; -- id of message
-- searchable
DECLARE @pid UNIQUEIDENTIFIER; -- id of the profile
DECLARE @cur CURSOR;
DECLARE @value VARCHAR(8000);
-- si NO existe un registro en la tabla de estadisticas, para este result, lnuser y mensaje ...
BEGIN TRANSACTION
select @n = count(*)
from drl_minutely_stats x with (xlock) -- bloqueo el registro de movimientos
where x.id_result = @id_result
and x.id_lnuser = @id_lnuser
and x.id_message = @id_message;
if (@n=0)
begin
-- ... lo inserto
insert into drl_minutely_stats
(id_client, id_search, id_pipeline, id_message, id_lnuser, id_result, id_profile, create_time)
select distinct top 1 u.id_client, r.id_search, m.id_pipeline, m.id, h.id_lnuser, h.id_result, r.id_profile, getdate()
from result r with (nolock)
join lnchat h with (nolock) on r.id=h.id_result
join [message] m with (nolock) on (m.id=h.id_message and isnull(m.trigger_type,0) in (0,-2)) -- invitation or campaign
join [user] u with (nolock) on u.id=m.id_user
where h.id_result = @id_result
and h.id_lnuser = @id_lnuser
and h.id_message = @id_message;
end
COMMIT TRANSACTION;
-- build searchable
select @pid = r.id_profile
from result r with (nolock)
where r.id=@id_result;
-- ... actualizo los searchable: name, headline, location, industry
update drl_minutely_stats
set
name = (select name from [profile] p with (nolock) where p.id=@pid),
location = (select location from [profile] p with (nolock) where p.id=@pid),
industry = (select industry from [profile] p with (nolock) where p.id=@pid),
company = (
select c.name
from [profile] p with (nolock)
left join company c with (nolock) on c.id=p.id_company_from_headline
where p.id=@pid
),
searchable = (
select
'name:"'+replace(isnull(p.name,''),'"','\"')+'", ' +
'headline:"'+replace(isnull(p.headline,''),'"','\"')+'", ' +
'location:"'+replace(isnull(p.location,''),'"','\"')+'", ' +
'industry:"'+replace(isnull(p.industry,''),'"','\"')+'", ' +
'company:"'+replace(isnull(c.name,''),'"','\"')+'", '
from [profile] p with (nolock)
left join company c with (nolock) on c.id=p.id_company_from_headline
where p.id=@pid
)
where id_result = @id_result
and id_lnuser = @id_lnuser
and id_message = @id_message;
-- ... actualizo los searchable: emails
SET @cur = CURSOR FOR
SELECT DISTINCT a.email as value
FROM [append] a WITH (NOLOCK)
WHERE a.id_profile = @pid
AND ISNULL(a.type,20) = 20; -- email
OPEN @cur;
FETCH NEXT FROM @cur INTO @value;
WHILE @@FETCH_STATUS = 0
BEGIN
update drl_minutely_stats
set searchable = isnull(searchable, '') + 'email:"'+replace(isnull(@value,''),'"','\"')+'", '
where id_result = @id_result
and id_lnuser = @id_lnuser
and id_message = @id_message;
FETCH NEXT FROM @cur INTO @value;
END
CLOSE @cur;
DEALLOCATE @cur;
-- ... actualizo los searchable: phones
SET @cur = CURSOR FOR
SELECT DISTINCT a.email as value
FROM [append] a WITH (NOLOCK)
WHERE a.id_profile = @pid
AND ISNULL(a.type,20) = 10; -- phone
OPEN @cur;
FETCH NEXT FROM @cur INTO @value;
WHILE @@FETCH_STATUS = 0
BEGIN
update drl_minutely_stats
set searchable = isnull(searchable, '') + 'phone:"'+replace(isnull(@value,''),'"','\"')+'", '
where id_result = @id_result
and id_lnuser = @id_lnuser
and id_message = @id_message;
FETCH NEXT FROM @cur INTO @value;
END
CLOSE @cur;
DEALLOCATE @cur;
-- ... actualizo los searchable: linkedin
SET @cur = CURSOR FOR
SELECT DISTINCT a.email as value
FROM [append] a WITH (NOLOCK)
WHERE a.id_profile = @pid
AND ISNULL(a.type,20) = 90; -- linkedin
OPEN @cur;
FETCH NEXT FROM @cur INTO @value;
WHILE @@FETCH_STATUS = 0
BEGIN
update drl_minutely_stats
set searchable = isnull(searchable, '') + 'linkedin:"'+replace(isnull(@value,''),'"','\"')+'", '
where id_result = @id_result
and id_lnuser = @id_lnuser
and id_message = @id_message;
FETCH NEXT FROM @cur INTO @value;
END
CLOSE @cur;
DEALLOCATE @cur;
-- ... actualizo los searchable: chats
SET @cur = CURSOR FOR
SELECT DISTINCT h.body as body
FROM lnchat h WITH (NOLOCK)
where h.id_result = @id_result
and h.id_lnuser = @id_lnuser
and h.id_message = @id_message
and isnull(h.direction, 0) <> 3; -- disregards "Invitation Accepted" chats
OPEN @cur;
FETCH NEXT FROM @cur INTO @value;
WHILE @@FETCH_STATUS = 0
BEGIN
update drl_minutely_stats
set searchable = isnull(searchable, '') + 'chat:"'+replace(isnull(@value,''),'"','\"')+'", '
where id_result = @id_result
and id_lnuser = @id_lnuser
and id_message = @id_message;
FETCH NEXT FROM @cur INTO @value;
END
CLOSE @cur;
DEALLOCATE @cur;
-- INVITATION MESSAGES
-- ininvite_start_time
-- ininvite_end_time
update x set
x.ininvite_start_time = h.inmessage_start_time,
x.ininvite_end_time = h.inmessage_end_time
from drl_minutely_stats x with (nolock)
join lnchat h with (nolock) on h.id_result=x.id_result
join [message] m with (nolock) on (m.id=h.id_message and isnull(m.trigger_type,0) in (0,-2) and isnull(h.direction,0)=0)
where x.id_result = @id_result
and x.id_lnuser = @id_lnuser
and x.id_message = @id_message;
-- INVITATIONS ACCEPTANCE - NEW CONNECTIONS
-- ininvite_hit_time
set @dt=null;
select @dt = min(h.create_time)
from drl_minutely_stats x with (nolock)
join lnchat h with (nolock) on (x.id_result=h.id_result and isnull(h.direction,0)=3)
where x.id_result = @id_result
and x.id_lnuser = @id_lnuser
and x.id_message = @id_message;
--
update drl_minutely_stats set ininvite_hit_time = @dt where id_result = @id_result and id_lnuser=@id_lnuser and id_message=@id_message;
-- INTRODUCTION MESSAGES
-- id_message_1
-- inmessage_1_start_time
-- inmessage_1_end_time
set @dt1=null;
set @dt2=null;
set @id=null;
set @gct=null;
set @gct_id_user=null;
select top 1 @dt1 = h.inmessage_start_time, @dt2 = h.inmessage_end_time, @id = m.id, @gct=h.gct, @gct_id_user=h.gct_id_user
from drl_minutely_stats x with (nolock)
join lnchat h with (nolock) on (x.id_result=h.id_result and isnull(h.direction,0)=0 and h.delete_time is null)
join [message] m with (nolock) on (m.id=h.id_message and isnull(m.trigger_type,0)=1)
where x.id_result = @id_result
and x.id_lnuser = @id_lnuser
and x.id_message = @id_message
order by h.create_time; -- replicate the first intro messages created
--
update drl_minutely_stats set inmessage_1_start_time = @dt1, inmessage_1_end_time = @dt2, id_message_1 = @id, gct_1 = @gct, gct_1_id_user = @gct_id_user where id_result = @id_result and id_lnuser=@id_lnuser and id_message=@id_message;
-- WELCOME MESSAGE
-- id_message_2
-- inmessage_start_time (it should be inmessage_2_start_time)
-- inmessage_end_time (it should be inmessage_2_end_time)
set @dt1=null;
set @dt2=null;
set @id=null;
set @gct=null;
set @gct_id_user=null;
select top 1 @dt1 = h.inmessage_start_time, @dt2 = h.inmessage_end_time, @id = m.id, @gct=h.gct, @gct_id_user=h.gct_id_user
from drl_minutely_stats x with (nolock)
join lnchat h with (nolock) on (x.id_result=h.id_result and isnull(h.direction,0)=0 and h.delete_time is null)
join [message] m with (nolock) on (m.id=h.id_message and isnull(m.trigger_type,0)=2)
where x.id_result = @id_result
and x.id_lnuser = @id_lnuser
and x.id_message = @id_message
order by h.create_time; -- replicate the first welcome messages created
--
update drl_minutely_stats set inmessage_start_time = @dt1, inmessage_end_time = @dt2, id_message_2 = @id, gct_2 = @gct, gct_2_id_user = @gct_id_user where id_result = @id_result and id_lnuser=@id_lnuser and id_message=@id_message;
-- POSITIVE REPLY
-- inreply_positive
set @dt=null;
select @dt = max(h.create_time)
from drl_minutely_stats x with (nolock)
join lnchat h with (nolock) on (x.id_result=h.id_result and isnull(h.direction,0)=1 and isnull(h.positive,0)=1 and h.delete_time is null)
where x.id_result = @id_result
and x.id_lnuser = @id_lnuser
and x.id_message = @id_message;
if (@dt is not null)
begin
update drl_minutely_stats set inreply_positive = 1 where id_result = @id_result and id_lnuser=@id_lnuser and id_message=@id_message;
end
else
begin
update drl_minutely_stats set inreply_positive = 0 where id_result = @id_result and id_lnuser=@id_lnuser and id_message=@id_message;
end
-- LAST REPLY
-- inreply_time
set @dt=null;
select @dt = max(h.create_time)
from drl_minutely_stats x with (nolock)
join lnchat h with (nolock) on (x.id_result=h.id_result and isnull(h.direction,0)=1 /*and isnull(h.positive,0)=1*/ and h.delete_time is null)
where x.id_result = @id_result
and x.id_lnuser = @id_lnuser
and x.id_message = @id_message;
--
update drl_minutely_stats set inreply_time = @dt where id_result = @id_result and id_lnuser=@id_lnuser and id_message=@id_message;
-- TRANSFER MESSAGES
-- wlt_time
set @dt=null;
set @gct=null;
set @gct_id_user=null;
select top 1 @dt = h.inmessage_end_time, @gct=h.gct, @gct_id_user=h.gct_id_user
from drl_minutely_stats x with (nolock)
join lnchat h with (nolock) on ( x.id_result=h.id_result and isnull(h.direction,0)=0 and isnull(h.wlt_enabled,0)=1 and h.delete_time is null )
where x.id_result = @id_result
and x.id_lnuser = @id_lnuser
and x.id_message = @id_message
order by h.create_time; -- replicate the first transfer messages created
--
update drl_minutely_stats set wlt_time = @dt, gct = @gct, gct_id_user = @gct_id_user where id_result = @id_result and id_lnuser=@id_lnuser and id_message=@id_message;
/*
-- CAMPAIGN (BLAST) MESSAGES
-- id_message_campaign
-- inmessage_campaign_start_time
-- inmessage_campaign_end_time
set @dt1=null;
set @dt2=null;
set @id=null;
set @gct=null;
set @gct_id_user=null;
select top 1 @dt1 = h.inmessage_start_time, @dt2 = h.inmessage_end_time, @id = m.id
from drl_minutely_stats x with (nolock)
join lnchat h with (nolock) on (x.id_result=h.id_result and isnull(h.direction,0)=0 and h.delete_time is null)
join [message] m with (nolock) on (m.id=h.id_message and isnull(m.trigger_type,0)=-2)
where x.id_result = @id_result
and x.id_lnuser = @id_lnuser
and x.id_message = @id_message
order by h.create_time; -- replicate the first welcome messages created
--
update drl_minutely_stats set inmessage_campaign_start_time = @dt1, inmessage_campaign_end_time = @dt2, id_message_campaign = @id where id_result = @id_result and id_lnuser=@id_lnuser and id_message=@id_message;
*/
-- FLAG SNAPSHOT AS DONE
-- snapshot_time
update drl_minutely_stats set snapshot_time=getdate() where id_result = @id_result and id_lnuser=@id_lnuser and id_message=@id_message;
END; -- PROC
GOselect y.id_lnuser, x.create_time, y.create_time, x.*
from drl_minutely_stats x with (nolock)
/*left*/ join drl_minutely_stats y with (nolock) on (x.id_result=y.id_result and x.id_lnuser<>y.id_lnuser)
where x.ininvite_hit_time > dateadd(hh,-24*1,getdate())There are duplicatons of just created connection requests, who have not been delivered yet.
select h.id_result, h.body, x.ininvite_start_time, x.ininvite_end_time, h.id_result, h.id_lnuser as lnchat_id_lnuser, x.id_lnuser as snapshot_id_lnuser
from [message] m with (nolock)
join lnchat h with (nolock) on (m.id=h.id_message and h.direction=0 and h.create_time>dateadd(hh,-2,getdate()))
join drl_minutely_stats x with (nolock) on (h.id_result=x.id_result and h.id_lnuser<>x.id_lnuser)
where m.trigger_type=0
and h.inmessage_start_time is null
and h.inmessage_end_time is null
and h.inmessage_tries is null
order by h.id_result, h.create_timeWe are sending connection requests to the same lead, from more than 1 account:
select x.id_pipeline, count(x.id_result), count(distinct x.id_result)
from drl_minutely_stats x with (nolock)
join lnchat h with (nolock) on (h.id_result=x.id_result and h.id_lnuser=x.id_lnuser and h.inmessage_end_time is not null)
where x.id_pipeline='8DE371CB-A22F-44B3-A624-83448BA21A28'
and x.ininvite_end_time is not null
group by x.id_pipeline
/*
8DE371CB-A22F-44B3-A624-83448BA21A28, 4794, 3544
*/I found a clue
There are duplicatons of just created connection requests, who have not been delivered yet.
select
h.create_time,
h.id_result,
x.id_result,
h.body,
x.ininvite_start_time,
x.ininvite_end_time,
h.inmessage_tries,
h.id_result,
u1.username,
p1.name,
u2.username,
p2.name, -- Pipeline for Kenneth
u1.id_client_allocated, -- Spark Outbound
u2.id_client_allocated,
y1.id_pipeline,
y2.id_pipeline
--h.id_lnuser as lnchat_id_lnuser, x.id_lnuser as snapshot_id_lnuser
from [message] m with (nolock)
join lnchat h with (nolock) on (m.id=h.id_message and h.direction=0 and h.create_time>dateadd(hh,-1,getdate()))
left join drl_minutely_stats x with (nolock) on (h.id_result=x.id_result and h.id_lnuser<>x.id_lnuser)
join lnuser u1 with (nolock) on u1.id=h.id_lnuser
join pipeline_lnuser y1 with (nolock) on u1.id=y1.id_lnuser
join pipeline p1 with (nolock) on p1.id=y1.id_pipeline
left join lnuser u2 with (nolock) on u2.id=x.id_lnuser
left join pipeline_lnuser y2 with (nolock) on u2.id=y2.id_lnuser
left join pipeline p2 with (nolock) on p2.id=y2.id_pipeline
where m.trigger_type=0
--and h.inmessage_start_time is null
--and h.inmessage_end_time is null
--and h.inmessage_tries is null
order by h.id_result, h.create_timeThis query returns no records, so duplications are always beloning the client.
use euler;
/*
select dateadd(hh,-8,getdate())
*/
select
h.create_time,
x.create_time,
y.create_time,
x.id_result,
y.id_result,
x.id_client,
y.id_client,
x.ininvite_end_time as if_old_chat_has_been_delivered
--h.id_lnuser as lnchat_id_lnuser, x.id_lnuser as snapshot_id_lnuser
from [message] m with (nolock)
--join lnchat h with (nolock) on (m.id=h.id_message and h.direction=0 and h.create_time>'2022-01-03 16:30:10')
join lnchat h with (nolock) on (m.id=h.id_message and h.direction=0 and h.create_time>'2021-12-03 16:30:10')
/*left*/ join drl_minutely_stats x with (nolock) on (h.id_result=x.id_result and h.id_lnuser<>x.id_lnuser) -- old chat
/*left*/ join drl_minutely_stats y with (nolock) on (h.id_result=y.id_result and h.id_lnuser=y.id_lnuser) -- new chat
where m.trigger_type=0
and x.id_client<>y.id_client
--and h.inmessage_start_time is null
--and h.inmessage_end_time is null
--and h.inmessage_tries is null
order by h.id_result, h.create_timeThis other query returns no records, so duplication are mapping the invite_end_time of the real record:
use euler;
/*
select dateadd(hh,-8,getdate())
*/
select
h.create_time,
x.create_time,
y.create_time,
x.id_result,
y.id_result,
x.id_client,
y.id_client,
x.ininvite_end_time as if_old_chat_has_been_delivered
--h.id_lnuser as lnchat_id_lnuser, x.id_lnuser as snapshot_id_lnuser
from [message] m with (nolock)
--join lnchat h with (nolock) on (m.id=h.id_message and h.direction=0 and h.create_time>'2022-01-03 16:30:10')
join lnchat h with (nolock) on (m.id=h.id_message and h.direction=0 and h.create_time>'2021-12-03 16:30:10')
/*left*/ join drl_minutely_stats x with (nolock) on (h.id_result=x.id_result and h.id_lnuser<>x.id_lnuser) -- old chat
/*left*/ join drl_minutely_stats y with (nolock) on (h.id_result=y.id_result and h.id_lnuser=y.id_lnuser) -- new chat
where m.trigger_type=0
and x.inmessage_end_time is not null
and y.inmessage_end_time is not null
and x.inmessage_end_time <> y.inmessage_end_time
--and h.inmessage_start_time is null
--and h.inmessage_end_time is null
--and h.inmessage_tries is null
order by h.id_result, h.create_timeThis query below retrieves 4 records, so the real noise on the stats is very low.
use euler;
/*
select dateadd(hh,-8,getdate())
*/
select
h.create_time,
x.create_time,
y.create_time,
x.id_result,
y.id_result,
x.id_client,
y.id_client,
x.ininvite_end_time as if_old_chat_has_been_delivered
--h.id_lnuser as lnchat_id_lnuser, x.id_lnuser as snapshot_id_lnuser
from [message] m with (nolock)
--join lnchat h with (nolock) on (m.id=h.id_message and h.direction=0 and h.create_time>'2022-01-03 16:30:10')
join lnchat h with (nolock) on (m.id=h.id_message and h.direction=0 and h.create_time>'2021-12-03 16:30:10')
/*left*/ join drl_minutely_stats x with (nolock) on (h.id_result=x.id_result and h.id_lnuser<>x.id_lnuser) -- old chat
/*left*/ join drl_minutely_stats y with (nolock) on (h.id_result=y.id_result and h.id_lnuser=y.id_lnuser) -- new chat
where m.trigger_type=0
and x.inmessage_end_time is not null
and y.inmessage_end_time is not null
--and x.inmessage_end_time <> y.inmessage_end_time
--and h.inmessage_start_time is null
--and h.inmessage_end_time is null
--and h.inmessage_tries is null
order by h.id_result, h.create_time