Narrow down the recordset

Question

Post reply

Narrow down the recordset

Viewing 15 posts - 106 through 120 (of 124 total)

You must be logged in to reply to this topic. Login to reply

Jonnie Pettersson SSC Veteran Points: 257 More actions · Answer 1

Adam Haines (2/12/2008)

Not quite, if the repromote is the currentlevel then don't display. The script works but it is still not suppressing the repromote is at the current level.

--if the consultant has a repromote = the max PeriodEndDate

--then don't display

This is what the script is doing... I have tested this with test data and succesfully gotten the expected results. In the query I provided you, I am displaying all records that have a period date < the max period date. This would exclude repromote = max periodenddate.

This script worked perfectly for the test data I have. What is it doing differently for you?

I think I know what the problem is...if current level was a repromote, lets say three month ago...you have three rows and your script only exclude the last row but all three rows should be excluded...

Now it's morning here in Sweden and I'm quite tired 🙂 but I think this will work...not the most elegant one but as a starting point...

I'm not sure this is the fastest solution though...



SELECT * FROM(

SELECT DISTINCT ConB.ConsultantID,

		ConB.AchieveLevel,

		ConB.AchieveTitle,

		ConB.PeriodEndDate,

		ConB.RepFlag

FROM #C ConA

	INNER JOIN #C ConB ON ConA.ConsultantID = ConB.ConsultantID

	AND ConA.AchieveLevel = ConB.AchieveLevel

	AND ConA.PeriodEndDate < ConB.PeriodEndDate) as CON

WHERE EXISTS (SELECT * FROM #C b

		WHERE b.ConsultantID = CON.ConsultantID

		AND b.AchieveLevel < CON.AchieveLevel

		AND b.PeriodEndDate < CON.PeriodEndDate

AND NOT EXISTS (SELECT * FROM #C c

		WHERE c.ConsultantID = CON.ConsultantID

		AND c.PeriodEndDate > b.PeriodEndDate

		AND c.PeriodEndDate < CON.PeriodEndDate))

--if the consultant has a repromote < the max PeriodEndDate

--then display; otherwise, exclude because his current position

--is the repromote 

AND Con.Periodenddate <= (SELECT TOP 1 PeriodEndDate FROM (

			SELECT TOP 2 ConsultantID, AchieveLevel, Max(PeriodEndDate) AS PeriodEndDate

			FROM #C

			WHERE ConsultantID = Con.ConsultantID

			GROUP BY ConsultantID, AchieveLevel

			ORDER BY PeriodEndDate DESC) AS x

			ORDER BY PeriodEndDate ASC)

ORDER BY PeriodEndDate

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 2

alorenzini

SSCarpal Tunnel

Points: 4620

February 13, 2008 at 6:58 am

#777796

Please see attachment, it shows what the end product is doing which is a report services report. I am going to see if they can redeploy the report and see if that helps.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Adam Haines SSC-Insane Points: 23217 More actions · Answer 3

You lost me. Are you saying that you do not want a consultant id to display any repromotes if the latest repromote is the current level?

Additionally, what makes this even more confusing is the test consultant has current level of 3, according to the test data. Her ID should return all three records because neither 4 nor 5 is her current level, right.

My blog: http://jahaines.blogspot.com

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 4

I think I found the issue, it is not the repromote procedure its in the main proc (See bold). I think it needs to be changed because it is looking just for the 'X' in the repflag regardless of the Current AchieveLevel. THis is the proc that returns the recordset that populates the report I sent you in the last attachment.

ALTER PROCEDURE [consultantreports].[uspS_ConsultantDownline]

@ConsultantID AS nVarChar(20), @PeriodDate AS DateTime, @LineFilter as Varchar(50)

----

AS

Declare @MonthStartDt As DateTime

DECLARE @MonthEndDt AS DateTime

DECLARE @QuarterStartDt AS DateTime

DECLARE @QuarterEndDt AS DateTime

Declare @Year AS DateTime

Declare @PeriodStartDt As DateTime

Declare @PeriodEndDt as DateTime

Declare @BumpupDate As Datetime

DECLARE @SQLStr nVarchar(100)

DECLARE @CurrMonthStartDt DateTime

DECLARE @CurrMonthEndDt DateTime

-- Breakdown @PeriodDate into Quarter and Months

SET @QuarterStartDt = DATEADD(quarter, DATEDIFF(quarter, 0, @periodDate), 0)

Set @QuarterEndDt = DATEADD(quarter, DATEDIFF(quarter, -1, @PeriodDate), -1)

SET @Year = DATEADD(yy, DATEDIFF(yy,0,@PeriodStartDt), 0)

SET @MonthStartDt = DATEADD(month, DATEDIFF(month, 0, @PeriodDate), 0)

Set @MonthEndDt = DATEADD(month, DATEDIFF(month, -1, @PeriodDate), -1)

SET @CurrMonthStartDt = DATEADD(month, DATEDIFF(month, 0, GETDate()), 0)

Set @CurrMonthEndDt = DATEADD(month, DATEDIFF(month, -1, GetDate()), -1)

SET @LineFilter = @LineFilter

IF @MonthEndDt > GETDATE()

BEGIN

SET @MonthEndDt = GETDATE()

END;

-- Declare the Downline Recursive Query

With downline (ConsultantID,EffectiveDate, ConsultantName,SponsorID,SponsorName,DownLineLevel,

ConsultantXId,SponsorXID,Active, DeactivationDate,BumpupDate,NACDate,CurrentLevelAchieveDate, CurrentLevelXID, StatusID)

AS

