CREATE PROCEDURE [dbo].[fetch_new_customers_cnt]
    @from nvarchar(max) = '2022-01-01',
    @to nvarchar(max) = '2022-12-31',
    @sales_id nvarchar(max) = '',
    @tl_id nvarchar(max) = '',
    @region_type_id nvarchar(max) = '',
    @client_grade nvarchar(max) = '',
    @relationship_id nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';
    declare @select_query_create nvarchar(max) = '';
    declare @select_query_first_pkg_start nvarchar(max) = '';
    declare @select_query_first_pkg_proj_start nvarchar(max) = '';
    declare @select_query_inactive nvarchar(max) = '';
    SET @select_query_create = 'select dd.YearMonth, count(distinct cus.customer_id) as customers_cnt,0 as  customers_cnt_first_start,0 as customers_cnt_first_pkg_proj_start, 0 as customers_cnt_inactive  from dim_customer as cus
left join fact_customer_sales_history as fcsh on fcsh.history_id = (select top 1 fcsh1.history_id from fact_customer_sales_history fcsh1
    where fcsh1.DateKey <= cus.created_on and fcsh1.customer_id = cus.customer_id  order by fcsh1.from_date desc)
join dim_date as dd on dd.DateKey = cus.created_on';

    SET @select_query_first_pkg_start = 'select dd.YearMonth, 0,count(distinct cus.customer_id) as customers_cnt_first_start, 0 as customers_cnt_first_pkg_proj_start,0 as customers_cnt_inactive  from dim_customer as cus
left join fact_customer_sales_history as fcsh on fcsh.history_id = (select top 1 fcsh1.history_id from fact_customer_sales_history fcsh1
    where fcsh1.DateKey <= cus.first_start_pkg_on and fcsh1.customer_id = cus.customer_id order by fcsh1.from_date desc)
join dim_date as dd on dd.DateKey = cus.first_start_pkg_on';

       SET @select_query_first_pkg_proj_start = 'select dd.YearMonth, 0, 0, count(*) as customers_cnt_first_pkg_proj_start, 0 as customers_cnt_inactive from fact_customers
join dim_date as dd on dd.DateKey = fact_customers.DateKey
where (is_started = 1 or is_restarted = 1)';

     SET @select_query_inactive = 'select dd.YearMonth, 0, 0, 0, count(*) as customers_cnt_inactive from fact_customers
join dim_date as dd on dd.DateKey = fact_customers.DateKey
where (is_discontinued = 1) ';


    SET @select_query_create = @select_query_create + ' where cast(YearMonth+''-''+''01'' as date)  >= ''' + @from +'''';
	SET @select_query_create = @select_query_create + ' and cast(YearMonth+''-''+''01'' as date)  <=''' + @to +'''';

    SET @select_query_first_pkg_start = @select_query_first_pkg_start + ' where cast(YearMonth+''-''+''01'' as date)  >= ''' + @from +'''';
	SET @select_query_first_pkg_start = @select_query_first_pkg_start + ' and cast(YearMonth+''-''+''01'' as date)  <=''' + @to +'''';

    SET @select_query_first_pkg_proj_start = @select_query_first_pkg_proj_start + ' and cast(YearMonth+''-''+''01'' as date)  >= ''' + @from +'''';
	SET @select_query_first_pkg_proj_start = @select_query_first_pkg_proj_start + ' and cast(YearMonth+''-''+''01'' as date)  <=''' + @to +'''';

    SET @select_query_inactive = @select_query_inactive + ' and cast(YearMonth+''-''+''01'' as date)  >= ''' + @from +'''';
	SET @select_query_inactive = @select_query_inactive + ' and cast(YearMonth+''-''+''01'' as date)  <=''' + @to +'''';

    IF @sales_id != ''
        BEGIN
            SET @select_query_create = @select_query_create + ' AND (fcsh.sales_person_id IN ('+@sales_id+') OR fcsh.shared_with IN ('+@sales_id+')) ';
            SET @select_query_first_pkg_start = @select_query_first_pkg_start + ' AND (fcsh.sales_person_id IN ('+@sales_id+') OR fcsh.shared_with IN ('+@sales_id+')) ';
             SET @select_query_first_pkg_proj_start = @select_query_first_pkg_proj_start + ' AND (fact_customers.sales_person_id IN ('+@sales_id+') OR fact_customers.shared_with IN ('+@sales_id+')) ';
            SET @select_query_inactive = @select_query_inactive + ' AND (fact_customers.sales_person_id IN ('+@sales_id+') OR fact_customers.shared_with IN ('+@sales_id+')) ';
        end
    IF @region_type_id != ''
        BEGIN
            SET @select_query_create = @select_query_create + ' AND cus.region_type_id = ' + @region_type_id;
            SET @select_query_first_pkg_start = @select_query_first_pkg_start + ' AND cus.region_type_id = ' + @region_type_id;
            SET @select_query_first_pkg_proj_start = @select_query_first_pkg_proj_start + ' AND fact_customers.region_type_id = ' + @region_type_id;
            SET @select_query_inactive = @select_query_inactive + ' AND fact_customers.region_type_id = ' + @region_type_id;
        end
     IF @relationship_id != ''
        BEGIN
            SET @select_query_create = @select_query_create + ' AND cus.relationship_id IN (' + @relationship_id+')';
            SET @select_query_first_pkg_start = @select_query_first_pkg_start + ' AND cus.relationship_id IN (' + @relationship_id+')';
            SET @select_query_first_pkg_proj_start = @select_query_first_pkg_proj_start + ' AND fact_customers.relationship_id IN (' + @relationship_id+')';
            SET @select_query_inactive = @select_query_inactive + ' AND fact_customers.relationship_id IN (' + @relationship_id+')';
        end
--     IF  @tl_id != ''
--         BEGIN
--             SET @select_query_create =@select_query_create + ' AND tl_id = ' + @tl_id;
--             SET @select_query_first_pkg_start =@select_query_first_pkg_start + ' AND tl_id = ' + @tl_id;
--         END
    IF @client_grade != ''
        BEGIN
            SET @select_query_create = @select_query_create + ' AND cus.client_grade_id IN ('+@client_grade+')';
            SET @select_query_first_pkg_start = @select_query_first_pkg_start + ' AND cus.client_grade_id IN ('+@client_grade+')';
            SET @select_query_first_pkg_proj_start = @select_query_first_pkg_proj_start + ' AND fact_customers.client_grade_id IN ('+@client_grade+')';
            SET @select_query_inactive = @select_query_inactive + ' AND fact_customers.client_grade_id IN ('+@client_grade+')';
        end
    SET @select_query_create = @select_query_create + ' group by YearMonth';
    SET @select_query_first_pkg_start = @select_query_first_pkg_start + ' group by YearMonth';
    SET @select_query_first_pkg_proj_start = @select_query_first_pkg_proj_start + ' group by YearMonth';
    SET @select_query_inactive = @select_query_inactive + ' group by YearMonth';

    SET @select_query = 'SELECT YearMonth, SUM(customers_cnt) as cnt_by_create, SUM(customers_cnt_first_start) AS cnt_by_first_pkg, SUM(customers_cnt_first_pkg_proj_start) as cnt_by_first_pkg_proj, SUM(customers_cnt_inactive) as cnt_by_inactive FROM ('+@select_query_create+' UNION ALL '+@select_query_first_pkg_start+' UNION ALL '+@select_query_first_pkg_proj_start+' UNION ALL '+@select_query_inactive+') AS t GROUP BY t.YearMonth';
    EXEC sp_executesql    @select_query;
go

