We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Select D.ConsultantID 
,D.Downlinelevel
,D.ConsultantName
,D.EffectiveDate
,B1.FirstName + ' ' + B1.LastName AS SponsorName
,D.SponsorID
,D.AchievedTitle
,ISNULL(CONVERT(CHAR(10),D.CurrentLevelAchieveDate,101),'') AS AchieveDate
,ISNULL(Convert(Char(10),D.BumpupDate,101),'') as BumpupDate
,ISNULL(Sum(CASE 
		WHEN O.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt THEN O.PartOneTotal
		ELSE 0
	END),0) AS QuarterToDate_total
,ISNULL(Sum(CASE 
		WHEN O.OrderCreateDate Between @MonthStartDt And @MonthEndDt THEN O.PartOneTotal
		ELSE 0
	END),0) AS MonthToDate_Total
,D.ConsultantXID
,D.SponsorXID
,ISNULL(QuarterVolume,0) AS QuarterToDate_Volume
,ISNULL(MonthVolume,0) AS MonthToDate_Volume
,D.Active
,ISNULL(r.RepFlag,' ') AS RepFlag 
,CASE StatusID
		WHEN 'Active' THEN ''
		WHEN 'Home Office' THEN ''
		WHEN 'New, Non Active Consultant' THEN ''
		ELSE StatusID
	END AS StatusID
,CONVERT(Varchar(10),D.NACDate,101) as AgreementDate
,CASE WHEN @PeriodDate Between @CurrMonthStartDt AND @CurrMonthEndDt THEN 'X'
			ELSE ' '
	END AS ShowFlag

INTO #DLLevel 

-- not much point in nolock on a temp table. Hint only applies to a single table
FROM #Downline D with (nolock) 
	LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID
	LEFT OUTER JOIN Repromotes r ON d.ConsultantID = r.ConsultantID AND r.repflag = 'X'

	-- Was there a reason for the uvwConsultantDownLine table?
	-- Took it out of the subquery, because it didn't appear to be necessary. 
	-- Hence subquery reduced to one table and joined in.
	LEFT OUTER JOIN dbo.consultant AS B1 ON D.SponsorID = B1.ConsultantID
	-- Added from select clause. Merged 2 subqueries into 1, since 1 was a subset of the other
	LEFT OUTER JOIN (SELECT ConsultantID,
				SUM(v.PartOneTotal) AS QuarterVolume, 
				SUM (CASE WHEN V.OrderCreateDate Between @MonthStartDt And @MonthEndDt THEN PartOneTotal ELSE 0 END) AS MonthVolume
			FROM uvw_DownlineOrder V
				INNER JOIN SharedDimension.dbo.DimOrderType AS T ON	V.OrderTypeXID = T.XID
			WHERE (T.OrderType != 'Credit')	AND V.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt
			GROUP BY ConsultantID) VT ON D.ConsultantID = VT.ConsultantID

-- Missing brackets here. AND has precedence over OR. 
WHERE ((D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt))
	AND r.AchieveLevel >= 4 

-- Might need some modification
GROUP BY D.ConsultantID ,D.Downlinelevel
,D.ConsultantName
,D.SponsorName 
,D.EffectiveDate
,D.SponsorID
,D.AchievedTitle
,D.CurrentLevelAchieveDate
,D.BumpupDate 
,D.NACDate
,D.ConsultantXID
,D.SponsorXID
,D.Active
,r.RepFlag
,D.StatusID

We walk in the dark places no others will enter
We stand on the bridge and no one may pass