n-tran

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.*

n-tran

use stage
go
  
select *
from d_originalJanssenInvokana as c
where not exists (select * from d_janssenInvokana as v
                  where v.firstName = c.[First Name]
                    and v.lastName = c.[last name]
                    and v.add1 = c.[Business Address 1]
                    and v.add2 = c.[Business Address 2]

n-tran

use mip_portal
go

select geo_name, rtrim(ltrim(REPLACE(geo_name,'"','')))
from geo
where geo_set_id = 169
order by geo_name


--------------------------------------------

n-tran

use stage
go
  
select *
from d_janssenBday
where ProviderID not in (select provider_id from mip_portal.dbo.provider where project_id = 38)

n-tran

use mip_portal
go


select cts.project_id
      ,cts.clinical_trial_set_name
      ,count(ct.clinical_trial_id) as ctCount
from clinical_trial_set as cts
inner join clinical_trial as ct on ct.clinical_trial_set_id = cts.clinical_trial_set_id
where cts.project_id in (38,48,37)

n-tran

use stage
go


insert into mip_portal.dbo.response
select null as org_site_id, null as provider_id
      ,ps.provider_survey_id
      ,case when piv.surveyQuestionId in (10178,10179,10180,10181,10182) and piv.response = 1 then 'True'
            when piv.surveyQuestionId in (10178,10179,10180,10181,10182) and piv.response = 0 then 'False'
            when piv.surveyQuestionId in (10193) and piv.response in (1,2) then 'True'

n-tran

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

n-tran

use mip_portal
go

select distinct p.provider_id
      ,p.provider_name_first, p.provider_name_last
      ,p.primary_addr_line_1, p.primary_addr_line_2
      ,p.primary_addr_city, p.primary_addr_realm, p.primary_addr_zip
      ,p.provider_phone, p.provider_fax, p.provider_email
      ,p.provider_default_score as iv_score
      ,pct.ct_count

n-tran

use mip_portal
go

select mip.provider_id, hp.[S No], hp.NPI, hp.[First Name], hp.[Last Name], hp.[Address]
      ,hp.City, hp.ST, mip.primary_addr_zip, hm.SITEID, hm.org_site_id, hm.MATCH_LEVEL
from stage.dbo.hds_provider hp
inner join (select distinct pid.provider_identifier_identity,p.provider_id, p.primary_addr_zip
			from provider p
			inner join provider_identifier pid on pid.provider_id = p.provider_id
			where p.project_id = 51

n-tran

select *
from score s
inner join roi_campaign_score rcs on rcs.score_id = s.score_id
inner join roi_campaign_score_type rcst on rcst.roi_campaign_score_type_id = rcs.roi_campaign_score_type_id
inner join roi_campaign_score_weight rcsw on rcsw.roi_campaign_score_id = rcs.roi_campaign_score_id
inner join score_score_attribute ssa on ssa.score_attribute_id = rcsw.score_attribute_id
where rcs.roi_campaign_id in (55)