set statistics time on

                DECLARE @month nvarchar(2) = '1', @year nvarchar(4) = '2018', @siteId int = 73, @responseFormat int = 1
                , @categoryId int = 13, @assetId int  = 1983
                DECLARE @date DATETIME = CONVERT(DATETIME, '01/'+@month+'/'+@year, 103)

                SELECT data_entriesV2.date_reported, sp.asset_id, assets.asset_name, categories.category_id, categories.category_name, categories.class_name
                , sections.section_id, sections.section_name, mainDt.sub_section_id, sub_sections.sub_section_name, sub_sections.sub_section_name_he
                , mainDt.data_point_id, mainDt.data_point_text , mainDt.data_point_text_he ,mainDt.response_type, mainDt.response_format
                , procedure_typesV2.procedure_type_id AS answer_id , procedure_typesV2.procedure_type_name AS answer_name
                , procedure_typesV2.response_text_hebrew AS answer_name_heb, CAST(CHARINDEX(LTRIM(STR(procedure_typesV2.procedure_type_id)), data_entriesV2.internal_raw_value, 1) as BIT) as selected_answer
                , unit_groups.unit_id ,units.unit_name, CAST(CASE WHEN data_entriesV2.unit_id = units.unit_id THEN 1 ELSE 0 END AS BIT) as selected_unit
                , data_entriesV2.unit_id as selected_id, data_entriesV2.calculation_method_id, data_entriesV2.calc_comment
                , data_entriesV2.internal_raw_value as value, @date as date_reported, mainDt.dependent_data_point_id, mainDt.dependent_data_point_value
                , units.unit_name, ISNULL(unit_groups.is_default, 0) as selected_default_unit, preselected_calc_method.calc_method_id as preselected_calc_method
                , (SELECT unit_groups.unit_id
                    FROM unit_groups
                    WHERE unit_groups.group_id = mainDt.UoM_group AND unit_groups.is_default = 1 ) AS default_unit_id
                , (SELECT de.unit_id FROM data_entriesV2 AS de
                    WHERE de.site_id = @siteId
                        AND de.data_point_id = mainDt.data_point_id
                        AND de.asset_id = @assetId
                        AND date_reported = DATEADD(yy, DATEDIFF(yy, 0, @date), 0)
                    ) AS first_month_unit_id
                , CAST(CASE WHEN files.data_point_id = mainDt.data_point_id THEN 1 ELSE 0 END AS BIT) as has_file_updated
                , CAST(CASE WHEN comments.data_point_id = mainDt.data_point_id THEN 1 ELSE 0 END AS BIT) as has_comments

                FROM data_pointsV2 as mainDt
                JOIN selected_parameters AS sp ON mainDt.data_point_id = sp.data_point_id AND sp.asset_id = @assetId AND sp.site_id = @siteId
                    AND sp.category_id = @categoryId AND sp.response_format = mainDt.response_format
                JOIN sub_sections ON sub_sections.sub_section_id = mainDt.sub_section_id
                JOIN sections ON sections.section_id = sub_sections.section_id
                JOIN categories ON categories.category_id = sections.category_id
                LEFT JOIN procedure_typesV2 ON procedure_typesV2.data_point_id = mainDt.data_point_id
                LEFT JOIN data_entriesV2 ON mainDt.data_point_id = data_entriesV2.data_point_id AND data_entriesV2.date_reported = @date
                    AND data_entriesV2.site_id = sp.site_id  AND data_entriesV2.asset_id = sp.asset_id
                LEFT JOIN unit_groups ON unit_groups.group_id = mainDt.UoM_group
                LEFT JOIN units ON units.unit_id = unit_groups.unit_id
                LEFT JOIN assets ON assets.asset_id = sp.asset_id
                LEFT JOIN files on files.data_point_id = mainDt.data_point_id AND files.asset_id = @assetId AND files.site_id = @siteId AND files.date_reported = @date
                LEFT JOIN comments on comments.data_point_id = mainDt.data_point_id AND comments.asset_id = @assetId AND comments.site_id = @siteId AND comments.date_reported = @date
                OUTER APPLY
                (
                 SELECT TOP 1 preselected_de.calculation_method_id as calc_method_id
                 FROM data_entriesV2 AS preselected_de
                 WHERE preselected_de.site_id = @siteId
                        AND preselected_de.data_point_id = mainDt.data_point_id
                        AND preselected_de.asset_id = @assetId
                        AND preselected_de.calculation_method_id != 0
                        AND preselected_de.date_reported <= @date
                 ORDER BY preselected_de.date_reported DESC
                 ) AS preselected_calc_method
                WHERE mainDt.response_format = @responseFormat AND
                sections.category_id = @categoryId
                ORDER BY sections.section_id, mainDt.sub_section_id ,mainDt.order_of_appearance ,mainDt.data_point_id, procedure_typesV2.procedure_type_id DESC

set statistics time off