janssen_ani_index_check.text

-----------------------------------------------------------------
--score_attribute_id = 22
-----------------------------------------------------------------
--OLD SQL
SELECT DISTINCT p.provider_id, jac.adboard AS ct    
FROM   mip_portal.dbo.provider AS p    
JOIN   mip_me.dbo.janssen_ani_cana AS jac ON (jac.provider_id = p.provider_id)    
JOIN   mip_portal.dbo.roi_campaign_project AS rcp ON (rcp.project_id = p.project_id)    
WHERE  rcp.roi_campaign_id = :roiCampaignId    AND    p.provider_created_date < :hx

--NEW SQL
SELECT DISTINCT p.provider_id, pes.provider_external_score_id AS ct FROM   mip_portal.dbo.provider AS p JOIN   mip_portal.dbo.provider_external_score AS pes ON (pes.provider_id = p.provider_id) JOIN   mip_portal.dbo.roi_campaign_project AS rcp ON (rcp.project_id = p.project_id) WHERE  rcp.roi_campaign_id = :roiCampaignId AND p.provider_created_date < :hx and pes.external_score_id = 1


-----------------------------------------------------------------
--score_attribute_id = 41
-----------------------------------------------------------------
--OLD SQL
    select provider_id, cast([RANK] as int) as ct   from mip_me.dbo.janssen_ani_cana  where [rank] > 0 and 0 < :roiCamapignId and '1/1/1900' < :hx

--NEW SQL
select p.provider_id, pes.score_value as ct from provider as p inner join provider_external_score as pes on pes.provider_id = p.provider_id inner join roi_campaign_project as rcp on rcp.project_id = p.project_id where pes.score_value > 0 and rcp.roi_campaign_id = :roiCampaignId and ''1/1/1900'' < :hx