n-tran

USE mip_portal
GO

SET IDENTITY_INSERT question_group_class ON
INSERT INTO question_group_class (question_group_class_id, question_group_class_name,
                                  question_group_class_created_by, question_group_class_created_date,
                                  question_group_class_modified_by, question_group_class_modified_date)
VALUES (28, 'Influence Out', 'nghia.tran', GETDATE(), 'nghia.tran', GETDATE()),
       (29, 'Influence In', 'nghia.tran', GETDATE(), 'nghia.tran', GETDATE())
SET IDENTITY_INSERT question_group_class OFF

n-tran

use mip_portal
go

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)

n-tran

select provider_identifier_id
from provider_identifier pid
join provider p on p.provider_id = pid.provider_id
join 
(SELECT dr.project_id, drid.identifier_type_id, drid.provider_identifier_identity
     FROM provider_identifier drid
          left outer JOIN provider dr ON (dr.provider_id = drid.provider_id)
                  WHERE identifier_type_id <> 7
     GROUP BY dr.project_id, drid.identifier_type_id, drid.provider_identifier_identity
     HAVING COUNT(*) > 1) as prob on prob.project_id = p.project_id

n-tran

use mip_portal
go

declare @roiCampaignId int = 43
declare @geoSetId int = 147
declare @geoId int = 399359
declare @providerSpecialtyId int = 191

select distinct p.provider_id
      ,p.provider_name_first, p.provider_name_last, p.provider_name_middle

n-tran

use stage
go

    
select distinct left(g.rdt,2) as regon, g.rdtname--, m.ZIP
from d_janssenGeoMap as g
join (select LEFT([1q2013],2) as region, rdtname, zip
      from d_janssenZipMap) as m on m.region = left(g.rdt,2)
      and g.RDTname = m.RDTName
order by 2

n-tran

use mip_portal
go

drop table rx_product_level
go
 
drop table rx_product
go
 
drop table provider_rx

n-tran

use mip_portal
go

declare @roiCampaignId int = 1
declare @surveyQuestionId int = 8143
declare @geoId int = 397153
declare @geoSetId int = 137

select p.provider_id
      ,p.provider_name_last, p.provider_name_first, p.provider_name_middle, p.provider_name_suffix

n-tran

use mip_dw
go

declare @roiCampaignId int = 1
declare @surveyQuestionId int = 8143
declare @geoId int = 397153
declare @geoSetId int = 137

SELECT drl.provider_name, drl.provider_id, drl.provider_name_last, drl.provider_name_first, drl.provider_name_middle,
  drl.provider_name_suffix, drl.provider_phone, drl.provider_email, drl.provider_addr_line_1, drl.provider_addr_line_2,

n-tran

use mip_portal
go
--chicago
select d.*
      ,p.provider_id
      ,p.primary_addr_line_1
      ,p.provider_default_score
      ,p.provider_phone
      ,p.provider_email
from stage.dbo.d_chicagoMeeting as d

n-tran

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