BDM_7

USE [FP&A]
GO
/****** Object:  StoredProcedure [dbo].[CPACombinedReportData]    Script Date: 2/6/2025 11:06:52 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROC [dbo].[CPACombinedReportData] AS
BEGIN

set transaction isolation level read uncommitted
/*
drop table if exists #Round0;
drop table if exists #Round1;
drop table if exists #Round2;
drop table if exists #Round3;
drop table if exists #Round4;
drop table if exists #Round5;
drop table if exists #Round6;
drop table if exists #Round7;
drop table if exists #Round8;
drop table if exists #Round9;
drop table if exists #Round10;
drop table if exists #Round11;
drop table if exists #Round12;
drop table if exists #Round13;
drop table if exists #Round14;
drop table if exists #Round15;
drop table if exists #Round16;
drop table if exists #Round17;
drop table if exists #Round18;
drop table if exists #Round19;


SELECT CM.CPA_NAME,CF.CPAFirmName,CF.AccountID,CPAFirmIndex
INTO #Round0
FROM
(
SELECT CPA_NAME,MatterNUmber
FROM
[fp&a]..CPAMatch CM
WHERE
ARMIndex IN (
SELECT ARMIndex
FROM
[fp&a]..CPAMatch
GROUP BY ARMIndex
HAVING COUNT(ARMIndex) = 1 AND SUM(COALESCE(TRY_CAST(AMOUNTDUETOCPA as numeric),0))>0)
GROUP BY  CPA_NAME,MatterNUmber
) CM
JOIN
TE_3E_PROD_Clone..Matter M on M.NUmber COLLATE SQL_Latin1_General_CP1_CI_AS = CM.MatterNUmber
JOIN
(SELECT Matterlkup,MAX(CPAFirm) CPAFirm
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY MatterLkup
HAVING COUNT(DISTINCT CPAFirm)=1
)
MCF on MCF.MatterLkup = M.MattINdex
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmIndex = MCF.CPAFirm
GROUP BY
CM.CPA_NAME,CPAFirmName,CF.AccountID,CPAFirmIndex

SELECT CM.CPA_NAME,CF.CPAFirmName,CF.AccountID,CPAFirmIndex
INTO #Round1
FROM
(
SELECT CPA_NAME,MatterNUmber
FROM
[fp&a]..CPAMatch CM
WHERE
ARMIndex IN (
SELECT ARMIndex
FROM
[fp&a]..CPAMatch
WHERE
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round0)
GROUP BY ARMIndex
HAVING COUNT(ARMIndex) = 1)
AND
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round0)
GROUP BY  CPA_NAME,MatterNUmber
) CM
JOIN
TE_3E_PROD_Clone..Matter M on M.NUmber COLLATE SQL_Latin1_General_CP1_CI_AS = CM.MatterNUmber
JOIN
(SELECT Matterlkup,MAX(CPAFirm) CPAFirm
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY MatterLkup
HAVING COUNT(DISTINCT CPAFirm)=1
)
MCF on MCF.MatterLkup = M.MattINdex
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmIndex = MCF.CPAFirm
GROUP BY
CM.CPA_NAME,CPAFirmName,CF.AccountID,CPAFirmIndex



SELECT CM.CPA_NAME,CF.CPAFirmName,CF.AccountID,CPAFirmIndex
INTO #Round2
FROM
(
SELECT CPA_NAME,MatterNUmber
FROM
[fp&a]..CPAMatch CM
WHERE
ARMIndex IN (
SELECT ARMIndex
FROM
[fp&a]..CPAMatch
WHERE
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY ARMIndex
HAVING COUNT(ARMIndex) = 1)
AND
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY  CPA_NAME,MatterNUmber
) CM
JOIN
TE_3E_PROD_Clone..Matter M on M.NUmber COLLATE SQL_Latin1_General_CP1_CI_AS = CM.MatterNUmber
JOIN
(SELECT Matterlkup,MAX(CPAFirm) CPAFirm
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
WHERE
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round1)
GROUP BY MatterLkup
HAVING COUNT(DISTINCT CPAFirm)=1
)
MCF on MCF.MatterLkup = M.MattINdex
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmIndex = MCF.CPAFirm
GROUP BY
CM.CPA_NAME,CPAFirmName,CF.AccountID,CPAFirmIndex


SELECT CM.CPA_NAME,CF.CPAFirmName,CF.AccountID,CPAFirmIndex
INTO #Round3
FROM
(
SELECT CPA_NAME,MatterNUmber
FROM
[fp&a]..CPAMatch CM
WHERE
ARMIndex IN (
SELECT ARMIndex
FROM
[fp&a]..CPAMatch
WHERE
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round2) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY ARMIndex
HAVING COUNT(ARMIndex) = 1)
AND
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round2) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY  CPA_NAME,MatterNUmber
) CM
JOIN
TE_3E_PROD_Clone..Matter M on M.NUmber COLLATE SQL_Latin1_General_CP1_CI_AS = CM.MatterNUmber
JOIN
(SELECT Matterlkup,MAX(CPAFirm) CPAFirm
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
WHERE
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round2) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round1)
GROUP BY MatterLkup
HAVING COUNT(DISTINCT CPAFirm)=1
)
MCF on MCF.MatterLkup = M.MattINdex
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmIndex = MCF.CPAFirm
GROUP BY
CM.CPA_NAME,CPAFirmName,CF.AccountID,CPAFirmIndex


SELECT CM.CPA_NAME,CF.CPAFirmName,CF.AccountID,CPAFirmIndex
INTO #Round4
FROM
(
SELECT CPA_NAME,[CLIENT ID]
FROM
[fp&a]..CPAMatch CM
WHERE
ARMIndex IN (
SELECT ARMIndex
FROM
[fp&a]..CPAMatch
WHERE
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round3) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round2) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY ARMIndex
HAVING COUNT(ARMIndex) = 1)
AND
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round3) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round2) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY  CPA_NAME,[CLIENT ID]
) CM
JOIN
TE_3E_PROD_Clone..Matter M on M.Client = CM.[CLIENT ID]
JOIN
(SELECT Matterlkup,MAX(CPAFirm) CPAFirm
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
WHERE
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round3) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round2) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round1)
GROUP BY MatterLkup
HAVING COUNT(DISTINCT CPAFirm)=1
)
MCF on MCF.MatterLkup = M.MattINdex
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmIndex = MCF.CPAFirm
GROUP BY
CM.CPA_NAME,CPAFirmName,CF.AccountID,CPAFirmIndex



SELECT CM.CPA_NAME,CF.CPAFirmName,CF.AccountID,CPAFirmIndex
INTO #Round5
--SELECT *
FROM
(
SELECT CPA_NAME,ARMIndex
FROM
[fp&a]..CPAMatch CM
WHERE
ARMIndex IN (
SELECT ARMIndex
FROM
[fp&a]..CPAMatch
WHERE
CPA_NAME != 'Null' AND
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round4) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round3) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round2) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY ARMIndex
HAVING COUNT(ARMIndex) = 1)
AND
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round4) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round3) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round2) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY  CPA_NAME,ARMIndex
) CM
JOIN
TE_3E_PROD_Clone..ARMaster AM on AM.ARMIndex = CM.ARMIndex
JOIN
TE_3E_PROD_Clone..InvMaster IM on IM.InvIndex = AM.InvMaster
JOIN
TE_3E_PROD_Clone..Matter M on M.Mattindex = IM.LeadMatter
JOIN
(SELECT Matterlkup,MAX(CPAFirm) CPAFirm
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
WHERE
FeeAllocation is not null and
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round4) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round3) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round2) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round1)
GROUP BY MatterLkup
HAVING COUNT(DISTINCT CPAFirm)=1
)
MCF on MCF.MatterLkup = M.MattINdex
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmIndex = MCF.CPAFirm
--WHERE CM.ARMIndex = 364262
GROUP BY
CM.CPA_NAME,CPAFirmName,CF.AccountID,CPAFirmIndex



SELECT CM.CPA_NAME,CF.CPAFirmName,CF.AccountID,CPAFirmIndex
INTO #Round6
--SELECT *
FROM
(
SELECT CPA_NAME,ARMIndex
FROM
[fp&a]..CPAMatch CM
WHERE
ARMIndex IN (
SELECT ARMIndex
FROM
[fp&a]..CPAMatch
WHERE
CPA_NAME != 'Null' AND
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round5) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round4) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round3) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round2) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY ARMIndex
HAVING COUNT(ARMIndex) = 1)
AND
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round5) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round4) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round3) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round2) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY  CPA_NAME,ARMIndex
) CM
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS = CM.CPA_NAME
--JOIN
--TE_3E_PROD_Clone..ARMaster AM on AM.ARMIndex = CM.ARMIndex
--JOIN
--TE_3E_PROD_Clone..InvMaster IM on IM.InvIndex = AM.InvMaster
--JOIN
--TE_3E_PROD_Clone..Matter M on M.Mattindex = IM.LeadMatter
JOIN
(SELECT Matterlkup,MAX(CPAFirm) CPAFirm
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
WHERE
--FeeAllocation is not null and
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round5) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round4) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round3) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round2) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round1)
GROUP BY MatterLkup
HAVING COUNT(DISTINCT CPAFirm)=1
)
MCF on CF.CPAFirmIndex = MCF.CPAFirm


GROUP BY
CM.CPA_NAME,CPAFirmName,CF.AccountID,CPAFirmIndex




SELECT CM.CPA_NAME,CF.CPAFirmName,CF.AccountID,CPAFirmIndex
INTO #Round7
--SELECT *
FROM
(
SELECT CPA_NAME,ARMIndex
FROM
[fp&a]..CPAMatch CM
WHERE
ARMIndex IN (
SELECT ARMIndex
FROM
[fp&a]..CPAMatch
WHERE
CPA_NAME != 'Null' AND
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round6) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round5) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round4) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round3) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round2) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY ARMIndex
HAVING COUNT(ARMIndex) = 1)
AND
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round6) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round5) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round4) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round3) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round2) and
CPA_NAME NOT IN (SELECT CPA_NAME FROM #Round1)
GROUP BY  CPA_NAME,ARMIndex
) CM
JOIN
TE_3E_PROD_Clone..ARMaster AM on AM.ARMIndex = CM.ARMIndex
JOIN
TE_3E_PROD_Clone..InvMaster IM on IM.InvIndex = AM.InvMaster
JOIN
TE_3E_PROD_Clone..Matter M on M.Mattindex = IM.LeadMatter
JOIN
(SELECT Client,MAX(CPAFirm) CPAFirm
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm mcf
JOIN
TE_3E_PROD_Clone..Matter m on m.MattIndex = mcf.MatterLkup
WHERE
--FeeAllocation is not null and
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round6) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round5) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round4) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round3) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round2) AND
CPAFirm NOT IN (SELECT CPAFirmIndex FROM #Round1)
GROUP BY Client
HAVING COUNT(DISTINCT CPAFirm)=1
)
MCF on MCF.Client = m.client
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmIndex = MCF.CPAFirm

GROUP BY
CM.CPA_NAME,CPAFirmName,CF.AccountID,CPAFirmIndex



SELECT CPA_NAME,CPAFirmName,a.id AccountID, CPAFirmIndex
into #Round8
FROM
[fp&a]..CPAMatch cm
JOIN
SalesforceClone..account a on a.name = cm.CPA_NAME
LEFT JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = a.id
WHERE
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7)
GROUP BY CPA_NAME,CPAFirmName,a.id , CPAFirmIndex

SELECT CPA_NAME,CPAFirmName,cf.AccountID, cf.CPAFirmIndex
into #Round9

FROM
[fp&a]..CPAMatch cm
JOIN
TE_3E_PROD_Clone..ARMaster AM on AM.ARMIndex = cm.ARMIndex
JOIN
TE_3E_PROD_Clone..Matter m on m.MattIndex = am.Matter
JOIN
SalesforceClone..Opportunity o on o.id = m.OpportunityID_CCC COLLATE SQL_Latin1_General_CP1_CI_AS
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = o.Referring_CPA_Firm_ID__c
LEFT JOIN (
SELECT CPAFirmIndex
FROM
#Round1
UNION ALL
SELECT CPAFirmIndex
FROM
#Round2
UNION ALL
SELECT CPAFirmIndex
FROM
#Round3
UNION ALL
SELECT CPAFirmIndex
FROM
#Round4
UNION ALL
SELECT CPAFirmIndex
FROM
#Round5
UNION ALL
SELECT CPAFirmIndex
FROM
#Round6
UNION ALL
SELECT CPAFirmIndex
FROM
#Round7
UNION ALL
SELECT CPAFirmIndex
FROM
#Round8) a on a.CPAFirmIndex = cf.CPAFirmIndex

WHERE
a.CPAFirmIndex is null and
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7
UNION ALL
SELECT CPA_NAME
FROM
#Round8)
GROUP BY CPA_NAME,CPAFirmName,cf.AccountID , cf.CPAFirmIndex


SELECT CPA_NAME,CPAFirmName,cf.AccountID, cf.CPAFirmIndex
into #Round10

FROM
[fp&a]..CPAMatch cm
JOIN
TE_3E_PROD_Clone..ARMaster AM on AM.ARMIndex = cm.ARMIndex
JOIN
TE_3E_PROD_Clone..Matter m on m.MattIndex = am.Matter
JOIN
SalesforceClone..Opportunity o on o.id = m.OpportunityID_CCC COLLATE SQL_Latin1_General_CP1_CI_AS
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = o.Amending_CPA_Firm_ID__c
LEFT JOIN (
SELECT CPAFirmIndex
FROM
#Round1
UNION ALL
SELECT CPAFirmIndex
FROM
#Round2
UNION ALL
SELECT CPAFirmIndex
FROM
#Round3
UNION ALL
SELECT CPAFirmIndex
FROM
#Round4
UNION ALL
SELECT CPAFirmIndex
FROM
#Round5
UNION ALL
SELECT CPAFirmIndex
FROM
#Round6
UNION ALL
SELECT CPAFirmIndex
FROM
#Round7
UNION ALL
SELECT CPAFirmIndex
FROM
#Round8
UNION ALL
SELECT CPAFirmIndex
FROM
#Round9) a on a.CPAFirmIndex = cf.CPAFirmIndex

WHERE
a.CPAFirmIndex is null and
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7
UNION ALL
SELECT CPA_NAME
FROM
#Round8
UNION ALL
SELECT CPA_NAME
FROM
#Round9)
GROUP BY CPA_NAME,CPAFirmName,cf.AccountID , cf.CPAFirmIndex


SELECT CPA_NAME,CPAFirmName,cf.AccountID, cf.CPAFirmIndex
into #Round11

FROM
[fp&a]..CPAMatch cm
JOIN
TE_3E_PROD_Clone..ARMaster AM on AM.ARMIndex = cm.ARMIndex
JOIN
TE_3E_PROD_Clone..Matter m on m.MattIndex = am.Matter
JOIN
TE_3E_PROD_Clone..Client c on c.ClientIndex = m.Client
JOIN
SalesforceClone..account ac on ac.id = c.AccountID_CCC COLLATE SQL_Latin1_General_CP1_CI_AS
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = ac.CPA_Account__c
LEFT JOIN (
SELECT CPAFirmIndex
FROM
#Round1
UNION ALL
SELECT CPAFirmIndex
FROM
#Round2
UNION ALL
SELECT CPAFirmIndex
FROM
#Round3
UNION ALL
SELECT CPAFirmIndex
FROM
#Round4
UNION ALL
SELECT CPAFirmIndex
FROM
#Round5
UNION ALL
SELECT CPAFirmIndex
FROM
#Round6
UNION ALL
SELECT CPAFirmIndex
FROM
#Round7
UNION ALL
SELECT CPAFirmIndex
FROM
#Round8
UNION ALL
SELECT CPAFirmIndex
FROM
#Round9
UNION ALL
SELECT CPAFirmIndex
FROM
#Round10) a on a.CPAFirmIndex = cf.CPAFirmIndex

WHERE
a.CPAFirmIndex is null and
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7
UNION ALL
SELECT CPA_NAME
FROM
#Round8
UNION ALL
SELECT CPA_NAME
FROM
#Round9
UNION ALL
SELECT CPA_NAME
FROM
#Round10)
GROUP BY CPA_NAME,CPAFirmName,cf.AccountID , cf.CPAFirmIndex



SELECT CPA_NAME,CPAFirmName,a.id AccountID, CPAFirmIndex
into #Round12
FROM
[fp&a]..CPAMatch cm
JOIN
SalesforceClone..account a on  a.name like '%' + cm.CPA_NAME + '%'
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = a.id
WHERE
 CPA_NAME != 'Bbs' AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7
UNION ALL
SELECT CPA_NAME
FROM
#Round8
UNION ALL
SELECT CPA_NAME
FROM
#Round9
UNION ALL
SELECT CPA_NAME
FROM
#Round10
UNION ALL
SELECT CPA_NAME
FROM
#Round11)
GROUP BY CPA_NAME,CPAFirmName,a.id , CPAFirmIndex


SELECT CPA_NAME,CPAFirmName,a.id AccountID, CPAFirmIndex
into #Round13
FROM
[fp&a]..CPAMatch cm
JOIN
SalesforceClone..account a on  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.name,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'  ',' ') = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cm.CPA_NAME,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'  ',' ')
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = a.id
WHERE
 CPA_NAME != 'Bbs' AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7
UNION ALL
SELECT CPA_NAME
FROM
#Round8
UNION ALL
SELECT CPA_NAME
FROM
#Round9
UNION ALL
SELECT CPA_NAME
FROM
#Round10
UNION ALL
SELECT CPA_NAME
FROM
#Round11
UNION ALL
SELECT CPA_NAME
FROM
#Round12)
GROUP BY CPA_NAME,CPAFirmName,a.id , CPAFirmIndex


SELECT CPA_NAME,CPAFirmName,a.id AccountID, CPAFirmIndex
into #Round14
FROM
[fp&a]..CPAMatch cm
JOIN
SalesforceClone..account a on  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.name,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cm.CPA_NAME,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ')
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = a.id
WHERE
 CPA_NAME != 'Bbs' AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7
UNION ALL
SELECT CPA_NAME
FROM
#Round8
UNION ALL
SELECT CPA_NAME
FROM
#Round9
UNION ALL
SELECT CPA_NAME
FROM
#Round10
UNION ALL
SELECT CPA_NAME
FROM
#Round11
UNION ALL
SELECT CPA_NAME
FROM
#Round12
UNION ALL
SELECT CPA_NAME
FROM
#Round13)
GROUP BY CPA_NAME,CPAFirmName,a.id , CPAFirmIndex





SELECT CPA_NAME,CPAFirmName,a1.CPA_Account__c AccountID, CPAFirmIndex
into #Round15
FROM
[fp&a]..CPAMatch cm
JOIN
SalesforceClone..Contact c on REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(c.name,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cm.CPA_NAME,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ')
JOIN
SalesforceClone..account a1 on  a1.CPA_Contact__c = c.id
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = a1.CPA_Account__c
WHERE
 CPA_NAME != 'Bbs' AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7
UNION ALL
SELECT CPA_NAME
FROM
#Round8
UNION ALL
SELECT CPA_NAME
FROM
#Round9
UNION ALL
SELECT CPA_NAME
FROM
#Round10
UNION ALL
SELECT CPA_NAME
FROM
#Round11
UNION ALL
SELECT CPA_NAME
FROM
#Round12
UNION ALL
SELECT CPA_NAME
FROM
#Round13
UNION ALL
SELECT CPA_NAME
FROM
#Round14)
GROUP BY CPA_NAME,CPAFirmName,a1.CPA_Account__c , CPAFirmIndex



SELECT CPA_NAME,CPAFirmName,c.AccountId AccountID, CPAFirmIndex
into #Round16
FROM
[fp&a]..CPAMatch cm
JOIN
SalesforceClone..Contact c on REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(c.name,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cm.CPA_NAME,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ')
--JOIN
--SalesforceClone..account a1 on  a1.id = c.AccountId
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = c.AccountId
WHERE
 CPA_NAME != 'Bbs' AND CPAFirmName not in ('Miller Ward & Co','Welker Harris & Co') AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7
UNION ALL
SELECT CPA_NAME
FROM
#Round8
UNION ALL
SELECT CPA_NAME
FROM
#Round9
UNION ALL
SELECT CPA_NAME
FROM
#Round10
UNION ALL
SELECT CPA_NAME
FROM
#Round11
UNION ALL
SELECT CPA_NAME
FROM
#Round12
UNION ALL
SELECT CPA_NAME
FROM
#Round13
UNION ALL
SELECT CPA_NAME
FROM
#Round14
UNION ALL
SELECT CPA_NAME
FROM
#Round15)
GROUP BY CPA_NAME,CPAFirmName,c.AccountId , CPAFirmIndex



SELECT CPA_NAME,CPAFirmName,a1.CPA_Account__c AccountID, CPAFirmIndex
into #Round17
FROM
[fp&a]..CPAMatch cm
JOIN
SalesforceClone..Contact c on REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(c.name,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cm.CPA_NAME,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ')
JOIN
SalesforceClone..account a1 on  a1.id = c.AccountId
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = a1.CPA_Account__c
WHERE
 CPA_NAME != 'Bbs' AND CPAFirmName not in ('Miller Ward & Co','Welker Harris & Co','Harding Shymanski & Company Psc - Evansville IN') AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7
UNION ALL
SELECT CPA_NAME
FROM
#Round8
UNION ALL
SELECT CPA_NAME
FROM
#Round9
UNION ALL
SELECT CPA_NAME
FROM
#Round10
UNION ALL
SELECT CPA_NAME
FROM
#Round11
UNION ALL
SELECT CPA_NAME
FROM
#Round12
UNION ALL
SELECT CPA_NAME
FROM
#Round13
UNION ALL
SELECT CPA_NAME
FROM
#Round14
UNION ALL
SELECT CPA_NAME
FROM
#Round15
UNION ALL
SELECT CPA_NAME
FROM
#Round16)
GROUP BY CPA_NAME,CPAFirmName,a1.CPA_Account__c , CPAFirmIndex



SELECT CPA_NAME,CPAFirmName,a.id AccountID, CPAFirmIndex
into #Round18
FROM
[fp&a]..CPAMatch cm
JOIN
SalesforceClone..account a on  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.name,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') like '%' +  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cm.CPA_NAME,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') + '%'
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = a.id
WHERE
 CPA_NAME != 'Bbs' AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7
UNION ALL
SELECT CPA_NAME
FROM
#Round8
UNION ALL
SELECT CPA_NAME
FROM
#Round9
UNION ALL
SELECT CPA_NAME
FROM
#Round10
UNION ALL
SELECT CPA_NAME
FROM
#Round11
UNION ALL
SELECT CPA_NAME
FROM
#Round12
UNION ALL
SELECT CPA_NAME
FROM
#Round13
UNION ALL
SELECT CPA_NAME
FROM
#Round14
UNION ALL
SELECT CPA_NAME
FROM
#Round15
UNION ALL
SELECT CPA_NAME
FROM
#Round16
UNION ALL
SELECT CPA_NAME
FROM
#Round17)
GROUP BY CPA_NAME,CPAFirmName,a.id , CPAFirmIndex


SELECT CPA_NAME,CPAFirmName,a.id AccountID, CPAFirmIndex
into #Round19
FROM
[fp&a]..CPAMatch cm
JOIN
SalesforceClone..account a on  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cm.CPA_NAME,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') like '%' +  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.name,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') + '%'
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = a.id
WHERE
 CPA_NAME not in ('Bbs','Cla Financial Advisors, Llc','Clarke, Snow & Riley','Cpa Template','Hassan Cpa','Dean Dorton Allen Ford Pllc','Riitters Thompson & Olson PA') AND
 CPAFirmName not in ('CLA','CEA LLP','Freyberg Hinkle Ashland Powers & Stowell, SC','Anders','CBIZ','MPM, CPA'+char(39)+'s','Ssa PC','MSPC CPAs & Advisors, P. C.','Anders','AGN','ELO (FKA Endorf Lurken Olson & Co CPA LLC)','Main Amundson and Associates','C&D LLP (fka: Christensen & Drake)','PKF (FKA Batchelor, Frechette, Mc Crory, & Michael)','Smith Linden & Basso LLP','Tys') AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round1
UNION ALL
SELECT CPA_NAME
FROM
#Round2
UNION ALL
SELECT CPA_NAME
FROM
#Round3
UNION ALL
SELECT CPA_NAME
FROM
#Round4
UNION ALL
SELECT CPA_NAME
FROM
#Round5
UNION ALL
SELECT CPA_NAME
FROM
#Round6
UNION ALL
SELECT CPA_NAME
FROM
#Round7
UNION ALL
SELECT CPA_NAME
FROM
#Round8
UNION ALL
SELECT CPA_NAME
FROM
#Round9
UNION ALL
SELECT CPA_NAME
FROM
#Round10
UNION ALL
SELECT CPA_NAME
FROM
#Round11
UNION ALL
SELECT CPA_NAME
FROM
#Round12
UNION ALL
SELECT CPA_NAME
FROM
#Round13
UNION ALL
SELECT CPA_NAME
FROM
#Round14
UNION ALL
SELECT CPA_NAME
FROM
#Round15
UNION ALL
SELECT CPA_NAME
FROM
#Round16
UNION ALL
SELECT CPA_NAME
FROM
#Round17
UNION ALL
SELECT CPA_NAME
FROM
#Round18)
GROUP BY CPA_NAME,CPAFirmName,a.id , CPAFirmIndex
*/
drop table if exists #Cpam
/*
SELECT CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS CPAFirmName,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS AccountID, CPAFirmIndex
into #cpam
FROM
#Round1
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round2
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round3
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round4
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round5
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round6
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round7
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round8
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round9
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round10
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round11
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round12
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round13
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round14
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round15
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round16
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round17
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round18
UNION ALL
SELECT  CPA_NAME,CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS,AccountID COLLATE SQL_Latin1_General_CP1_CI_AS, CPAFirmIndex
FROM
#Round19
*/


