-- Here's an attempt to remove the complex view vwDeals from the query. Run it and compare with the original.
-- Note that I can't find [AccountingDate] in the view. You will have to track this down.
-- I can't find which table [HFFTotalFee] is from, the reference isn't fully qualified in vwDeals so you will have to find out where this is from too.
-- There are some other changes in the FROM list.
SELECT FDC.DealID,
FDC.StageID,
FDC.PlatformID
INTO #ReportDealCore
FROM report.fnReportDealCore(@StartDate, @EndDate, @Type) AS FDC
Select Distinct
D.DealID,
D.DealName,
D.Amount,
--D.AccountingDate AS AccountingDate, -- not in vwDeals
D.Description,
--D.HFFTotalFee, -- don't know where this comes from
D.DealTypeID, -- join column found in Deals table
D.OfficeID, -- join column found in Deals table
PT.GroupID,
PT.PropertyType,
DPF.PlatformID,
DPF.Platform,
DS.StageID,
DS.Stage,
DT.DealTypeID,
DT.DealType,
AT.ActivityTypeID,
AT.ActivityType,
LO.InvestorID,
LO.Investor,
--LO.Amount,
C.City,
C.State,
case when C.CountryID <> 1 then 'Foreign' else R.Region end as Region,
MTRE.Producer as PrimaryProducer,
O.Name as HFFOffice
FROM #ReportDealCore AS FDC
INNER JOIN DealProperties AS DP ON DP.DealID = FDC.DealID AND DP.IsMainProperty = 1 AND DP.DateRemoved IS NULL
--INNER JOIN report.vwDeals AS D ON D.DealID = FDC.DealID -- ##
INNER JOIN Deals AS D ON D.DealID = FDC.DealID -- ##
INNER JOIN Assets AS A ON DP.AssetID = A.AssetID
LEFT JOIN Activities as ACT on ACT.DealID = FDC.DealID AND ACT.Type >= 2 AND ACT.Type <=7
LEFT JOIN ActivityTypes AS AT ON AT.ActivityTypeID = ACT.Type -- ##
LEFT JOIN DealStages AS DS ON DS.StageID = FDC.StageID -- ##
LEFT JOIN DealTypes DT ON DT.DealTypeID = D.DealTypeID -- ##
LEFT JOIN DealPlatforms AS DPF ON DPF.PlatformID = FDC.PlatformID -- ##
LEFT JOIN PropertyTypes AS PT ON PT.PropertyTypeID = DP.PropertyTypeID -- ##
LEFT JOIN Cities as C on C.CityID = A.CityID -- ##
LEFT JOIN States as S on S.StateID = C.StateID -- ##
--LEFT JOIN Regions as R ON isnull(C.RCARegionID, isnull(S.RCARegionID,S.RegionID)) = R.RegionID -- ##
LEFT JOIN Regions as R ON R.RegionID = COALESCE(C.RCARegionID,S.RCARegionID,S.RegionID)
LEFT JOIN Offices as O on O.OfficeID = D.OfficeID -- ##
LEFT JOIN vwReportMemberTypeResults_Employee AS MTRE ON MTRE.DealID = D.DealID -- ##
--LEFT JOIN vwReportMemberTypeResults_Client AS MTRC ON MTRC.DealID = D.DealID
--LEFT JOIN vwReportOnlyLoserOffers AS LO ON LO.DealID = D.DealID -- ##
INNER JOIN vwReportOnlyLoserOffers AS LO ON LO.DealID = D.DealID -- ##
WHERE D.AccountingDate BETWEEN @StartDate AND @EndDate
AND LO.isForeign = 1 -- this converts left join to inner join
ORDER BY D.DealID ASC, LO.InvestorID ASC, AT.ActivityTypeID DESC