CREATE PROCEDURE [dbo].[create_fact_customer_sales_history]
AS
    BEGIN
        DROP TABLE IF EXISTS fact_customer_sales_history;
        create table fact_customer_sales_history
        (
            history_id int identity(1,1),
            DateKey int not null,
            from_date datetime null,
            customer_id int not null,
            sales_person_id int null,
            shared_with int null,
            temp_shared_with int null,
            leadgen_id int null,
            mentor_id int null,
            collection_user_id int null,
            tl_id int null
        )
        create index fact_customer_sales_history_customer_id_index
            on fact_customer_sales_history (DateKey, customer_id);
        create CLUSTERED columnstore index cci_fact_customer_sales_history__index
	on fact_customer_sales_history;
        INSERT INTO fact_customer_sales_history
        select dbo.convertDateToInt(from_date),
               from_date, customer_id,
        (SELECT top 1 user_id FROM mis_customer_sales_person_logs
                WHERE customer_id = base.customer_id and
              base.from_date between from_date and iif(to_date is null, getdate(),to_date) order by from_date desc) as sales_person_id,
       (SELECT top 1 user_id FROM mis_customer_shared_with_logs
                WHERE customer_id = base.customer_id and
              base.from_date between from_date and iif(to_date is null, getdate(),to_date) order by from_date desc) as shared_with,
       (SELECT top 1 user_id FROM mis_customer_temp_shared_with_logs
                WHERE customer_id = base.customer_id and
              base.from_date between from_date and iif(to_date is null, getdate(),to_date) order by from_date desc) as temp_shared_with,
        (SELECT top 1 user_id FROM mis_customer_leadgen_logs
                WHERE customer_id = base.customer_id and type = 1 and
              base.from_date between from_date and iif(to_date is null, getdate(),to_date) order by from_date desc) as leadgen_id,
        (SELECT top 1 user_id FROM mis_customer_mentor_logs
                WHERE customer_id = base.customer_id and
              base.from_date between from_date and iif(to_date is null, getdate(),to_date) order by from_date desc) as mentor_id,
        (SELECT top 1 user_id FROM mis_customer_collection_user_logs
                WHERE customer_id = base.customer_id and
              base.from_date between from_date and iif(to_date is null, getdate(),to_date) order by from_date desc) as collection_user_id,
         (SELECT top 1 sales.sales_team_lead_id FROM mis_customer_sales_person_logs
               left join dim_users as sales on sales.user_id = mis_customer_sales_person_logs.user_id
                WHERE customer_id = base.customer_id and
              (base.from_date between from_date and iif(to_date is null, getdate(),to_date)) order by from_date desc) as tl_id
from (select distinct customer_id,from_date from (
select customer_id,from_date from mis_customer_sales_person_logs
union all
select customer_id,from_date from mis_customer_shared_with_logs
union all
select customer_id,from_date from mis_customer_temp_shared_with_logs
union all
select customer_id,from_date from mis_customer_leadgen_logs
union all
select customer_id,from_date from mis_customer_mentor_logs
union all
select customer_id,from_date from mis_customer_collection_user_logs) as
    t ) as base order by customer_id, from_date;

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

