Thanks I believe this will work but I am getting an error and I am not sure how to fix this.
Msg 156, Level 15, State 1, Line 301
Incorrect syntax near the keyword 'Left'.
Msg 319, Level 15, State 1, Line 301
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Line 305
Incorrect syntax near 'c'.
Msg 102, Level 15, State 1, Line 320
Incorrect syntax near 'P1'.
Msg 102, Level 15, State 1, Line 335
Incorrect syntax near 'P2'.
Msg 102, Level 15, State 1, Line 348
Incorrect syntax near 'P3'.
Msg 102, Level 15, State 1, Line 362
Incorrect syntax near 'P4'.
Msg 102, Level 15, State 1, Line 375
Incorrect syntax near 'P5'.
Msg 102, Level 15, State 1, Line 381
Incorrect syntax near 'A'.
Here is the joins below.......
FROM impact.dbo.tmpHumanaEffectiveDates t1
LEFT OUTER JOIN impact.dbo.pcst t2
ON t1.pcsp_id1 = t2.pcst_id1
Right OUTER JOIN------- took out pcsp_trm is null 5-26-11 now other records are showing.
impact.dbo.pcs t3 ON t1.pcsp_id1 = t3.pcs_id1 and t3.pcs_ctl <> 'X' --t1.pcsp_trm is null and
LEFT OUTER JOIN impact.dbo.pcsa t7
ON t7.pcsa_id1 = t3.pcs_id1
Left OUTER JOIN impact.dbo.pcsb t8
on pcsb_id1 = t3.pcs_id1
left join impact.dbo.pro
on t7.pcsa_pro = pro_id1
inner join impact.dbo.prop
on t7.pcsa_pro = prop_id1 and prop_trm is null
left join impact.dbo.pcst t5
ON t1.pcsp_id1 = t5.pcst_id1
and t5.PCST_WHAT ='RECRED' and t5.pcst_act2 is NULL ----- Future Date
LEFT JOIN impact.dbo.valid s1 with (nolock)
ON t3.pcs_spec1 = s1.val_code and s1.val_type = '302'--Spec1
LEFT JOIN impact.dbo.valid s2 with (nolock)
ON t3.pcs_spec2 = s2.val_code and s2.val_type = '302' --Spec2
Left JOIN impact.dbo.valid IPA1 with (nolock)
ON Prop_SYS = IPA1.val_code and IPA1.val_type = '367' ---IPA
;WITH cte AS (select pcsb_id1
,val_desc
,max(PCSB_TRM1) as PCSB_TRM1
,row_number() over (partition by pcsb_id1
,val_desc
order by PCSB_TRM1 desc
) as rn
from impact.dbo.PCSB as a
left join impact.dbo.vw_VALID_471 as b
on b.val_code=a.pcsb_what
group by pcsb_id1,val_desc,pcsb_trm1
)
select a.pcsb_id1 as PCS_id1
,a.val_desc as [Board Cert1]
,a.pcsb_trm1 as [Experation Date-BC1]
,b.val_desc as [Board Cert2]
,b.pcsb_trm1 as [Experation Date-BC2]
,c.val_desc as [Board Cert3]
,c.pcsb_trm1 as [Experation Date-BC3]
,d.val_desc as [Board Cert4]
,d.pcsb_trm1 as [Experation Date-BC4]
,e.val_desc as [Board Cert5]
,e.pcsb_trm1 as [Experation Date-BC5]
from cte as a
left outer join cte as b
on b.pcsb_id1=a.pcsb_id1
and b.rn=2
left outer join cte as c
on c.pcsb_id1=a.pcsb_id1
and c.rn=3
left outer join cte as d
on d.pcsb_id1=a.pcsb_id1
and d.rn=4
left outer join cte as e
on e.pcsb_id1=a.pcsb_id1
and e.rn=5
Where a.rn=1
;
----Left JOIN impact.dbo.vw_VALID_471 BCERT with (nolock)
-----ON pcsb_what = BCERT.val_code --Board Cert
Left JOIN impact.dbo.vw_VALID_316 Protyp with (nolock)
ON t3.pcs_xtyp = protyp.val_code -----ProviderType
Left join (Select Distinct pcsa_id1 as correspondanceID, pcsa_addr1 as correspondanceAddress, pcsa_addr2 as correspondanceSuite,pcsa_city as correspondanceCity, pcsa_state as correspondanceState, pcsa_zip as correspondanceZIP,pcsa_phone as CorrespondancePhone, pcsa_fax as CorrespondanceFax , pcsa_tax1 as CorrespondanceTAX From impact.dbo.pcsa where pcsa_trst = 'CORRES' and (pcsa_trm is null or pcsa_trm = '' or pcsa_trm = NULL)) c on t7.pcsa_id1 = c.correspondanceID and t7.pcsa_tax1 = c.CorrespondanceTAX
/-----=========================================================
MAX Board Decision Made Date - Current updated(7/18/2013)
-----=========================================================/
Left Join (select pcst_id1,MaxboardDecisionMade, [Cred Type - Last Board Decision Made Date]
from
(
select pcst_id1, pcst_dat2 as MaxboardDecisionMade, pcst_what as [Cred Type - Last Board Decision Made Date],
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
)t
where r= 1)P1 on t2.pcst_id1 = P1.pcst_id1
/-----=========================================================
MAX Previous Board Decision Made Date - Current updated(7/18/2013)
-----=========================================================/
Left Join (select pcst_id1,MaxPreviousboardDecisionMade
from
(
select pcst_id1, pcst_dat2 as MaxPreviousboardDecisionMade,
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
)t
where r= 2)P2 on t2.pcst_id1 = P2.pcst_id1
/-----=========================================================
MAX Board Decision Made Date Other 3 - Current updated(7/18/2013)
-----=========================================================/
Left Join (select pcst_id1,MaxBoardDecisionMadeOther3
from
(
select pcst_id1, pcst_dat2 as MaxBoardDecisionMadeOther3,
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
)t
where r= 3)P3 on t2.pcst_id1 = P3.pcst_id1
/-----=========================================================
MAX Board Decision Made Date Other 4 - Current updated(7/18/2013)
-----=========================================================/
Left Join (select pcst_id1,MaxBoardDecisionMadeOther4
from
(
select pcst_id1, pcst_dat2 as MaxBoardDecisionMadeOther4,
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
)t
where r= 4)P4 on t2.pcst_id1 = P4.pcst_id1
/-----=========================================================
MAX Board Decision Made Date Other 5 - Current updated(7/18/2013)
-----=========================================================/
Left Join (select pcst_id1,MaxBoardDecisionMadeOther5
from
(
select pcst_id1, pcst_dat2 as MaxBoardDecisionMadeOther5,
row_number() over (partition by pcst_id1 order by pcst_dat2 desc) As r
from impact.dbo.pcst
)t
where r= 5)P5 on t2.pcst_id1 = P5.pcst_id1
/-----=========================================================
[First_Board_Decision_Made_Date]
-----=========================================================/
Left Join (select pcst_id1, MIN(pcst_dat2) as [First_Board_Decision_Made_Date] from impact.dbo.pcst group by pcst_id1) A
on t2.pcst_id1 = A.pcst_id1
WHERE
t3.pcs_ctl <> 'X' and
---t7.pcsa_ctl <> 'X' and
prop_prd is not null and prop_prd = 'DGH' and ------IN('DGH','ECN','ESN','IMA','IMW','MVA','WCP') and -----and 8/27/2014 took out because Felicia wants all Products to appear.
t7.pcsa_state IN('PA','DE')and
pcsa_trst <> 'INPROC' and
s1.val_desc NOT IN('PART-TIME PRECEPTOR','LOCUM TENEN')