CREATE PROCEDURE [dbo].[create_kpi_new_customers]
AS
BEGIN
    DROP TABLE IF EXISTS kpi_new_customers;
    create table kpi_new_customers
(
    DateKey int not null,
    YearMonth varchar(255) not null,
    cnt_by_create int null,
    cnt_by_first_pkg int null,
      cnt_by_pkg_project int default 0,
    closed_cnt_by_pkg_project int default 0
    );
    DECLARE @CurrentMonth DATE = CAST('2005-01-01' AS DATE)
    DECLARE @EndMonth DATE = EOMONTH(CONVERT(varchar,YEAR(getdate()))+'-12-31')
    WHILE @CurrentMonth < @EndMonth
    BEGIN
        INSERT INTO kpi_new_customers (DateKey, YearMonth, cnt_by_create, cnt_by_first_pkg,cnt_by_pkg_project, closed_cnt_by_pkg_project)
        VALUES
        (
         (YEAR(@CurrentMonth) * 10000 + MONTH(@CurrentMonth) * 100 + DAY(@CurrentMonth)),
          (select dim_date.YearMonth from dim_date where Date = @CurrentMonth ),
         (select count(customer_id) from dim_customer where created_on/100 = dbo.convertDateToInt(@CurrentMonth)/100),
         (select count(customer_id) from dim_customer where first_start_pkg_on/100 = dbo.convertDateToInt(@CurrentMonth)/100),
          (select count(customer_id) from dim_customer where (iif(first_start_pkg_on is not null and first_project_started_on is not null,(iif(first_start_pkg_on > first_project_started_on, first_project_started_on, first_start_pkg_on)), iif(first_start_pkg_on is null and first_project_started_on is not null, first_project_started_on, first_start_pkg_on) ))/100 = dbo.convertDateToInt(@CurrentMonth)/100 ),
        (select count(customer_id) from dim_customer where (iif(last_package_end_date is not null and last_project_delivered_on is not null, (iif(last_package_end_date > last_project_delivered_on, last_package_end_date, last_project_delivered_on)), (iif(last_package_end_date is null, last_project_delivered_on , last_package_end_date))))/100 = dbo.convertDateToInt(@CurrentMonth)/100)

        );
        SET @CurrentMonth = DATEADD(MONTH , 1, @CurrentMonth)
    END;
         UPDATE last_updated_at_history set last_updated_at = getdate() where sp_name = 'create_kpi_new_customers';
END;
go

