CREATE PROCEDURE [dbo].[fetch_invoice_collection_by_month]
    @from nvarchar(max) = '2022-01-01',
    @to nvarchar(max) = '2022-12-31',
    @whereCondn nvarchar(max)= '',
    @salesId nvarchar(max) = '',
    @region_type_id nvarchar(max) = '',
    @client_grade nvarchar(max) = '',
    @relationship_id nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';
    declare @whereStmt nvarchar(max) = '';
    declare @whereStmtSub nvarchar(max) = '';
    IF  @salesId != ''
        BEGIN
            SET @whereStmt = @whereStmt + ' AND ( f.sales_person_id IN (' + @salesId + ') OR f.shared_with IN ('+ @salesId+'))';
            SET @whereStmtSub = @whereStmtSub + ' AND ( fp.sales_person_id IN (' + @salesId + ') OR fp.shared_with IN ('+ @salesId+'))';
        END
    IF  @region_type_id != ''
        BEGIN
            SET @whereStmt = @whereStmt + ' AND f.region_type_id='+@region_type_id;
            SET @whereStmtSub = @whereStmtSub + ' AND fp.region_type_id='+@region_type_id;
        END
    IF  @client_grade != ''
        BEGIN
            SET @whereStmt = @whereStmt + ' AND f.client_grade_id IN ('+@client_grade+')';
            SET @whereStmtSub = @whereStmtSub + ' AND fp.client_grade_id IN ('+@client_grade+')';
        END
    IF  @relationship_id != ''
        BEGIN
            SET @whereStmt = @whereStmt + ' AND f.relationship_id IN ('+@relationship_id+')';
            SET @whereStmtSub = @whereStmtSub + ' AND fp.relationship_id IN ('+@relationship_id+')';
        END
      SET @select_query = 'select f.DateKey,dim_date.YearMonth as yearmonth, sum(f.invoice_total) as invoice_raised, sum(f.invoice_basic) as invoice_raised_basic,
       sum(invoice_balance) as invoice_pending,sum(f.unbilled_amount) as unbilled, sum(f.collection_amount) as collection,sum(due_amount_billed) as due_amt_this_month,
       (select sum(invoice_balance) from fact_customers fp where fp.DateKey < f.DateKey '+@whereStmtSub+') as previous_invoice_pending,
        (select sum(unbilled_amount) from fact_customers fp where fp.DateKey < f.DateKey '+@whereStmtSub+') as previous_unbilled,' +
                        '(select sum(due_amount) from fact_customers fp where fp.DateKey < f.DateKey '+@whereStmtSub+') as due_amt_till_now,' +
                        '(select sum(due_amount_unbilled) from fact_customers fp where fp.DateKey <= f.DateKey '+@whereStmtSub+') as due_unbilled_amt_till_now,' +
                        '(select sum(due_amount+due_amount_unbilled) from fact_customers fp where fp.DateKey between dbo.convertDateToInt(dateadd(month,-2,CONVERT(DATE,CAST(f.DateKey AS varchar),112))) and f.DateKey '+@whereStmtSub+') as due_amt_last_quarter
from fact_customers as f join dim_date on dim_date.DateKey = f.DateKey where f.DateKey between dbo.convertDateToInt('''+@from+''') and dbo.convertDateToInt('''+@to+''') ' + @whereStmt

    SET @select_query = @select_query + ' group by f.DateKey, dim_date.YearMonth'
    EXEC sp_executesql    @select_query;
go