drop table if exists #Round0;
drop table if exists #acc;

SELECT CPA_NAME,CPAFirmName, AccountID, CPAFirmIndex
into #Round0
FROM
(
SELECT CPA_NAME,CPAFirmName,a.id AccountID, CPAFirmIndex ,ROW_NUMBER()OVER(Partition By CPA_NAME Order By Nbr desc,MaxTS Desc) rn
FROM
[FP&A]..CPAMatch CM
JOIN
SalesforceClone..Account a on a.Name = CM.CPA_NAME
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = a.id
LEFT JOIN
(SELECT CPAFirm,COUNT(1) Nbr,MAX(TimeStamp) MaxTS
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY CPAFirm
) MCF on MCF.CPAFirm = CF.CPAFirmIndex
) a WHERE rn = 1
GROUP BY CPA_NAME,CPAFirmName,AccountID , CPAFirmIndex

INSERT INTO #Round0
SELECT CPA_NAME,CPAFirmName,AccountID AccountID, CPAFirmIndex

FROM
(
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex ,ROW_NUMBER()OVER(Partition By CPA_NAME Order By Nbr desc,MaxTS Desc) rn
FROM
[FP&A]..CPAMatch CM
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS = cm.CPA_NAME
LEFT JOIN
(SELECT CPAFirm,COUNT(1) Nbr,MAX(TimeStamp) MaxTS
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY CPAFirm
) MCF on MCF.CPAFirm = CF.CPAFirmIndex
WHERE AccountID is not null
) a WHERE rn = 1

