API tools faq
paste
nkarmi

Untitled

nkarmi
Sep 3rd, 2018
199
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.90 KB | None | 0 0
raw download clone embed print report
  1.  
  2. ----------------------
  3. -- Protocol Calc --
  4. ----------------------
  5. DECLARE @FromYear nvarchar(4) = '2017', @FromMonth nvarchar(4) = '1', @ToYear nvarchar(4) = '2018', @ToMonth nvarchar(4) = '12', @ProtocolID int = 13,
  6. @GranularityLevel int = 3, @CategoryID int = 0, @AssetTypeID int = 0, @siteList nvarchar(100) = '40', @AssetID int = 0
  7. , @defaultSite int, @i int
  8. Set @defaultSite = (SELECT TOP 1 * FROM splitstring(@siteList))
  9. DECLARE @years Table(year_id int)
  10. SET @i = @FromYear
  11. WHILE @i<=@ToYear
  12. BEGIN
  13. INSERT INTO @years VALUES (@i)
  14. SET @i = @i + 1
  15. END
  16.  
  17. 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,
  18. ISNULL(sections.section_id, 0) AS section_id, ISNULL(sections.section_name, '') AS section_name, ISNULL(sub_sections.sub_section_id, 0) AS sub_section_id,
  19. ISNULL(sub_sections.sub_section_name, '') AS sub_section_name, questions.question_id, questions.external_question_id ,questions.question_text, questions.question_text_heb
  20. , questions.question_expression, questions.granularity_level_id, ISNULL (data_pointsV2.data_point_id, 0) AS data_point_id
  21. , data_pointsV2.data_point_text, data_pointsV2.data_point_text_he, ISNULL(data_pointsV2.response_type, 0) AS response_type
  22. , COALESCE(calc_methods.calc_method_name, '') AS calc_method_name, COALESCE(calc_methods.calc_method_name_heb, '') AS calc_method_name_heb
  23. , ISNULL(selected_parameters.asset_id, 0) AS asset_id, ISNULL(assets.asset_identifier, '') AS asset_external_id
  24. , COALESCE(assets.asset_name, '') as asset_name, COALESCE(asset_types.asset_type_name, '') AS asset_type, COALESCE(parent.asset_name, '') as parent_name
  25. , ISNULL(asset_extensions.destination_country, '') AS destination_country, ISNULL(asset_extensions.destination_name, '') AS destination_name, ISNULL(treatment_methods.name, '') AS treatment_method_name
  26. , 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]
  27. , 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,
  28. COALESCE(originalUnits.unit_name,'') as original_unit_name, COALESCE(procedure_typesV2.procedure_type_id,'') as answerId, COALESCE(procedure_typesV2.procedure_type_name, '') as answerText,
  29. 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,
  30. COALESCE(reportingUnits.unit_name,'') as reporting_unit_name, insights.insight_text, ISNULL(questions.threshold_value, 0) as threshold_value,
  31. STUFF((SELECT ', '+ comments.comment as [text()] from comments
  32. 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
  33. FOR XML PATH('')),1,1,'') as comments,
  34. STUFF((SELECT ', '+ files.original_file_name as [text()] from files
  35. 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
  36. FOR XML PATH('')),1,1,'') as reference
  37. , ISNULL(waste_catalog.attribute_value, '') AS waste_catalog_code
  38. , ISNULL(cas_number.attribute_value, '') AS cas_number
  39. , ISNULL(cas_link.attribute_value, '') AS cas_link
  40. , ISNULL(ec_number.attribute_value, '') AS ec_number
  41. , ISNULL(ec_link.attribute_value, '') AS ec_link
  42. , ISNULL(basel_codes.attribute_value, '') AS basel_code
  43. , ISNULL(haz_mats.attribute_value, 'False') AS haz_mat
  44.  
  45. FROM questions
  46. LEFT JOIN data_points2question ON data_points2question.question_id = questions.question_id
  47. LEFT JOIN data_pointsV2 ON data_pointsV2.data_point_id = data_points2question.data_point_id
  48. LEFT JOIN sub_sections ON data_pointsV2.sub_section_id = sub_sections.sub_section_id
  49. LEFT JOIN sections ON sub_sections.section_id = sections.section_id
  50. LEFT JOIN categories ON categories.category_id = sections.category_id
  51. LEFT JOIN selected_parameters ON selected_parameters.data_point_id = data_pointsV2.data_point_id AND
  52. selected_parameters.response_format = data_pointsV2.response_format AND
  53. selected_parameters.site_id in (select * from dbo.splitstring(@siteList)) AND
  54. selected_parameters.category_id = Sections.category_id
  55. LEFT JOIN selected_assets ON selected_parameters.site_id = selected_assets.site_id
  56. AND selected_assets.category_id = selected_parameters.category_id
  57. AND selected_assets.asset_id = selected_parameters.asset_id
  58. AND selected_assets.response_format = data_pointsV2.response_format
  59. LEFT JOIN data_entriesV2 as dpValue ON dpValue.site_id = selected_parameters.site_id AND
  60. dpValue.data_point_id = selected_parameters.data_point_id AND
  61. dpValue.asset_id = selected_parameters.asset_id AND
  62. dpValue.date_reported BETWEEN CONVERT(DATETIME, '01/'+@FromMonth+'/'+@FromYear, 103) AND CONVERT(DATETIME, '01/'+@ToMonth+'/'+@ToYear, 103)
  63. LEFT JOIN data_entriesV2 as parentDpValue ON parentDpValue.site_id = selected_parameters.site_id AND
  64. parentDpValue.data_point_id = data_pointsV2.dependent_data_point_id AND
  65. parentDpValue.asset_id = selected_parameters.asset_id AND
  66. parentDpValue.date_reported BETWEEN CONVERT(DATETIME, '01/'+@FromMonth+'/'+@FromYear, 103) AND CONVERT(DATETIME, '01/'+@ToMonth+'/'+@ToYear, 103)
  67. LEFT JOIN assets ON assets.asset_id = selected_parameters.asset_id
  68. LEFT JOIN assets AS parent ON parent.asset_id = assets.facility_id
  69. LEFT JOIN data_entriesV2 as secondLevelDP ON secondLevelDP.site_id = selected_parameters.site_id
  70. AND secondLevelDP.data_point_id = selected_parameters.data_point_id
  71. AND secondLevelDP.asset_id = parent.asset_id
  72. AND secondLevelDP.date_reported BETWEEN CONVERT ( DATETIME, '01/' +@FromMonth + '/' +@FromYear, 103 )
  73. AND CONVERT ( DATETIME, '01/' +@ToMonth + '/' +@ToYear, 103 )
  74. LEFT JOIN data_entriesV2 as firstLevelDp ON firstLevelDp.site_id = selected_parameters.site_id
  75. AND firstLevelDp.data_point_id = selected_parameters.data_point_id
  76. AND firstLevelDp.asset_id = parent.facility_id
  77. AND firstLevelDp.date_reported BETWEEN CONVERT ( DATETIME, '01/' +@FromMonth + '/' +@FromYear, 103 )
  78. AND CONVERT ( DATETIME, '01/' +@ToMonth + '/' +@ToYear, 103 )
  79. LEFT JOIN procedure_typesV2 ON procedure_typesV2.data_point_id = data_pointsV2.data_point_id
  80. LEFT JOIN units AS originalUnits ON originalUnits.unit_id = dpValue.unit_id
  81. LEFT JOIN units AS secondLevelUnits ON secondLevelUnits.unit_id = secondLevelDP.unit_id
  82. LEFT JOIN units AS firstLevelUnits ON firstLevelUnits.unit_id = firstLevelDp.unit_id
  83. LEFT JOIN units AS reportingUnits ON reportingUnits.unit_id = questions.reporting_unit_id
  84. LEFT JOIN order_of_appearanceV2 ON order_of_appearanceV2.entity_type = 'category'
  85. AND order_of_appearanceV2.site_id = ISNULL(selected_parameters.site_id, @defaultSite)
  86. AND order_of_appearanceV2.entity_id = categories.category_id
  87. LEFT JOIN insights ON insights.site_id = selected_parameters.site_id
  88. AND insights.entity_type = 'question'
  89. AND insights.insight_period = CONVERT(DATETIME, '01/01/'+CAST(YEAR(dpValue.date_reported) as nvarchar(4)), 103)
  90. AND insights.entity_id = questions.question_id
  91. LEFT JOIN sites ON sites.site_id = ISNULL(selected_parameters.site_id, @defaultSite)
  92. LEFT JOIN protocols ON protocols.protocol_id = questions.protocol_id
  93. LEFT JOIN asset_types ON asset_types.asset_type_id = assets.asset_type_id
  94. LEFT JOIN asset_extensions ON asset_extensions.asset_id = selected_parameters.asset_id
  95. LEFT JOIN treatment_methods ON treatment_methods.asset_type = assets.asset_type_id
  96. AND treatment_methods.id = asset_extensions.destination_treatment_method_id
  97. LEFT JOIN calc_methods ON calc_methods.calc_method_id = dpValue.calculation_method_id
  98. LEFT JOIN dp_attributes AS cas_number ON cas_number.attribute_type = 1
  99. AND cas_number.dp_id = data_pointsV2.data_point_id
  100. LEFT JOIN dp_attributes AS waste_catalog ON waste_catalog.attribute_type = 2
  101. AND waste_catalog.dp_id = data_pointsV2.data_point_id
  102. LEFT JOIN dp_attributes AS basel_codes ON basel_codes.attribute_type = 3
  103. AND basel_codes.dp_id = data_pointsV2.data_point_id
  104. LEFT JOIN dp_attributes AS haz_mats ON haz_mats.attribute_type = 4
  105. AND haz_mats.dp_id = data_pointsV2.data_point_id
  106. LEFT JOIN dp_attributes AS cas_link ON cas_link.attribute_type = 5
  107. AND cas_link.dp_id = data_pointsV2.data_point_id
  108. LEFT JOIN dp_attributes AS ec_number ON ec_number.attribute_type = 6
  109. AND ec_number.dp_id = data_pointsV2.data_point_id
  110. LEFT JOIN dp_attributes AS ec_link ON ec_link.attribute_type = 7
  111. AND ec_link.dp_id = data_pointsV2.data_point_id
  112. JOIN @years ON year_id BETWEEN
  113. CASE WHEN ISNULL(questions.question_expression,'') = '' THEN YEAR(dpValue.date_reported) ELSE @FromYear END
  114. AND
  115. CASE WHEN ISNULL(questions.question_expression,'') = '' THEN YEAR(dpValue.date_reported) ELSE @ToYear END
  116.  
  117. WHERE
  118. questions.protocol_id = @ProtocolID
  119. -- for response_type !=1 ignore selected asset as it does not exist
  120. AND (selected_assets.asset_id IS NOT NULL
  121. OR selected_parameters.asset_id = 0
  122. OR ISNULL(questions.question_expression, '') ! ='')
  123. -- Only DPs where there is no dependency or the conditional expression is true are counted
  124. 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
  125.  
  126. -- 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)
  127. AND (data_pointsV2.response_type IS NULL
  128. OR data_pointsV2.response_type > 4
  129. OR procedure_typesV2.procedure_type_id IN (select name from dbo.splitstring(dpValue.internal_raw_value))
  130. )
  131. -- select DPs from a specific category or all categories
  132. AND (@CategoryID = 0 OR @CategoryID = sections.category_id)
  133. -- select assets from a specific asset_type or all assets
  134. AND (@AssetTypeID = 0 or @AssetTypeID = assets.asset_type_id)
  135. AND (isNull(CAST(YEAR(dpValue.date_reported) as nvarchar(100)), '') != '' OR ISNULL(questions.question_expression, '') ! ='')
  136. 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
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!