crcGuidelines.sql

use mip_portal
go

declare @projectId int = 37

select p.provider_name_last + ', ' + p.provider_name_first as providerName
      ,p.primary_addr_line_1, p.primary_addr_line_2, p.primary_addr_line_3
      ,p.primary_addr_city, p.primary_addr_realm, p.primary_addr_zip
      ,p.provider_email, p.provider_phone
      ,pos.*
      ,q.question_text
      ,r.response_answer
from provider as p
inner join provider_survey as ps on ps.provider_id = p.provider_id
inner join response as r on r.provider_survey_id = ps.provider_survey_id
inner join survey_question as sq on sq.survey_question_id = r.survey_question_id
inner join question as q on q.question_id = sq.question_id
inner join question_group as qg on qg.question_group_id = q.question_group_id
--get primary org info
inner join (select pos.provider_id
                  ,o.org_name, o.org_id
                  ,os.org_site_name, os.org_site_id
                  ,pos.provider_org_site_title
                  ,posr.provider_org_site_role_name
            from provider_org_site as pos
            inner join org_site as os on os.org_site_id = pos.org_site_id
            inner join provider_org_site_role as posr on posr.provider_org_site_role_id = pos.provider_org_site_role_id
            inner join organization as o on os.org_id = o.org_id
            inner join provider as p on p.provider_id = pos.provider_id
            where pos.provider_org_site_primary = 1
              and p.project_id = @projectId) as pos on pos.provider_id = p.provider_id
where p.project_id = @projectId
  and qg.question_group_comp_id = 7 --guidelines