AND
 CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round0)
GROUP BY CPA_NAME,CPAFirmName,AccountID , CPAFirmIndex

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.name,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') name,id
INTO #acc
FROM SalesforceClone..account  a

INSERT INTO #Round0
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex
FROM
(
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex ,ROW_NUMBER()OVER(Partition By CPA_NAME Order By Nbr desc,MaxTS Desc) rn
FROM
(SELECT * FROM
[fp&a]..CPAMatch cm
WHERE
CPA_NAME NOT IN ('Null','Bbs','Hlb Usa','Agh, Llc','agn','Anders','Rost & Co., Cpas, P.C.','J&J Cpas','Braver Pc','Fisher, P.C.','Business Solutions, Llc','Campbell','Cohen & Company','Fisher, P.C.','Lk & Associates','Miller & Co., Plc','Ssa, P.C.','Family Farms') AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round0))cm
JOIN
#acc a on  name like '%' +  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cm.CPA_NAME,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') + '%'
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = a.id
LEFT JOIN
(SELECT CPAFirm,COUNT(1) Nbr,MAX(TimeStamp) MaxTS
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY CPAFirm
) MCF on MCF.CPAFirm = CF.CPAFirmIndex
WHERE AccountID is not null
AND
 CPAFirmName NOT IN ('Boas & Boas LLP','Landucci, Bick, Matter & Johnston LLP','Mullen Howard Hammatt & Co PA','Lublin Sussman Group LLP','Don Friedman','Bohlmann Accounting Group PLLC','Baden Gage & Schroeder LLC - Dupe2','Sharrard McGee & Co','Travis Wolff & Co.','CDPA','MSPC CPAs & Advisors, P. C.')
) a WHERE rn = 1
GROUP BY CPA_NAME,CPAFirmName,AccountID , CPAFirmIndex