(

-- Anchor member defintion

SELECT A.ConsultantID

,A.EffectiveDate

,A.FirstName + ' ' + A.LastName as ConsultantName

,CAST(A.SponsorID AS Nvarchar(MAX))

,'' As SponsorName

,0 as DownLineLevel

,A.ConsultantXID

,A.SponsorXID

,A.Active

,A.DeactivationDate

,A.BumpupDate

,A.NACDate

,A.CurrentLevelAchieveDate

,A.CurrentLevelXID

,A.StatusID

FROM dbo.uvwConsultantDownLine A with (nolock)

WHERE A.ConsultantID = @ConsultantID

AND @MonthEndDt Between a.EffectiveDate and A.EffectiveEndDate

--AND A.StatusID NOT IN ('Inactive') -- 1 Record

UNION ALL

--Recursive member definition

SELECT A.ConsultantID

,A.EffectiveDate

,A.FirstName + ' ' + A.LastName as ConsultantName

,cast(A.SponsorID as nvarchar(MAX)) AS SponsorID

,'' AS SponsorName

,DownLineLevel + 1

,A.ConsultantXID

,A.SponsorXID

,A.Active

,A.DeactivationDate

,A.BumpupDate

,A.NACDate

,A.CurrentLevelAchieveDate

,A.CurrentLevelXID

,A.StatusID

FROM dbo.uvwConsultantDownLine AS A with (nolock)

INNER JOIN DownLine AS B ON

A.SponsorID = B.ConsultantID

WHERE @MonthEndDt Between a.EffectiveDate and A.EffectiveEndDate

--AND A.StatusID NOT IN ('Inactive')

) -- Appropriate records

-- Create the Temp table #Downline that returns the CTE results

SELECT A.DownLineLevel

,A.ConsultantID

,A.EffectiveDate

,UPPER(RTRIM(A.ConsultantName)) AS ConsultantName

-- ,C.EmailAddress

,D.Title AS AchievedTitle

, CONVERT(NVARCHAR(MAX),A.SponsorID) AS SponsorID

,A.SponsorName

,A.ConsultantXID

,A.SponsorXID

,A.Active

,A.DeactivationDate

,A.BumpupDate

,A.NACDate

,A.CurrentLevelAchieveDate

,A.CurrentLevelXID

,A.StatusID

INTO #Downline

FROM DownLine AS A with (noLock)

LEFT OUTER JOIN SharedDimension.dbo.DimConsultantTitle AS D ON A.CurrentLevelXID = D.XID

WHERE DownLineLevel <= 4

GROUP BY A.ConsultantID

,A.EffectiveDate

,A.ConsultantName

,A.SponsorID

,A.SponsorName

,DownLineLevel

-- ,C.BumpUpDate

-- ,C.EmailAddress

,D.Title

,A.ConsultantXID

,A.SponsorXID

,A.Active

,A.DeactivationDate

,A.BumpupDate

,A.NACDate

,A.CurrentLevelAchieveDate

,A.CurrentLevelXID

,A.StatusID

DECLARE @CONSULTANTIDS NVARCHAR(MAX)

SET @CONSULTANTIDS =

(SELECT DISTINCT RTRIM(ConsultantID) + '|'

FROM #DownLine

FOR XML PATH(''))

CREATE TABLE #Temp (

ConsultantID CHAR(20),

AchieveLevel CHAR(2),

AchieveTitle CHAR(50),

PeriodEndDate DATETIME,

RepFlag Varchar(2)

)

--SELECT @CONSULTANTIDS

INSERT INTO #Temp

EXEC [consultantreports].uspS_DownlineRepromotions2 @CONSULTANTIDS

--Select * from #Temp

Select 'OrigConsID' = @ConsultantID

,(Select ConsultantName FROM #Downline d

WHERE D.ConsultantID = @ConsultantID) AS 'OrigConsName'

,D.ConsultantID

,D.Downlinelevel

,D.ConsultantName

,D.SponsorName

,D.EffectiveDate

,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((SELECT ISNULL(SUM(v.PartOneTotal),0) FROM uvw_DownlineOrder V

INNER JOIN SharedDimension.dbo.DimOrderType AS T ON

V.OrderTypeXID = T.XID

WHERE (T.OrderType NOT In ('Credit'))

AND D.ConsultantID = V.ConsultantID

AND V.OrderCreateDate Between @QuarterStartDt And @QuarterEndDt),0) AS QuarterToDate_Volume

,ISNULL((SELECT ISNULL(SUM(v.PartOneTotal),0) FROM uvw_DownlineOrder V

INNER JOIN SharedDimension.dbo.DimOrderType AS T ON

V.OrderTypeXID = T.XID

WHERE (T.OrderType NOT In ('Credit'))

AND D.ConsultantID = V.ConsultantID

AND V.OrderCreateDate Between @MonthStartDt And @MonthEndDt),0) AS MonthToDate_Volume

,D.Active

,ISNULL(r.RepFlag,' ') AS RepFlag

,'StatusID' = CASE

WHEN StatusID ='Active' THEN ''

WHEN StatusID = 'Home Office' THEN ''

WHEN StatusID = 'New, Non Active Consultant' THEN ''

ELSE StatusID

END

,CONVERT(Varchar(10),D.NACDate,101) as AgreementDate

,'ShowFlag' = CASE

WHEN @PeriodDate Between @CurrMonthStartDt AND @CurrMonthEndDt THEN 'X'

ELSE ' '

END

INTO #DLLevel from #Downline D with (nolock)

LEFT OUTER JOIN uvw_DownlineOrder O ON D.ConsultantID = O.ConsultantID

LEFT Outer JOIN #Temp r ON d.ConsultantID = r.ConsultantID AND r.repflag = 'X'

WHERE (D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)

AND r.AchieveLevel >= 4

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

ORDER BY 2