CREATE PROCEDURE dbo.fetch_city_wise_demands
(
@from nvarchar(max) = '2022-01-01',
@to nvarchar(max) = '2022-12-01',
@tech_id nvarchar(max) = '',
@bu_id nvarchar(max) = '',
@demand_status_id varchar(100) = '',
@sales_id nvarchar(max) = '',
@month_array varchar(255) = '"2023-01","2023-02","2022-03","2022-04","2022-05","2022-06","2022-07","2022-08","2022-09","2022-10","2022-11","2022-12"'
)
AS

declare @select_query nvarchar(max) = '';

SET @select_query = 'select * from (
select cm.city_id,
cm.city_name,YearMonth,
req_id
from dim_cities as cm
inner join fact_demand as pm on pm.city_id = cm.city_id
left join dim_date dd on dd.DateKey = pm.created_on
    where deleted_on is null
    and (YearMonth+''-01'') between (''';

SET @select_query = @select_query + @from +''') and (''';
SET @select_query = @select_query + @to +''')';

    IF  @tech_id != ''
        BEGIN
            SET @select_query = @select_query + ' AND tech_id in (' + @tech_id + ')';
        END
    IF  @bu_id != ''
        BEGIN
            SET @select_query =@select_query + ' AND bu_id = ' + @bu_id;
        END
    IF  @demand_status_id != ''
        BEGIN
            SET @select_query =@select_query + ' AND dim_status_id = ' + @demand_status_id;
        END

    IF @sales_id != ''
        BEGIN
            SET @select_query =@select_query + ' AND sales_id IN (' + @sales_id + ')';
        END

SET @select_query = @select_query + ') as s
PIVOT (COUNT(req_id) for YearMonth in ('+ @month_array +')) as p
order by city_name asc'

--PRINT @select_query

EXEC sp_executesql @select_query
go

