CREATE PROCEDURE [dbo].[fetch_package_stats]
    @packageId nvarchar(max)= '0'
AS
    declare @select_query nvarchar(max) = '';
    SET @select_query = 'select (CAST(startDate.Day AS VARCHAR) + '' ''+startDate.MonthName_Short+'' ''+CAST(startDate.Year AS VARCHAR)) as start_date,
       (CAST(endDate.Day AS VARCHAR) + '' ''+endDate.MonthName_Short+'' ''+CAST(endDate.Year AS VARCHAR)) as end_date, package_stats.status_name,' +
                        'p.start_note,dis_reason.dc_name as discontinue_reason,p.end_note,
       sec_reason.reason_title as secondary_discontinue_reason,
p.is_lost_entire,p.dc_other_reason
from dim_customer_package as p
left join dim_date as startDate on startDate.DateKey = p.start_date
left join dim_date as endDate on endDate.DateKey = p.end_date
left join dim_package_status as package_stats on package_stats.status_id = p.package_status_id ' +
                        'left join dim_package_discontinue_reasons as dis_reason on dis_reason.dc_id = p.dc_reason_id
left join dim_package_discontinue_secondary_reasons as sec_reason on sec_reason.dcr_id = p.dc_sub_reason_id
where parent_id = '+@packageId+' order by start_date'
    EXEC sp_executesql    @select_query;
go

