guidelineReportCheck.sql

use mip_portal
go

declare @roiCampaignId int = 52
--the three org site that does not have guideline provider, but has a status/guideline
--44091 -- Red Rock Fertility Center
--44003 -- Boston IVF - South Shore Center
--44112 -- Boston IVF - The Maine Center
declare @orgSiteId int = 44091

--check to see if org_site has guideline
select * from org_site_guideline where org_site_id = @orgSiteId

--check to see if there are any providers with the two role ids below (participate/expect)
select * from provider_org_site as pos
inner join provider_org_site_role as posr on posr.provider_org_site_role_id = pos.provider_org_site_role_id
inner join provider as p on p.provider_id = pos.provider_id
inner join roi_campaign_project as rcp on rcp.project_id = p.project_id
where pos.org_site_id = @orgSiteId
  and rcp.roi_campaign_id = @roiCampaignId
  and pos.provider_org_site_role_id in (4930,4931)