INSERT INTO #Round0
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex
FROM
(
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex ,ROW_NUMBER()OVER(Partition By CPA_NAME Order By Nbr desc,MaxTS Desc) rn
FROM
(SELECT * FROM
[fp&a]..CPAMatch cm
WHERE
CPA_NAME NOT IN ('Null','Bbs','Hlb Usa','Agh, Llc','agn','Anders','Rost & Co., Cpas, P.C.','J&J Cpas','Braver Pc','Fisher, P.C.','Business Solutions, Llc','Campbell','Cohen & Company','Fisher, P.C.','Lk & Associates','Miller & Co., Plc','Ssa, P.C.','Family Farms') AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round0))cm
JOIN
#acc a on REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cm.CPA_NAME,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ')  like '%' +  name + '%'
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = a.id
LEFT JOIN
(SELECT CPAFirm,COUNT(1) Nbr,MAX(TimeStamp) MaxTS
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY CPAFirm
) MCF on MCF.CPAFirm = CF.CPAFirmIndex
WHERE AccountID is not null
AND
 CPAFirmName NOT IN ('Boas & Boas LLP','Landucci, Bick, Matter & Johnston LLP','Mullen Howard Hammatt & Co PA','Lublin Sussman Group LLP','Don Friedman','Bohlmann Accounting Group PLLC','Baden Gage & Schroeder LLC - Dupe2','Sharrard McGee & Co','Travis Wolff & Co.','CDPA','MSPC CPAs & Advisors, P. C.')
) a WHERE rn = 1
GROUP BY CPA_NAME,CPAFirmName,AccountID , CPAFirmIndex


SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(a.CPAFirmName,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') name,CPAFirmIndex,AccountID
INTO #acc3e
FROM TE_3E_PROD_Clone..AG_CPAFirm  a


INSERT INTO #Round0
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex
FROM
(
SELECT CPA_NAME,name CPAFirmName,AccountID, CPAFirmIndex ,ROW_NUMBER()OVER(Partition By CPA_NAME Order By Nbr desc,MaxTS Desc) rn
FROM
(SELECT * FROM
[fp&a]..CPAMatch cm
WHERE
CPA_NAME NOT IN ('Null','Bbs','Hlb Usa','Agh, Llc','agn','Anders','Rost & Co., Cpas, P.C.','J&J Cpas','Braver Pc','Fisher, P.C.','Business Solutions, Llc','Campbell','Cohen & Company','Fisher, P.C.','Lk & Associates','Miller & Co., Plc','Ssa, P.C.','Family Farms') AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round0))cm
JOIN
#acc3e a on  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cm.CPA_NAME COLLATE SQL_Latin1_General_CP1_CI_AS,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ')  like '%' + name + '%'
LEFT JOIN
(SELECT CPAFirm,COUNT(1) Nbr,MAX(TimeStamp) MaxTS
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY CPAFirm
) MCF on MCF.CPAFirm = a.CPAFirmIndex
WHERE AccountID is not null
AND
 name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN ('MBE','RKE','Boas & Boas LLP','Landucci, Bick, Matter & Johnston LLP','Mullen Howard Hammatt & Co PA','Lublin Sussman Group LLP','Don Friedman','Bohlmann Accounting Group PLLC','Baden Gage & Schroeder LLC - Dupe2','Sharrard McGee & Co','Travis Wolff & Co.','CDPA','MSPC CPAs & Advisors, P. C.')
) a WHERE rn = 1
GROUP BY CPA_NAME,CPAFirmName,AccountID , CPAFirmIndex


