CREATE PROCEDURE [dbo].[create_insert_user_stats]
AS
    BEGIN
        DROP TABLE IF EXISTS user_stats;
        CREATE TABLE user_stats (
            log_id int not null identity(1,1),
            user_id int not null,
            joined_on int null,
            left_on int null,
            new_left_on int null,
            is_last_record int default 0
        );
        insert into user_stats (user_id, joined_on, left_on)
        select * from (
                  select user_id, joined_on, left_on
                  from dim_users
                  union all
                  select user_id, dbo.convertDateToInt(rejoin_date), dbo.convertDateToInt(last_working_day)
                  from mis_user_rejoined
        )t order by user_id , joined_on
;
        update t1
    set t1.new_left_on = us.left_on,
        is_last_record = (CASE WHEN us.log_id is NULL THEN 1 ELSE 0 END)
from user_stats as t1
left join user_stats us on us.log_id = (
    select top 1 log_id from user_stats as us1 where us1.log_id > t1.log_id and us1.user_id = t1.user_id and t1.joined_on != us.joined_on
    )
where t1.user_id IN (select user_id from user_stats group by user_id having count(log_id) > 1);
;

        update  s1
set s1.new_left_on = (select top 1 left_on from user_stats s2  where s2.user_id = s1.user_id)
from user_stats s1
where is_last_record = 1;

        update user_stats
set new_left_on = left_on
where user_id not IN (select user_id from user_stats where is_last_record = 1);

        UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_insert_user_stats';
    END
go

