CREATE PROCEDURE [dbo].[fetch_monthly_sales_by_bu]
    @from nvarchar(max) = '2022-01-01',
    @to nvarchar(max) = '2022-12-31',
    @sales_id nvarchar(max) = '',
    @region_type_id nvarchar(max) = '',
    @client_grade nvarchar(max) = '',
    @tech_type nvarchar(max) = 'pkg',
    @bu_id nvarchar(max) = '',
    @tech_id nvarchar(max) = '',
    @relationship_id nvarchar(max) = ''
AS
    declare @select_query nvarchar(max) = '';
    declare @whereCondn nvarchar(max) = '';
    declare @teamFilterCondn nvarchar(max) = '';
    declare @kpiTableName nvarchar(max) = 'kpi_monthly_package_summary_by_pkg_technology';
    declare @techIdColumn nvarchar(max) = 'pkg_tech_id';
    declare @techBuIdColumn nvarchar(max) = 'pkg_tech_bu_id';
    IF @tech_type = 'res'
        BEGIN
            SET @kpiTableName = 'kpi_monthly_package_summary_by_resource_technology'
            SET @techIdColumn = 'res_tech_id'
            SET @techBuIdColumn = 'res_tech_bu_id'
        end
    IF @region_type_id != ''
        BEGIN
            SET @whereCondn = @whereCondn + ' and region_type_id = '+@region_type_id
        end
    IF @client_grade != ''
        BEGIN
            SET @whereCondn = @whereCondn + ' and client_grade = '+@client_grade
        end
    IF @relationship_id != ''
        BEGIN
            SET @whereCondn = @whereCondn + ' and relationship_id IN ('+@relationship_id+')'
        end
 IF @bu_id != '' and @tech_type = 'pkg'
        BEGIN
            SET @whereCondn = @whereCondn + ' and pkg_tech_bu_id = '+@bu_id
        end
    IF @bu_id != '' and @tech_type = 'res'
        BEGIN
            SET @whereCondn = @whereCondn + ' and res_tech_bu_id = '+@bu_id
        end


     IF @tech_id != '' and @tech_type = 'pkg'
        BEGIN
            SET @whereCondn = @whereCondn + ' and pkg_tech_id IN ('+@tech_id+')'
        end
    IF @tech_id != '' and @tech_type = 'res'
        BEGIN
            SET @whereCondn = @whereCondn + ' and res_tech_id IN ('+@tech_id+')'
        end

    IF  @sales_id != ''
        BEGIN
            SET @whereCondn  = @whereCondn  + ' AND ( sales_person_id IN (' + @sales_id + ') OR shared_with IN ('+ @sales_id+'))';
        END
    SET @select_query = 'select yearmonth,
       '+@techBuIdColumn+' as bu_id,
       dim_bu.bu_title,
       SUM(package_cnt) as package_cnt,
       SUM(total_actual_billed_amt+total_actual_unbilled_amt) as amt
from '+@kpiTableName+' as k
left join dim_bu on dim_bu.bu_id = k.'+@techBuIdColumn+'
where cast(yearmonth + ''-'' + ''01'' as date) between '''+@from+''' and '''+@to+''''+@whereCondn+'
group by yearmonth,'+@techBuIdColumn+', dim_bu.bu_title order by yearmonth,'+@techBuIdColumn
   EXEC sp_executesql    @select_query;
go