INSERT INTO #Round0
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex
FROM
(
SELECT CPA_NAME,name CPAFirmName,AccountID, CPAFirmIndex ,ROW_NUMBER()OVER(Partition By CPA_NAME Order By Nbr desc,MaxTS Desc) rn
FROM
(SELECT * FROM
[fp&a]..CPAMatch cm
WHERE
CPA_NAME NOT IN ('Null','Bbs','Hlb Usa','Agh, Llc','agn','Anders','Rost & Co., Cpas, P.C.','J&J Cpas','Braver Pc','Fisher, P.C.','Business Solutions, Llc','Campbell','Cohen & Company','Fisher, P.C.','Lk & Associates','Miller & Co., Plc','Ssa, P.C.','Family Farms') AND
CPA_NAME NOT IN (
SELECT CPA_NAME
FROM
#Round0))cm
JOIN
#acc3e a on name  like '%' +  REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cm.CPA_NAME COLLATE SQL_Latin1_General_CP1_CI_AS,'*',''),',',''),' & ',' '),' and ',' '),'.',''),'Company','Co'),' Cpas',''),' LLC',''),' PC',''),' P C',''),' PLC',''),char(39)+'s','') ,'  ',' ') + '%'
LEFT JOIN
(SELECT CPAFirm,COUNT(1) Nbr,MAX(TimeStamp) MaxTS
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY CPAFirm
) MCF on MCF.CPAFirm = a.CPAFirmIndex
WHERE AccountID is not null
AND
 name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN ('MBE','RKE','Boas & Boas LLP','Landucci, Bick, Matter & Johnston LLP','Mullen Howard Hammatt & Co PA','Lublin Sussman Group LLP','Don Friedman','Bohlmann Accounting Group PLLC','Baden Gage & Schroeder LLC - Dupe2','Sharrard McGee & Co','Travis Wolff & Co.','CDPA','MSPC CPAs & Advisors, P. C.')
) a WHERE rn = 1
GROUP BY CPA_NAME,CPAFirmName,AccountID , CPAFirmIndex


INSERT INTO #Round0
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex-- ,a2.name--,MatterNUmber
FROM
(
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex ,ROW_NUMBER()OVER(Partition By CPA_NAME Order By Nbr desc,MaxTS Desc) rn,MatterNUmber
FROM
(SELECT CPA_NAME,MatterNUmber FROM
[fp&a]..CPAMatch CM
where
CPA_NAME NOT IN (SELECT CPA_NAME
FROM
#Round0) AND
ARMIndex IN (
SELECT ARMIndex
FROM
[fp&a]..CPAMatch
GROUP BY ARMIndex
HAVING COUNT(ARMIndex) = 1 AND SUM(COALESCE(TRY_CAST(AMOUNTDUETOCPA as numeric),0))>0)
GROUP BY  CPA_NAME,MatterNUmber
) CM
JOIN
TE_3E_PROD_Clone..Matter M on M.NUmber COLLATE SQL_Latin1_General_CP1_CI_AS = CM.MatterNUmber
JOIN
(SELECT Matterlkup,MAX(CPAFirm) CPAFirm
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY MatterLkup
HAVING COUNT(DISTINCT CPAFirm)=1
)
MCF on MCF.MatterLkup = M.MattINdex
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmIndex = MCF.CPAFirm
LEFT JOIN
(SELECT CPAFirm,COUNT(1) Nbr,MAX(TimeStamp) MaxTS
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY CPAFirm
) MCF2 on MCF.CPAFirm = CF.CPAFirmIndex
WHERE AccountID is not null
) a
JOIN
SalesForceClone..account a2 on a2.id = a.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE rn = 1
GROUP BY
CPA_NAME,CPAFirmName,AccountID,CPAFirmIndex--,a2.name--,MatterNUmber




INSERT INTO #Round0
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex-- ,a2.name--,MatterNUmber
FROM
(
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex ,ROW_NUMBER()OVER(Partition By CPA_NAME Order By Nbr desc,MaxTS Desc) rn,MatterNUmber
FROM
(SELECT CPA_NAME,MatterNUmber FROM
[fp&a]..CPAMatch CM
where
CPA_NAME NOT IN (SELECT CPA_NAME
FROM
#Round0) AND
ARMIndex IN (
SELECT ARMIndex
FROM
[fp&a]..CPAMatch
GROUP BY ARMIndex
HAVING COUNT(ARMIndex) = 1 )
GROUP BY  CPA_NAME,MatterNUmber
) CM
JOIN
TE_3E_PROD_Clone..Matter M on M.NUmber COLLATE SQL_Latin1_General_CP1_CI_AS = CM.MatterNUmber
JOIN
(SELECT Matterlkup,MAX(CPAFirm) CPAFirm
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY MatterLkup
HAVING COUNT(DISTINCT CPAFirm)=1
)
MCF on MCF.MatterLkup = M.MattINdex
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmIndex = MCF.CPAFirm
LEFT JOIN
(SELECT CPAFirm,COUNT(1) Nbr,MAX(TimeStamp) MaxTS
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY CPAFirm
) MCF2 on MCF.CPAFirm = CF.CPAFirmIndex
WHERE AccountID is not null
) a
JOIN
SalesForceClone..account a2 on a2.id = a.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE rn = 1
GROUP BY
CPA_NAME,CPAFirmName,AccountID,CPAFirmIndex--,a2.name--,MatterNUmber

INSERT INTO #Round0
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex-- ,a2.name--,MatterNUmber
FROM
(
SELECT CPA_NAME,CPAFirmName,AccountID, CPAFirmIndex ,ROW_NUMBER()OVER(Partition By CPA_NAME Order By Nbr desc,MaxTS Desc) rn,MatterNUmber
FROM
(SELECT CPA_NAME,MatterNUmber FROM
[fp&a]..CPAMatch CM
where
CPA_NAME NOT IN (SELECT CPA_NAME
FROM
#Round0) AND
ARMIndex IN (
SELECT ARMIndex
FROM
[fp&a]..CPAMatch
where
CPA_NAME NOT IN (SELECT CPA_NAME
FROM
#Round0)
GROUP BY ARMIndex
HAVING COUNT(ARMIndex) = 1 )
GROUP BY  CPA_NAME,MatterNUmber
) CM
JOIN
TE_3E_PROD_Clone..Matter M on M.NUmber COLLATE SQL_Latin1_General_CP1_CI_AS = CM.MatterNUmber
JOIN
(SELECT Matterlkup,MAX(CPAFirm) CPAFirm
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
where
CPAFirm NOT IN (SELECT CPAFirmIndex
FROM
#Round0)
GROUP BY MatterLkup
HAVING COUNT(DISTINCT CPAFirm)=1
)
MCF on MCF.MatterLkup = M.MattINdex
JOIN
TE_3E_PROD_Clone..AG_CPAFirm CF on CF.CPAFirmIndex = MCF.CPAFirm
LEFT JOIN
(SELECT CPAFirm,COUNT(1) Nbr,MAX(TimeStamp) MaxTS
FROM
TE_3E_PROD_Clone..AG_MattCPAFirm
GROUP BY CPAFirm
) MCF2 on MCF.CPAFirm = CF.CPAFirmIndex
WHERE AccountID is not null
) a
JOIN
SalesForceClone..account a2 on a2.id = a.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE rn = 1
GROUP BY
CPA_NAME,CPAFirmName,AccountID,CPAFirmIndex--,a2.name--,MatterNUmber



SELECT CPA_NAME, CPAFirmName, AccountID, CPAFirmIndex
into #cpam
FROM
#Round0


--SELECT *
--FROM
--#cpam

drop table if exists #cpahm;

       select
              c.*
       --,      cpa1.[Type] [CPA1_Type]
       --,      cpa1.[ParentId] [CPA1_ParentId]
       --,      cpa1.CreatedDate [CPA1_CreatedDate]
       ,      coalesce(cpa4.[Id], cpa3.[Id], cpa2.[Id], cpa1.[Id]) [CPATop_AccountId]
       ,      coalesce(cpa4.[Name], cpa3.[Name], cpa2.[Name], cpa1.[Name]) [CPATop_Name]
       --,      cpa2.[Id] [CPA2_AccountId]
       --,      cpa2.[Name] [CPA2_Name]
       --,      cpa2.[ParentId] [CPA2_ParentId]
       --,      cpa3.[Id] [CPA3_AccountId]
       --,      cpa3.[Name] [CPA3_Name]
       --,      cpa3.[ParentId] [CPA3_ParentId]
       --,      cpa4.[Id] [CPA4_AccountId]
       --,      cpa4.[Name] [CPA4_Name]
       --,      cpa4.[ParentId] [CPA4_ParentId]
       into #cpahm
       from
              #cpam c
       left join
              SalesForceClone.dbo.Account cpa1 on cpa1.[Id] = c.AccountID
       left join
              SalesForceClone.dbo.Account cpa2 on cpa2.[Id] = cpa1.[ParentId]
       left join
              SalesForceClone.dbo.Account cpa3 on cpa3.[Id] = cpa2.[ParentId]
       left join
              SalesForceClone.dbo.Account cpa4 on cpa4.id = cpa3.ParentId




drop table if exists #cpa;

       select
              a.[CPAId]
       ,      a.[CPAName]
       into #cpa
       from
              (
                     select
                           o.Referring_CPA_Firm_ID__c [CPAId]
                     ,      o.CPA_Referral_Firm__c [CPAName]
                     from
                           SalesForceClone.dbo.Opportunity o
                     where
                           StageName like '%closed%'
                           and o.Referring_CPA_Firm_ID__c is not null
                     group by
                           o.Referring_CPA_Firm_ID__c
                     ,      o.CPA_Referral_Firm__c

                     union all

                     select
                           o.Amending_CPA_Firm_ID__c [CPAId]
                     ,      o.Amending_CPA_Firm__c [CPAName]
                     from
                           SalesForceClone.dbo.Opportunity o
                     where
                           StageName like '%closed%'
                           and o.Amending_CPA_Firm_ID__c is not null
                     group by
                           o.Amending_CPA_Firm_ID__c
                     ,      o.Amending_CPA_Firm__c

                     union all

                     select
                           cpa.Id [CPAId]
                     ,      cpa.[Name] [CPAName]
                     from
                           SalesForceClone.dbo.Opportunity o
                     left join
                           SalesForceClone.dbo.Account a on a.id = o.AccountId
                     left join
                           SalesForceClone.dbo.Account cpa on cpa.id = a.CPA_Account__c
                     where
                           o.StageName like '%closed%'
                           and cpa.Id is not null
                     group by
                           cpa.Id
                     ,      cpa.[Name]
              ) a
       group by
              [CPAId]
       ,      [CPAName]


drop table if exists #cpah;

       select
              c.[CPAId] [CPA1_AccountId]
       ,      c.[CPAName] [CPA1_Name]
       ,      cpa1.[Type] [CPA1_Type]
       ,      cpa1.[ParentId] [CPA1_ParentId]
       ,      cpa1.CreatedDate [CPA1_CreatedDate]
       ,      coalesce(cpa4.[Id], cpa3.[Id], cpa2.[Id], cpa1.[Id]) [CPATop_AccountId]
       ,      coalesce(cpa4.[Name], cpa3.[Name], cpa2.[Name], cpa1.[Name]) [CPATop_Name]
       ,      cpa2.[Id] [CPA2_AccountId]
       ,      cpa2.[Name] [CPA2_Name]
       ,      cpa2.[ParentId] [CPA2_ParentId]
       ,      cpa3.[Id] [CPA3_AccountId]
       ,      cpa3.[Name] [CPA3_Name]
       ,      cpa3.[ParentId] [CPA3_ParentId]
       ,      cpa4.[Id] [CPA4_AccountId]
       ,      cpa4.[Name] [CPA4_Name]
       ,      cpa4.[ParentId] [CPA4_ParentId]
       into #cpah
       from
              #cpa c
       left join
              SalesForceClone.dbo.Account cpa1 on cpa1.[Id] = c.[CPAId]
       left join
              SalesForceClone.dbo.Account cpa2 on cpa2.[Id] = cpa1.[ParentId]
       left join
              SalesForceClone.dbo.Account cpa3 on cpa3.[Id] = cpa2.[ParentId]
       left join
              SalesForceClone.dbo.Account cpa4 on cpa4.id = cpa3.ParentId


--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

drop table if exists #Base;

SELECT CPAMatchId,
CPA_NAME
, CM.Client,
          CASE WHEN PAIDINFULL ='0' THEN NULL ELSE PAIDINFULL END [Client Paid In Full], CM.INVOICENUMBER,
          CASE WHEN (INVOICEDATE='1/0/1900' OR INVOICEDATE='0') THEN '' ELSE FORMAT(try_convert(DATE, INVOICEDATE), 'MM/dd/yyyy') END [Invoice Date],
          try_convert(DECIMAL(15,2),CASE WHEN INVOICEAMOUNT='0' THEN '' WHEN ROW_NUMBER()OVER(Partition By CM.INVOICENUMBER,CM.Client ORDER BY CM.ARMIndex) != 1 AND CM.ARMIndex != -1 THEN '0' ELSE INVOICEAMOUNT END) [Invoice Amount],
          try_convert(DECIMAL(15,2),CASE WHEN AMOUNTPAID is null THEN '0' ELSE AMOUNTPAID END) [Amount Paid],
          try_convert(DECIMAL(15,2),CASE WHEN EXPENSES IS NULL THEN '0' ELSE EXPENSES END) [Expenses],
          try_convert(DECIMAL(15,2),CASE WHEN AMOUNTPAIDLESSEXPENSES IS NULL THEN '0' ELSE AMOUNTPAIDLESSEXPENSES END) [Amount Paid Less Expenses],
          try_convert(Decimal(15,2),CASE WHEN AMOUNTDUETOCPA IS NULL OR (TRY_CAST(AMOUNTDUETOCPA as decimal(15,2))>2000000000000 AND AMOUNTPAID = '0') THEN '0' ELSE AMOUNTDUETOCPA END) [Amount Due To CPA],
          try_convert(DECIMAL(15,2),CASE WHEN AMOUNTPAIDTOCPA = '0' OR (TRY_CAST(AMOUNTDUETOCPA as decimal(15,2))>2000000000000 AND AMOUNTPAID = '0')  THEN NULL ELSE AMOUNTPAIDTOCPA END) [Amount Paid To CPA],
          CASE WHEN (DATEPAIDTOCPA='1/0/1900' OR DATEPAIDTOCPA='0') THEN '' ELSE FORMAT(try_convert(DATE, DATEPAIDTOCPA), 'MM/dd/yyyy') END [Date Paid],
          try_convert(Decimal(15,2), REMAININGDUETOCPA ) [Remaining Amount Due to CPA]
          ,CASE
          WHEN MAX(NULLIF(COALESCE(CASE WHEN nord.Mattindex is null and COALESCE(CM.ARMIndex,-1) != -1 THEN 225 END, MD.PG2,C.PG2,C2.PG2,-1),-1))OVER(PARTITION BY CPA_NAME)
          = MIN(NULLIF(COALESCE(CASE WHEN nord.Mattindex is null and COALESCE(CM.ARMIndex,-1) != -1 THEN 225 END, MD.PG2,C.PG2,C2.PG2,-1),-1))OVER(PARTITION BY CPA_NAME) THEN
          MAX(NULLIF(COALESCE(CASE WHEN nord.Mattindex is null and COALESCE(CM.ARMIndex,-1) != -1 THEN 225 END, MD.PG2,C.PG2,C2.PG2,-1),-1))OVER(PARTITION BY CPA_NAME)
          WHEN MAX(NULLIF(COALESCE(CASE WHEN nord.Mattindex is null and COALESCE(CM.ARMIndex,-1) != -1 THEN 225 END, MD.PG2,C.PG2,C2.PG2,-1),-1))OVER(PARTITION BY CPA_NAME,CM.Client)
          = MIN(NULLIF(COALESCE(CASE WHEN nord.Mattindex is null and COALESCE(CM.ARMIndex,-1) != -1 THEN 225 END, MD.PG2,C.PG2,C2.PG2,-1),-1))OVER(PARTITION BY CPA_NAME,CM.Client) THEN
          MAX(NULLIF(COALESCE(CASE WHEN nord.Mattindex is null and COALESCE(CM.ARMIndex,-1) != -1 THEN 225 END, MD.PG2,C.PG2,C2.PG2,-1),-1))OVER(PARTITION BY CPA_NAME,CM.Client)
          ELSE COALESCE(CASE WHEN nord.Mattindex is null and COALESCE(CM.ARMIndex,-1) != -1 THEN 225 END, MD.PG2,C.PG2,C2.PG2,-1)
          END
           SL
          ,CM.MattCategory

          INTO #Base
          FROM [FP&A].dbo.CPAMatch CM
          LEFT JOIN
          TE_3E_PROD_Clone..ARMaster AM on AM.ARMIndex = CM.ARMIndex
          OUTER APPLY
          (SELECT TOP 1 MD.* ,CASE WHEN MattStatus in ('KO','TERM') AND PracticeGRoup = 225 THEN 110
          WHEN  (pc.PGDetHdr is null OR FNDate >AM.InvDate) AND PracticeGRoup = 225 THEN 110
          ELSE PracticeGRoup END PG2,FnDate
          FROM
          TE_3E_PROD_Clone..MattDate MD
          JOIN
          TE_3E_PROD_Clone..Matter M on M.MattIndex = MD.Matterlkup
          JOIN
          TE_3E_PROD_Clone..PGDetHdr_CCC PH on PH.MatterLkUp = M.MattIndex --AND PH.NxEndDate = '99991231' AND PH.NxStartDate != '99991231'
          LEFT JOIN
          (SELECT PGDetHdr,MIN(FNDATE) FnDate FROM TE_3E_PROD_Clone..PGDetChild_CCC PC WHERE TriggerType = 'FN' GROUP BY PGDetHdr) PC on PC.PGDetHdr = PH.PGDetHdr_CCCID
          WHERE MD.MatterLkUp = AM.Matter and MD.NxEndDate = '99991231' --and MD.PracticeGroup IN (@PG)
          ORDER by MD.NxStartDate,COALESCE(FnDate,GETDATE())) MD
          OUTER APPLY
          (SELECT TOP 1 MD.*,CASE WHEN MattStatus in ('KO','TERM') AND PracticeGRoup = 225 THEN 110
          WHEN  (pc.PGDetHdr is null OR FNDate >IM.InvDate) AND PracticeGRoup = 225 THEN 110
          ELSE PracticeGRoup END PG2
          FROM
          TE_3E_PROD_Clone..Payor P
          JOIN
          TE_3E_PROD_Clone..InvMaster IM on IM.DefPayor = P.PayorIndex
          JOIN
          TE_3E_PROD_Clone..ARMaster AM on AM.InvMaster = IM.InvIndex
          JOIN
          TE_3E_PROD_Clone..MattDate MD on MD.MatterLkUp = AM.Matter  and NxEndDate = '99991231'
          JOIN
          TE_3E_PROD_Clone..Matter M on M.MattIndex = MD.Matterlkup
          JOIN
          TE_3E_PROD_Clone..PGDetHdr_CCC PH on PH.MatterLkUp = M.MattIndex --AND PH.NxEndDate = '99991231' AND PH.NxStartDate != '99991231'
          LEFT JOIN
          (SELECT PGDetHdr,MIN(FNDATE) FnDate FROM TE_3E_PROD_Clone..PGDetChild_CCC PC WHERE TriggerType = 'FN' GROUP BY PGDetHdr) PC on PC.PGDetHdr = PH.PGDetHdr_CCCID
          WHERE  P.Entity = CM.[CLIENT ID] --and MD.PracticeGroup IN (@PG)
          ORDER BY CASE WHEN IM.InvDate = TRY_CAST(CM.INVOICEDATE as datetime) THEN 0 ELSE 1 END,ABS(CM.INVOICEAMOUNT + AM.ARAmt),COALESCE(FnDate,GETDATE())
          ) C
          OUTER APPLY
          (SELECT TOP 1 MD.*,CASE WHEN MattStatus in ('KO','TERM') AND PracticeGRoup = 225 THEN 110
          WHEN (pc.PGDetHdr is null OR FNDate >IM.InvDate) AND PracticeGRoup = 225 THEN 110
          ELSE PracticeGRoup END PG2
          FROM
          TE_3E_PROD_Clone..Payor P
          JOIN
          TE_3E_PROD_Clone..InvMaster IM on IM.DefPayor = P.PayorIndex
          JOIN
          TE_3E_PROD_Clone..ARMaster AM on AM.InvMaster = IM.InvIndex
          JOIN
          TE_3E_PROD_Clone..MattDate MD on MD.MatterLkUp = AM.Matter  and NxEndDate = '99991231'
          JOIN
          TE_3E_PROD_Clone..Matter M on M.MattIndex = MD.Matterlkup
          JOIN
          TE_3E_PROD_Clone..PGDetHdr_CCC PH on PH.MatterLkUp = M.MattIndex --AND PH.NxEndDate = '99991231' AND PH.NxStartDate != '99991231'
          LEFT JOIN
          (SELECT PGDetHdr,MIN(FNDATE) FnDate FROM TE_3E_PROD_Clone..PGDetChild_CCC PC WHERE TriggerType = 'FN' GROUP BY PGDetHdr) PC on PC.PGDetHdr = PH.PGDetHdr_CCCID
          WHERE  P.DisplayName COLLATE SQL_Latin1_General_CP1_CI_AS = CM.Client --and MD.PracticeGroup IN (@PG)
          ORDER BY CASE WHEN IM.InvDate = TRY_CAST(CM.INVOICEDATE as datetime) THEN 0 ELSE 1 END,ABS(CM.INVOICEAMOUNT + AM.ARAmt),COALESCE(FnDate,GETDATE())
          ) C2
          OUTER APPLY
          (SELECT TOP 1 mrd.* FROM TE_3E_PROD_Clone..Matter m
          JOIN
          TE_3E_PROD_Clone..Client c on c.ClientIndex = m.Client
          JOIN
          TE_3E_PROD_Clone..Matter mrd on mrd.client = c.ClientINdex
          JOIN
          TE_3E_PROD_Clone..MattDate MD on MD.Matterlkup = mrd.MattIndex AND NxEndDate = '99991231' and NxStartDate != '99991231' and PracticeGroup in (110,120,130)
          WHERE m.MattIndex = AM. Matter) nord
         where
            cpamatchid not in (select cpamatchid from [FP&A].dbo.CPAMatch where ARMIndex != -1 and isnull(trim(AMOUNTDUETOCPA),'0') = '0' and CPAMatchId is not null)
         --AND (COALESCE(CASE WHEN nord.Mattindex is null and COALESCE(CM.ARMIndex,-1) != -1 THEN 225 END, MD.PG2,C.PG2,C2.PG2,-1) IN (@PG))
         --AND CPA_NAME = @CPAParameter) IN (@PG))
         --AND CPA_NAME = 'Somerset Cpas'

drop table if exists #SLD;

SELECT *
INTO #SLD
FROM (
SELECT CM.CPA_NAME
,CM.[Amount Paid To CPA]
,pg.Description SLD
FROM
(
SELECT *
FROM
#Base) CM

         LEFT JOIN
         TE_3E_PROD_CLONE..PracticeGRoup pg on pg.code = CM.SL

) p PIVOT
(SUM([AMount Paid To CPA]) FOR SLD IN ([R&D Services],[ERC Services],[179D Services])) p


drop table if exists #SLO;

SELECT CPA_NAME
,[R&D Services] [R&D Services OS]
,[ERC Services] [ERC Services OS]
,[179D Services] [179D Services OS]
INTO #SLO
FROM (
SELECT CM.CPA_NAME
,CM.[Remaining Amount Due to CPA]
,pg.Description SLD
FROM
(
SELECT *
FROM
#Base) CM

         LEFT JOIN
         TE_3E_PROD_CLONE..PracticeGRoup pg on pg.code = CM.SL

) p PIVOT
(SUM([Remaining Amount Due to CPA]) FOR SLD IN ([R&D Services],[ERC Services],[179D Services])) p



drop table if exists #FAAmts;

SELECT
    [CPATop_AccountId]
,   [CPATop_Name]
,   SUM(ClientPaidLessExpenses) ClientPaidLessExpenses
,   SUM(AMountDueToCPA)AMountDueToCPA
,   SUM(AmountPaidToCPA) AmountPaidToCPA
,   SUM([R&D Services]) [R&D Services]
,   SUM([ERC Services])[ERC Services]
,   SUM([Other Services])[Other Services]
,   SUM(AmountOutStandingToCPA) AmountOutStandingToCPA
,   SUM([R&D Services OS]) [R&D Services OS]
,   SUM([ERC Services OS])[ERC Services OS]
,   SUM([Other Services OS])[Other Services OS]
,   SUM(ERCClientPaid)ERCClientPaid
INTO #FAAmts
FROM
(
SELECT a.*
,   [CPATop_AccountId]
,   [CPATop_Name]
,ROW_NUMBER()OVER(PArtition by a.CPA_Name ORDER BY DIFFERENCE(a.CPA_NAME,CPATop_Name) desc) rn
FROM
(
SELECT
    a.CPA_Name
,   State
,   COUNT(Distinct a.Client) NbrClients

,   SUM(TRY_CAST(InvoiceAmount as numeric(12,2))) ClientInvoices
,   SUM(TRY_CAST(AMOUNTPAIDLESSEXPENSES as numeric(12,2))) ClientPaidLessExpenses
,   SUM(TRY_CAST(AMOUNTDUETOCPA as numeric(12,2))) AMountDueToCPA
,   SUM(TRY_CAST(AMOUNTPAIDTOCPA as numeric(12,2))) AmountPaidToCPA
,   CASE WHEN [ERC Services] is null AND [179D Services] is null AND [R&D Services]>SUM(TRY_CAST(AMOUNTPAIDTOCPA as numeric(12,2))) THEN SUM(TRY_CAST(AMOUNTPAIDTOCPA as numeric(12,2))) ELSE  ([R&D Services]) END [R&D Services]
,   CASE WHEN [R&D Services] is null AND [179D Services] is null AND [ERC Services]>SUM(TRY_CAST(AMOUNTPAIDTOCPA as numeric(12,2))) THEN SUM(TRY_CAST(AMOUNTPAIDTOCPA as numeric(12,2))) ELSE  ([ERC Services]) END [ERC Services]
,   COALESCE(SUM(TRY_CAST(AMOUNTPAIDTOCPA as numeric(12,2))),0)
    -(COALESCE(CASE WHEN [ERC Services] is null AND [179D Services] is null AND [R&D Services]>SUM(TRY_CAST(AMOUNTPAIDTOCPA as numeric(12,2))) THEN SUM(TRY_CAST(AMOUNTPAIDTOCPA as numeric(12,2))) ELSE  ([R&D Services]) END,0))
    -(COALESCE(CASE WHEN [R&D Services] is null AND [179D Services] is null AND [ERC Services]>SUM(TRY_CAST(AMOUNTPAIDTOCPA as numeric(12,2))) THEN SUM(TRY_CAST(AMOUNTPAIDTOCPA as numeric(12,2))) ELSE  ([ERC Services]) END,0))
    [Other Services]
,   CASE WHEN [ERC Services OS] is null AND [179D Services OS] is null AND [R&D Services OS]>SUM(TRY_CAST(REMAININGDUETOCPA as numeric(12,2))) THEN SUM(TRY_CAST(REMAININGDUETOCPA as numeric(12,2))) ELSE  ([R&D Services OS]) END [R&D Services OS]
,   CASE WHEN [R&D Services OS] is null AND [179D Services OS] is null AND [ERC Services OS]>SUM(TRY_CAST(REMAININGDUETOCPA as numeric(12,2))) THEN SUM(TRY_CAST(REMAININGDUETOCPA as numeric(12,2))) ELSE  ([ERC Services OS]) END [ERC Services OS]
,   COALESCE(SUM(TRY_CAST(REMAININGDUETOCPA as numeric(12,2))),0)
    -(COALESCE(CASE WHEN [ERC Services OS] is null AND [179D Services OS] is null AND [R&D Services OS]>SUM(TRY_CAST(REMAININGDUETOCPA as numeric(12,2))) THEN SUM(TRY_CAST(REMAININGDUETOCPA as numeric(12,2))) ELSE  ([R&D Services OS]) END,0))
    -(COALESCE(CASE WHEN [R&D Services OS] is null AND [179D Services OS] is null AND [ERC Services OS]>SUM(TRY_CAST(REMAININGDUETOCPA as numeric(12,2))) THEN SUM(TRY_CAST(REMAININGDUETOCPA as numeric(12,2))) ELSE  ([ERC Services OS]) END,0))
    [Other Services OS]
,   SUM(TRY_CAST(REMAININGDUETOCPA as numeric(12,2)))  AmountOutStandingToCPA
,   COUNT(DISTINCT CASE WHEN COALESCE([ERC Services],0) + COALESCE([ERC Services OS],0)>0 THEN Client END) ERCClientPaid
FROM (

SELECT Distinct CM.*,COALESCE(MAX(a2.BillingState)OVER(Partition By CPA_Name),MAX(NBP.CPAState)OVER(Partition By CPA_Name),MAX(NBF.CPAState)OVER(Partition By CPA_Name),MAX(a.BillingState)OVER(Partition By CPA_Name),MAX(ad.State COLLATE SQL_Latin1_General_CP1_CI_AS)OVER(Partition By CPA_Name)) State
FROM
[FP&A]..CPAMatch CM

LEFT JOIN
(SELECT CPAEntity,CPAFirm
FROM
TE_3E_PROD_Clone..CPADetails_CCC
GROUP BY CPAEntity,CPAFirm)
 cd on cd.CPAEntity = CM.CPAID
 LEFT JOIN
 SalesforceClone..account a on a.Name = cd.CPAFirm COLLATE SQL_Latin1_General_CP1_CI_AS
 LEFT JOIN
 TE_3E_PROD_Clone..Payee P on P.NAme COLLATE SQL_Latin1_General_CP1_CI_AS = CM.CPA_NAME
 LEFT JOIN
 TE_3E_PROD_Clone..Site s on s.SiteIndex = P.CkSite
 LEFT JOIN
 TE_3E_PROD_Clone..Address ad on ad.AddrIndex = s.Address
LEFT JOIN
(SELECT CPAFirm,CPAState
FROM
TE_3E_PROD_Clone.[dbo].[NBI_Import_CPADetails_CCC]
GROUP BY CPAFirm,CPAState) NBF on NBF.CPAFirm = cd.CPAFirm
LEFT JOIN
TE_3E_PROD_Clone..Entity e on e.EntIndex = CM.CPAID
LEFT JOIN
(SELECT CPAName,CPAState
FROM
TE_3E_PROD_Clone.[dbo].[NBI_Import_CPADetails_CCC]
GROUP BY CPAName,CPAState
)  NBP on NBP.CPAName = e.DisplayName
LEFT JOIN
TE_3E_PROD_Clone.[dbo].[AG_CPAFirm] cf on cf.CPAFirmName COLLATE SQL_Latin1_General_CP1_CI_AS = CM.CPA_NAME
LEFT JOIN
SalesforceClone..account a2 on a2.id = cf.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS

) a
LEFT JOIN
#SLD SL on SL.CPA_NAME = a.CPA_NAME
LEFT JOIN
#SLO SO on SO.CPA_NAME = a.CPA_NAME
GROUP BY
    a.CPA_Name
,   State
,   ([R&D Services])
,   ([ERC Services])
,   [179D Services]
,   ([R&D Services OS])
,   ([ERC Services OS])
,   [179D Services OS]
) a
JOIN
#cpahm chm on chm.CPA_NAme = a.CPA_NAME
)b
WHERE rn = 1
GROUP BY
    [CPATop_AccountId]
,   [CPATop_Name]


drop table if exists #cpafinal;

       select
              o.Id [OpportunityId]
       ,      a.id [AccountId]
       ,      a.[Name] [AccountName]
       ,      c.CPA1_AccountId [CPAAccountId]
       ,      c.CPATop_Name [CPAName]
       ,      c.[CPATop_AccountId] [CPATop_AccountId]
       ,      con.[Name] [CPAContact]

       into #cpafinal
       from
              SalesForceClone.dbo.Account a
       left join
              SalesForceClone.dbo.Contact con on con.Id = a.CPA_Contact__c
       join
           SalesforceClone.dbo.Opportunity o on o.AccountId = a.Id
       left join
              #cpah c on c.CPA1_AccountId = COALESCE(a.CPA_Account__c,o.Amending_CPA_Firm_ID__c ,o.Referring_CPA_Firm_ID__c)
       group by
              o.Id
       ,      a.id
       ,      a.[Name]
       ,      c.CPA1_AccountId
       ,      c.CPATop_Name
       ,      con.[Name]
       ,      c.[CPATop_AccountId]


       drop table if exists #IsTalent;

       SELECT AccountId
       ,    CASE WHEN Service_Type__c IN ('External Audit','Tax Services','AMS','Business Advisory & Valuation') THEN 1 ELSE 0 END IsTalent
       into #IsTalent
       FROM
       SalesforceClone.dbo.Opportunity
       WHERE
       Service_Type__c IN ('External Audit','Tax Services','AMS','Business Advisory & Valuation')
       GROUP BY
       AccountId
       ,    CASE WHEN Service_Type__c IN ('External Audit','Tax Services','AMS','Business Advisory & Valuation') THEN 1 ELSE 0 END


drop table if exists #Credits;

select
       m.[MattIndex]
,      m.[Number] [Job_No]
,      m.OpportunityID_CCC [OpportunityId]
,      c.AccountID_CCC [AccountId]
,      m.[DisplayName] [Description]
,      m.[Client]
,      c.[DisplayName] [Company]
,      s.[Code] [State]
,      m.[EngSignedDate_CCC] [DateSigned]
,      year(m.[EngSignedDate_CCC]) [Year]
,      replace(ms.[Description],'CLOSING','Closing') [Status]
,      md.[Description] SL
,      case
              when m.MattCategory = 'NEW' then 'New'
              when m.MattCategory  = 'FU' then 'FU'
              when et.[Description] = 'New Business' then 'New'
              when et.[Description] = 'Follow - Up' then 'FU'
              when et.[Description] = 'Continuation' then 'Continuation'
              end [New_FU]
,     isnull ( coalesce(pc.[FinalCredits],0), 0) [FinalCredit]
,     Coalesce(pc.[Total],0) [Total]
,     case when md.[PracticeGroup] = 225 then coalesce(pc.[FinalCredits],0) else Coalesce(pc.[Total],0) end [Total2]
,      atk.[BD1] [PBD]
,      CASE WHEN BDMC.[MattIndex] is not null then 1 else 0 end [StudyCount]
,      cpa.[CPAAccountId]
,      coalesce(cpa.[CPAName],'Direct') [CPA]
,      cpa.[CPAContact] [CPA_Contact]
,      case when md.[PracticeGroup] = 225 then 1 else 0 end [isERC]
,   case when pc.[FinalCredits]!= 0 then 1 else pc.[FinalCredits] end isFinalCredits
,    CASE WHEN BDM.[MattIndex] is not null  then AdjustedWIP else 0 end AdjWIP
,   SUM(CASE WHEN BDM.[MattIndex] is not null  then AdjustedWIP else 0 end)OVER(Partition BY m.[Client]) ClientTotalWIP
,   [CPATop_AccountId]


into #Credits
from
       TE_3E_PROD_Clone.dbo.Matter m
LEFT JOIN
(SELECT BDM.[MattIndex],SUM(AdjustedWIP)AdjustedWIP,OpportunityIDCCC
FROM
[FP&A].BDM.BDMasterUnpublished BDM
WHERE
BDM.[EngTypeCCC] = 'New'
GROUP BY BDM.[MattIndex],OpportunityIDCCC) BDM on BDM.[MattIndex] = m.Mattindex
LEFT JOIN
(SELECT BDM.[MattIndex],OpportunityIDCCC
FROM
[FP&A].BDM.BDMasterUnpublished BDM
WHERE
BDM.[EngTypeCCC] = 'New' and BDM.OneTimeAdj = '0'
GROUP BY BDM.[MattIndex],OpportunityIDCCC) BDMC on BDMC.[MattIndex] = m.Mattindex
join
       TE_3E_PROD_Clone.dbo.Client c on c.ClientIndex = m.Client
left join
       TE_3E_PROD_Clone.[dbo].[Site] si on si.[SiteIndex] = c.[InvoiceSite]
left join
       TE_3E_PROD_Clone.[dbo].[Address] a on si.[Address] = a.[AddrIndex]
left join
       TE_3E_PROD_Clone.[dbo].[State] s on a.[State] = s.[Code]
join
       TE_3E_PROD_Clone.dbo.MattStatus ms on ms.code = m.MattStatus
left join
       TE_3E_PROD_Clone.[dbo].[EngType_CCC] et on et.[Code] = m.[EngType_CCC]
left join
       (
              select
                     row_number() over(partition by MatterLkUp order by effstart desc, nxStartDate desc, nxEndDate desc) [Rn]
              ,      PGDetHdr_CCCID
              ,      MatterLkUp
              ,     AdjWIP
              from
                     TE_3E_PROD_Clone.dbo.PGDetHdr_CCC
       ) ph on ph.MatterLkUp = m.MattIndex and ph.[Rn] = 1
left join
       (
              select
                     PGDetHdr
              ,      sum([FinalCredit]) [FinalCredits]
             ,       sum([Total]) [Total]
              from
                     TE_3E_PROD_Clone.dbo.PGDetChild_CCC
              group by
                     PGDetHdr
       ) pc on pc.PGDetHdr = ph.PGDetHdr_CCCID
left join
       (
              select
                     row_number() over(partition by md.MatterLkUp order by md.effstart desc, md.nxStartDate desc, md.nxEndDate desc) [Rn]
              ,      md.MatterLkUp
              ,      md.[PracticeGroup]
              ,      pg.[Description]
              from
                     TE_3E_PROD_Clone.dbo.MattDate md
              left join
                     TE_3E_PROD_Clone.dbo.PracticeGroup pg on pg.code = md.PracticeGroup
       ) md on md.MatterLkUp = m.MattIndex and md.[Rn] = 1
left join
       (
              select
                     row_number() over (partition by atk.[MatterLkUp] order by atk.[EffStart] desc) [Rn]
              ,      atk.[MatterLkUp]
              ,      atk.[BDTkpr1]
              ,      [FP&A].[dbo].[fGetFirstNameLastName](tk.DisplayName) [BD1]
              from
                     TE_3E_PROD_Clone.[dbo].[AssociatedTkprs_CCC] atk
              left join
                     TE_3E_PROD_Clone.dbo.Timekeeper tk on tk.TkprIndex = atk.BDTkpr1
       ) atk on atk.[MatterLkUp] = m.[MattIndex] and atk.[rn] = 1
left join
       #cpafinal cpa on cpa.OpportunityId = m.OpportunityID_CCC collate SQL_Latin1_General_CP1_CI_AS
where
    ms.[Description] not in ('Cancel NoEL Received','Cancelled - Duplicate')
    and md.[Description] not like '%-Z%'





drop table if exists [FP&A]..CPAMatchandCredits;

SELECT
    c.ParentCPA
,   a.BillingState State
,   a.BillingCity City
,   a.BillingPostalCode CPAZipCode
,   COALESCE(a.Alliance__c,'Direct') Alliance
,   bd.Name BD
,   rd.Name RD
,   credits.NoofClients
,   Credits.StudyCount
,   FA.ClientPaidLessExpenses
,   FA.AMountDueToCPA TotalAmountDuetoCPA
,   FA.AmountPaidToCPA TotalPaidtoCPA
,   FA.AmountOutStandingToCPA TotalAmountOutsandingtoCPA
,   COALESCE(FA.[ERC Services],0) + COALESCE(FA.[ERC Services OS],0) ERCAmountDueToCPA
,   COALESCE(FA.[ERC Services],0) ERCPaidToCPA
,   COALESCE(FA.[ERC Services OS],0) ERCAmountOutstandingToCPA
,   COALESCE(FA.[R&D Services],0) + COALESCE(FA.[R&D Services OS],0) [R&DAmountDueToCPA]
,   COALESCE(FA.[R&D Services],0) [R&DPaidToCPA]
,   COALESCE(FA.[R&D Services OS],0) [R&DAmountOutstandingToCPA]
,   COALESCE(FA.[Other Services],0) + COALESCE(FA.[Other Services OS],0) OtherAmountDueToCPA
,   COALESCE(FA.[Other Services],0) OtherPaidToCPA
,   COALESCE(FA.[Other Services OS],0) OtherAmountOutstandingToCPA
,   credits.AdjWIP [TotalAdjustedWIP]
,   credits.NoofBigClients
,   TotalCreditsDElivered
,   NoofERCClients
,   CASE WHEN ERCClientPaid>NoofERCClients THEN NoofERCClients ELSE ERCClientPaid END ERCClientPaid
,   ERCCreditsDElivered
,   bderc.Name ERCBD
,   rderc.Name ERCRD
,   RDNew
,   RDFU
,   ERC
,   CASE WHEN IsTalent = 1 THEN 'Yes' ELSE 'No' END IsTalent
INTO [FP&A]..CPAMatchandCredits
FROM
(SELECT CPATop_Name ParentCPA
    ,CPATop_AccountId ParentCPAId
    FROM
    #cpah c
    GROUP BY
     CPATop_Name
    ,CPATop_AccountId) c
JOIN
SalesforceClone..Account a on a.id = c.ParentCPAId
JOIN
SalesforceClone..[User] bd on bd.id = a.OwnerId
LEFT JOIN
SalesforceClone..[User] rd on rd.id = a.caller__c
LEFT JOIN
(SELECT [CPATop_AccountId]
,   COUNT(DISTINCT AccountId) NoofClients
,   COUNT(DISTINCT CASE WHEN ClientTotalWIP>100000 THEN AccountId END) NoofBigClients
,   SUM(AdjWIP) AdjWIP
,   SUM([FinalCredit]) TotalCreditsDElivered
,   COUNT(DISTINCT CASE WHEN isERC = 1 THEN AccountId END) NoofERCClients
,   SUM(DISTINCT CASE WHEN isERC = 1 THEN [FinalCredit] END) ERCCreditsDElivered
,   SUM(StudyCount)StudyCount

FROM
#Credits
GROUP BY
[CPATop_AccountId]
) Credits on credits.[CPATop_AccountId] = c.ParentCPAId
LEFT JOIN
#FAAmts FA on FA.CPATop_AccountId = c.ParentCPAId
LEFT JOIN
SalesforceClone..[User] bderc on bderc.id = a.[Technical_Director_ERC__c]
LEFT JOIN
SalesforceClone..[User] rderc on rderc.id = a.[ERC_Relationship_Director__c]
LEFT JOIN (
SELECT *
FROM
(
SELECT AccountID
,   Case
        when EngagementType = 150 AND PracticeGroup = 110 THEN 'RDNew'
        WHEN EngagementType = 125 AND PracticeGroup = 110 THEN 'RDFU'
        WHEN PracticeGroup = 225 THEN 'ERC'
        ELSE 'Other'
        END Type
,   ACFDD.FeePercentage
FROM
[TE_3E_PROD_Clone].dbo.[AG_CPAFirm] ACF
JOIN
[TE_3E_PROD_Clone].[dbo].[AG_CPAFirmDate] ACFD on ACF.CPAFirmIndex = ACFD.AG_CPAFirmLkUp and ACFD.NxEndDate  = '99991231' and ACFD.NxStartDate != '99991231'
JOIN
[TE_3E_PROD_Clone].[dbo].[AG_CPAFirmDateDet] ACFDD on ACFDD.CPAFirmDate = ACFD.AG_CPAFirmDateID
WHERE
 PracticeGroup IN (110,225)
)a
PIVOT (MAX(FeePercentage) FOR TYpe in ([RDNew],[RDFU],[ERC]))p
) Perc on Perc.AccountID COLLATE SQL_Latin1_General_CP1_CI_AS = c.ParentCPAId
LEFT JOIN
#IsTalent T on T.AccountId = c.ParentCPAId

END