CREATE PROCEDURE [dbo].[create_insert_kpi_lost_invoice_monthly_summary]
    AS
BEGIN
    DROP TABLE IF EXISTS kpi_lost_invoice_monthly_summary;
    create table kpi_lost_invoice_monthly_summary
    (
      year int not null,
      month int not null,
      yearmonth varchar(255) null,
      customer_id int null,
      region_type_id int null,
      service_type_id int null,
      sales_person_id_by_create int null,
      shared_with_by_create int null,
      temp_shared_with_by_create int null,
      leadgen_id_by_create int null,
      mentor_id_by_create int null,
      collection_user_id_by_create int null,
      tl_id_by_create int null,
      sales_person_id_by_start int null,
      shared_with_by_start int null,
      temp_shared_with_by_start int null,
      leadgen_id_by_start int null,
      mentor_id_by_start int null,
      collection_user_id_by_start int null,
      tl_id_by_start int null,
      invoices_cnt int null,
      lost_amount money null,
      invoice_amount money null,
      client_grade_id int null,
      relationship_id int default 0
    );
    create index kpi_lost_invoice_monthly_summary_yearmonth_index on kpi_lost_invoice_monthly_summary(yearmonth);
    create columnstore index ncci_kpi_lost_invoice_monthly_summary_index
    on kpi_lost_invoice_monthly_summary (yearmonth, customer_id);
    insert into kpi_lost_invoice_monthly_summary
select
year, Month, YearMonth, customer_id,
       region_type_id,
       t.service_type_id,
       sales_person_id_by_create,
       shared_with_by_create,
       temp_shared_with_by_create,
       mentor_id_by_create,
       leadgen_id_by_create,
       collection_user_id_by_create,
       tl_id_by_create,
       sales_person_id_by_start,
       shared_with_by_start,
       temp_shared_with_by_start,
       mentor_id_by_start,
       leadgen_id_by_start,
       collection_user_id_by_start,
       tl_id_by_start,
       COUNT(t.invoice_number) as invoices_cnt,
       SUM(lost_amount) as lost_amount,
       SUM(invoice_amount) as invoice_amount,
       client_grade_id,
       relationship_id
from (
select
       i.customer_id,
       cus.region_type_id,
       i.date_generated,
       create_date.year,
       create_date.Month,
       create_date.YearMonth,
       i.invoice_number,
       (case when i.project_id > 0 then 1 when i.package_id > 0 then 3 else 2 end) as service_type_id,
       (i.pending_amount/exchange_rate) as lost_amount,
       (i.invoice_amount/exchange_rate) as invoice_amount,
       sales_by_create.sales_person_id as sales_person_id_by_create,
       sales_by_create.shared_with as shared_with_by_create,
       sales_by_create.temp_shared_with as temp_shared_with_by_create,
       sales_by_create.mentor_id as mentor_id_by_create,
       sales_by_create.leadgen_id as leadgen_id_by_create,
       sales_by_create.collection_user_id as collection_user_id_by_create,
       sales_by_create.tl_id as tl_id_by_create,
       sales_by_start.sales_person_id as sales_person_id_by_start,
       sales_by_start.shared_with as shared_with_by_start,
       sales_by_start.temp_shared_with as temp_shared_with_by_start,
       sales_by_start.mentor_id as mentor_id_by_start,
       sales_by_start.leadgen_id as leadgen_id_by_start,
       sales_by_start.collection_user_id as collection_user_id_by_start,
       sales_by_start.tl_id as tl_id_by_start,
       cus.client_grade_id,
       cus.relationship_id
            from dim_lost_invoices as i
join dim_date as create_date on create_date.DateKey = (case when i.start_date is null then i.date_generated else i.start_date end)
join dim_customer as cus on cus.customer_id = i.customer_id
left join fact_customer_sales_history as sales_by_create on sales_by_create.history_id = (select top 1 history_id
    from fact_customer_sales_history as fcsh where fcsh.DateKey <= i.date_generated
    and fcsh.customer_id = i.customer_id
    order by fcsh.history_id desc
)
left join fact_customer_sales_history as sales_by_start on sales_by_start.history_id = (select top 1 history_id
    from fact_customer_sales_history as fcsh where fcsh.DateKey <= (case when i.start_date is null then i.date_generated else i.start_date end)
    and fcsh.customer_id = i.customer_id
    order by fcsh.history_id desc
)) as t
group by year, Month, YearMonth, customer_id,region_type_id,service_type_id,
         sales_person_id_by_create, shared_with_by_create, temp_shared_with_by_create, mentor_id_by_create, leadgen_id_by_create, collection_user_id_by_create, tl_id_by_create,
         sales_person_id_by_start, shared_with_by_start, temp_shared_with_by_start, mentor_id_by_start, leadgen_id_by_start, collection_user_id_by_start, tl_id_by_start,client_grade_id,relationship_id;
;
         UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_insert_kpi_lost_invoice_monthly_summary';

END;
go

