February 13, 2008 at 2:05 am
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
February 13, 2008 at 6:58 am
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!
February 13, 2008 at 7:38 am
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.
February 13, 2008 at 7:43 am
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]
@ConsultantIDASnVarChar(20), @PeriodDate AS DateTime, @LineFilter as Varchar(50)
----
AS
Declare @MonthStartDt As DateTime
DECLARE @MonthEndDt AS DateTime
DECLARE @QuarterStartDtASDateTime
DECLARE @QuarterEndDt AS DateTime
Declare @YearASDateTime
Declare @PeriodStartDt As DateTime
Declare @PeriodEndDt as DateTime
Declare @BumpupDate As Datetime
DECLARE @SQLStrnVarchar(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
SELECTA.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
SELECTA.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
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
-- ALL Level Filters
IF @LineFilter = ('Lines 1, 2, 3 and 4')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'0' ) + ' AND ' + CONVERT(Varchar(2),'4' )
END
-- Only Line 1 Filter
IF @LineFilter = ('Only Line 1')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'1' ) + ' AND ' + CONVERT(Varchar(2),'1' )
END
-- Only Line 2 Filter
IF @LineFilter = ('Only Line 2')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'2' ) + ' AND ' + CONVERT(Varchar(2),'2' )
END
-- Only Line 3 Filter
IF @LineFilter = ('Only Line 3')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'3' ) + ' AND ' + CONVERT(Varchar(2),'3' )
END
-- Only Line 3 Filter
IF @LineFilter = ('Only Line 3')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'3' ) + ' AND ' + CONVERT(Varchar(2),'3' )
END
-- Lines 1 and 2 Filter
IF @LineFilter = ('Lines 1 and 2')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'1' ) + ' AND ' + CONVERT(Varchar(2),'2' )
END
-- Lines 1, 2, and 3 Filter
IF @LineFilter = ('Lines 1, 2 and 3')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'1' ) + ' AND ' + CONVERT(Varchar(2),'3' )
END
-- Lines 3 and 4 Filter
IF @LineFilter = ('Lines 3 and 4')
BEGIN
SET @SQLStr = 'SELECT * FROM #DLLevel WHERE Active = 1 '
SET @SQlstr = @SQlstr + ' AND DownlineLevel BETWEEN ' + CONVERT(Varchar(2),'3' ) + ' AND ' + CONVERT(Varchar(2),'4' )
END
-- Execute the string
EXEC sp_executesql @SQLstr;
-- Drop the temp table
--Select * FROM #Downline
--Select * FROM #DLLevel
DROP TABLE #Downline
DROP TABLE #DLLevel
DROP TABLE #Temp
--TRUNCATE TABLE Repromotes
--Select * from Repromotes
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 13, 2008 at 11:05 am
I really do not know enough about this main process to give a concise answer. I would start by looking at why are joining with a left outer join, is this necessary? You are saying give me everything from table d even though r (#temp) may or may not have a match.
And what exactly does the 'X' mean for repflag. Are all the repflags 'X's?
February 13, 2008 at 11:58 am
yes, everything from table d needs to come across. Maybe I don't need the LeftOuter Join to the #temp table but it was what I thought would work because that the table with repromote data in it. The repflag column in the #Temp will always have an 'X'. It is there for the report.
So I am thinking that somewhere in this code I need to check between the #Temp table and the table d to make sure that if there is a repromote in #Temp that if it is a CurrentLevel to suppress the 'X' in the Repflag column.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 13, 2008 at 12:22 pm
alorenzini (2/13/2008)
.... repromote in #Temp that if it is a CurrentLevel to suppress the 'X' in the Repflag column.
Have you tried the code in my earlier post? Didn't it work?
February 13, 2008 at 12:36 pm
Yes I tried that code and the works fantastically in the Repromote Proc. I think the issue lies in the fact that in the Main proc, its not joined properly or something to the #Temp table.Because it's displaying an 'X' as long as there is records being return by repromote proc.
In the this specific case, the consultant is at Senior Consultant Level currently but because she has a couple repromote in the #Temp table its showing up as an 'X' even at the current level and it should not. Also the Senior Consultant is not even in the #Temp table.
In the table d you will find a column called CurrentLEvelXID which is the consultants current level and I think that needs to be used in some relation with the #Temp table but I can't seem to quite get it.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 13, 2008 at 12:58 pm
Hmm, I'm not sure what you are doing in the main sproc but as I see it, you have to left join with more columns...
LEFT Outer JOIN #Temp r ON d.ConsultantID = r.ConsultantID
AND d.AchievedLevel = r.AchievedLevel
AND d.PeriodEndDate = r.PeriodEndDate
...or whatever the correct column name is 😉
February 13, 2008 at 1:26 pm
Can we do something along these lines although my logic is not right but may be it will help clarify what I am after (See Bold):
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
,(Select RepFlag from #Temp WHere EXISTS (Select Repflag from #temp WHERE r.ConsultantID=d.consultantID) AND NOT EXIST (Select Repflag FROM #temp WHERE r.consultantID = d.ConsultantID AND d.CurrentLevelXID = r.AchieveLevel AND r.PeriodEndDate <> MAX(r.PeriodEndDate)) 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
,D.CurrentLevelXID
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 WHERE (D.Active = 1) OR (D.DeactivationDate BETWEEN @MonthStartDt AND @MonthEndDt)
AND d.CurrentLevelXID >= 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
,D.CurrentLEvelXID
,r.AchieveLevel
ORDER BY 2
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 13, 2008 at 1:36 pm
Another though maybe this is a place for some sort of CASE statement like:
CASE Repflag =
WHEN RepFlag = (Select Repflag From #Temp WHere r.ConsultantID = d.COnsultantID ) THEN 'x'
WHEN repFlag =(Select Repflag From #Temp WHere r.ConsultantID = d.COnsultantID AND d.CurrentLevelXID = r.AchieveLevel AND r.PeriodEndDate <> MAX(r.PeriodENdDate ) THEN ' '
ELSE
' '
or something along those lines.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 13, 2008 at 1:45 pm
Now I'm lost 😉
if you implemented this...
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)
...you should not have any repromotions at the current consultant level...
February 13, 2008 at 1:48 pm
A case statement is more along the lines of what I was thinking. I was thinking something like this.
CASE
WHEN d.CurrentLevelXID = r.AchieveLevel AND
r.PeriodEndDate < (SELECT MAX(a.PeriodEndDate)
FROM #temp a
WHERE a.ConsultantID = r.ConsultantID) THEN
'X'
ELSE
''
END AS [RepFlag]
February 13, 2008 at 1:52 pm
Johnnie,
The stored procedure we helped him with is returning the correct results however he has extended it to work inside his main process. The main process is not marking the correct repflags becuase of the way it was implemented. He is now trying to adjust the main procedure to accomodate the results of our stored procedure.
February 13, 2008 at 1:52 pm
Please correct me, if I have it wrong.
Viewing 15 posts - 106 through 120 (of 124 total)
You must be logged in to reply to this topic. Login to reply