----------------------
        --  Protocol Calc   --
        ----------------------
        DECLARE @FromYear nvarchar(4) = '2015', @FromMonth nvarchar(4) = '1', @ToYear nvarchar(4) = '2016', @ToMonth nvarchar(4) = '12', @ProtocolID int = 1,
        @GranularityLevel int = 3, @CategoryID int = 0, @AssetTypeID int = 0, @siteList nvarchar(100) = '40'
        , @defaultSite int, @i int
        Set @defaultSite = (SELECT TOP 1 * FROM splitstring(@siteList))
        DECLARE @years Table(year_id int)
        SET @i = @FromYear
        WHILE @i<=@ToYear
        BEGIN
          INSERT INTO @years VALUES (@i)
          SET @i = @i + 1
        END

        SELECT ISNULL(selected_parameters.site_id, @defaultSite) AS site_id, sites.site_name, protocols.protocol_short_name, ISNULL(categories.category_name, '') AS category_name, ISNULL(questions.q_category_id, categories.category_id) AS category_id, order_of_appearanceV2.oreder_id,
        ISNULL(sections.section_id, 0) AS section_id, ISNULL(questions.q_section, '') AS section_name, ISNULL(sub_sections.sub_section_id, 0) AS sub_section_id,
        ISNULL(sub_sections.sub_section_name, '') AS sub_section_name, questions.question_id, questions.external_question_id ,questions.question_text, questions.question_text_heb
        , questions.question_expression, questions.granularity_level_id, ISNULL (data_pointsV2.data_point_id, 0) AS data_point_id
        , data_pointsV2.data_point_text, data_pointsV2.data_point_text_he, ISNULL(data_pointsV2.response_type, 0) AS response_type
        , COALESCE(calc_methods.calc_method_name, '') AS calc_method_name, COALESCE(calc_methods.calc_method_name_heb, '') AS calc_method_name_heb
        , ISNULL(selected_parameters.asset_id, 0) AS asset_id, ISNULL(assets.asset_identifier, '') AS asset_external_id
        , COALESCE(assets.asset_name, '') as asset_name, COALESCE(asset_types.asset_type_name, '') AS asset_type, COALESCE(parent.asset_name, '') as parent_name
        , ISNULL(asset_extensions.destination_country, '') AS destination_country, ISNULL(asset_extensions.destination_name, '') AS destination_name, ISNULL(treatment_methods.name, '') AS treatment_method_name
        , ISNULL(dpValue.date_reported, CONVERT(DATETIME, '01/'+@ToMonth+'/'+CAST(year_id AS varchar(4)), 103)) AS date_reported , isNull(CAST(MONTH(dpValue.date_reported) as nvarchar(100)), @ToMonth) as [month]
        , isNull(CAST(YEAR(dpValue.date_reported) as nvarchar(100)), CAST(year_id AS varchar(4))) as [year], dpValue.internal_raw_value, originalUnits.unit_id as original_unit_id, secondLevelDP.internal_raw_value as second_level_dp_value, secondLevelUnits.unit_id as second_level_unit_id, firstLevelDp.internal_raw_value as first_level_dp_value, firstLevelUnits.unit_id as first_level_unit_id,
        COALESCE(originalUnits.unit_name,'') as original_unit_name, COALESCE(procedure_typesV2.procedure_type_id,'') as answerId, COALESCE(procedure_typesV2.procedure_type_name, '') as answerText,
        COALESCE(procedure_typesV2.response_text_hebrew,'') as response_text_hebrew, COALESCE(procedure_typesV2.response_score,0) as score, assets.asset_type_id, questions.reporting_unit_id,
        COALESCE(reportingUnits.unit_name,'') as reporting_unit_name, insights.insight_text, ISNULL(questions.threshold_value, 0) as threshold_value,
        STUFF((SELECT ', '+ comments.comment as [text()] from comments
        WHERE comments.site_id = dpValue.site_id AND comments.asset_id = dpValue.asset_id AND comments.data_point_id = dpValue.data_point_id AND comments.date_reported = dpValue.date_reported
        FOR XML PATH('')),1,1,'') as comments,
        STUFF((SELECT ', '+ files.original_file_name as [text()] from files
        WHERE files.site_id = selected_parameters.site_id AND files.asset_id = selected_parameters.asset_id AND files.data_point_id = selected_parameters.data_point_id AND files.date_reported = dpValue.date_reported
        FOR XML PATH('')),1,1,'') as reference
        , ISNULL(waste_catalog.attribute_value, '') AS waste_catalog_code, ISNULL(cas_number.attribute_value, '') AS cas_number, ISNULL(basel_codes.attribute_value, '') AS basel_code, ISNULL(haz_mat.attribute_value, '') AS haz_mat

        FROM questions
        LEFT JOIN data_points2question ON data_points2question.question_id = questions.question_id
        LEFT JOIN data_pointsV2 ON data_pointsV2.data_point_id = data_points2question.data_point_id
        LEFT JOIN sub_sections ON data_pointsV2.sub_section_id = sub_sections.sub_section_id
        LEFT JOIN sections ON sub_sections.section_id = sections.section_id
        LEFT JOIN categories ON categories.category_id = ISNULL(questions.q_category_id, sections.category_id)
        LEFT JOIN selected_parameters ON selected_parameters.data_point_id = data_pointsV2.data_point_id AND
            selected_parameters.response_format = data_pointsV2.response_format AND
            selected_parameters.site_id in (select * from dbo.splitstring(@siteList)) AND
            selected_parameters.category_id = Sections.category_id
        LEFT JOIN selected_assets ON selected_parameters.site_id = selected_assets.site_id
            AND selected_assets.category_id = selected_parameters.category_id
            AND selected_assets.asset_id = selected_parameters.asset_id
	        AND selected_assets.response_format = data_pointsV2.response_format
        LEFT JOIN data_entriesV2  as dpValue ON dpValue.site_id = selected_parameters.site_id AND
            dpValue.data_point_id = selected_parameters.data_point_id AND
            dpValue.asset_id = selected_parameters.asset_id AND
            dpValue.date_reported BETWEEN CONVERT(DATETIME, '01/'+@FromMonth+'/'+@FromYear, 103) AND CONVERT(DATETIME, '01/'+@ToMonth+'/'+@ToYear, 103)
        LEFT JOIN data_entriesV2  as parentDpValue ON parentDpValue.site_id = selected_parameters.site_id AND
            parentDpValue.data_point_id = data_pointsV2.dependent_data_point_id AND
            parentDpValue.asset_id = selected_parameters.asset_id AND
            parentDpValue.date_reported BETWEEN CONVERT(DATETIME, '01/'+@FromMonth+'/'+@FromYear, 103) AND CONVERT(DATETIME, '01/'+@ToMonth+'/'+@ToYear, 103)
        LEFT JOIN assets ON assets.asset_id = selected_parameters.asset_id
        LEFT JOIN assets AS parent ON parent.asset_id = assets.facility_id
        LEFT JOIN data_entriesV2 as secondLevelDP ON secondLevelDP.site_id = selected_parameters.site_id
	    AND secondLevelDP.data_point_id = selected_parameters.data_point_id
	    AND secondLevelDP.asset_id = parent.asset_id
	    AND secondLevelDP.date_reported BETWEEN CONVERT ( DATETIME, '01/' +@FromMonth + '/' +@FromYear, 103 )
	    AND CONVERT ( DATETIME, '01/' +@ToMonth + '/' +@ToYear, 103 )
	    LEFT JOIN data_entriesV2 as firstLevelDp ON firstLevelDp.site_id = selected_parameters.site_id
	    AND firstLevelDp.data_point_id = selected_parameters.data_point_id
	    AND firstLevelDp.asset_id = parent.facility_id
	    AND firstLevelDp.date_reported BETWEEN CONVERT ( DATETIME, '01/' +@FromMonth + '/' +@FromYear, 103 )
	    AND CONVERT ( DATETIME, '01/' +@ToMonth + '/' +@ToYear, 103 )
        LEFT JOIN procedure_typesV2 ON procedure_typesV2.data_point_id = data_pointsV2.data_point_id
        LEFT JOIN units AS originalUnits ON originalUnits.unit_id = dpValue.unit_id
        LEFT JOIN units AS secondLevelUnits ON secondLevelUnits.unit_id = secondLevelDP.unit_id
	    LEFT JOIN units AS firstLevelUnits ON firstLevelUnits.unit_id = firstLevelDp.unit_id
        LEFT JOIN units AS reportingUnits ON reportingUnits.unit_id = questions.reporting_unit_id
        LEFT JOIN order_of_appearanceV2 ON order_of_appearanceV2.entity_type = 'category'
	        AND order_of_appearanceV2.site_id = ISNULL(selected_parameters.site_id, @defaultSite)
	        AND order_of_appearanceV2.entity_id = categories.category_id
        LEFT JOIN insights ON insights.site_id = selected_parameters.site_id
	        AND insights.entity_type = 'question'
	        AND insights.insight_period = CONVERT(DATETIME, '01/01/'+CAST(YEAR(dpValue.date_reported) as nvarchar(4)), 103)
	        AND insights.entity_id = questions.question_id
        LEFT JOIN sites ON sites.site_id = ISNULL(selected_parameters.site_id, @defaultSite)
        LEFT JOIN protocols ON protocols.protocol_id = questions.protocol_id
        LEFT JOIN asset_types ON asset_types.asset_type_id = assets.asset_type_id
        LEFT JOIN asset_extensions ON asset_extensions.asset_id = selected_parameters.asset_id
        LEFT JOIN treatment_methods ON treatment_methods.asset_type = assets.asset_type_id
		          AND treatment_methods.id = asset_extensions.destination_treatment_method_id
        LEFT JOIN calc_methods ON calc_methods.calc_method_id = dpValue.calculation_method_id
        LEFT JOIN dp_attributes AS cas_number ON cas_number.attribute_type = 1
		          AND cas_number.dp_id = data_pointsV2.data_point_id
        LEFT JOIN dp_attributes AS waste_catalog ON waste_catalog.attribute_type = 2
		          AND waste_catalog.dp_id = data_pointsV2.data_point_id
        LEFT JOIN dp_attributes AS basel_codes ON basel_codes.attribute_type = 3
		          AND basel_codes.dp_id = data_pointsV2.data_point_id
        LEFT JOIN dp_attributes AS haz_mat ON haz_mat.attribute_type = 4
		          AND haz_mat.dp_id = data_pointsV2.data_point_id
        JOIN @years ON year_id BETWEEN
	          CASE WHEN ISNULL(questions.question_expression,'') = '' THEN YEAR(dpValue.date_reported) ELSE @FromYear END
	          AND
	          CASE WHEN ISNULL(questions.question_expression,'') = '' THEN YEAR(dpValue.date_reported) ELSE @ToYear END

        WHERE
          questions.protocol_id = @ProtocolID
        -- for response_type !=1 ignore selected asset as it does not exist
          AND (selected_assets.asset_id IS NOT NULL
		        OR selected_parameters.asset_id = 0
		        OR ISNULL(questions.question_expression, '') ! ='')
        -- Only DPs where there is no dependency or the conditional expression is true are counted
          AND (CASE WHEN data_pointsV2.dependent_data_point_id IS NULL OR data_pointsV2.dependent_data_point_id = 0 OR (convert(nvarchar(255), data_pointsV2.dependent_data_point_value ) IN (SELECT * FROM dbo.splitstring(parentDpValue.internal_raw_value)  ) ) THEN 1 ELSE 0 END) = 1

        -- For DPs of response_type radio_button, checkbox, select and multi-select, get the response text and score for the selected responses (as saved in internal_raw_value)
          AND (data_pointsV2.response_type IS NULL
		        OR data_pointsV2.response_type > 4
		        OR procedure_typesV2.procedure_type_id IN (select name from dbo.splitstring(dpValue.internal_raw_value))
	          )
        -- select DPs from a specific category or all categories
          AND (@CategoryID = 0 OR @CategoryID = sections.category_id)
        -- select assets from a specific asset_type or all assets
          AND (@AssetTypeID = 0 or @AssetTypeID = assets.asset_type_id)
          AND (isNull(CAST(YEAR(dpValue.date_reported) as nvarchar(100)), '') != '' OR ISNULL(questions.question_expression, '') ! ='')
        ORDER BY ISNULL(selected_parameters.site_id, @defaultSite), order_of_appearanceV2.oreder_id, sections.category_id, sections.section_id, data_pointsV2.sub_section_id, questions.question_id ,data_pointsV2.data_point_id, procedure_typesV2.procedure_type_id DESC