February 12, 2008 at 3:50 pm
Some may have repromotes and some may not. When I executed the following query:
EXEC [consultantreports].uspS_DownlineRepromotions2 '0004073|0007039|0017122|0024661|0025122|0032877|0036461|0038602|0040483|0042212|0052266|0053254|0054365|0056485|0056593|0058200|0059601|0060183|0060551|0061561|0061798|0062514|0063969|0065526|0067565|0067785|0067892|0069314|'
This is the result set:
0004073 06Senior Team Manager 2005-06-30 00:00:00.000D
0036461 04Team Leader 2006-06-30 00:00:00.000D
0004073 06Senior Team Manager 2006-10-31 00:00:00.000D
0036461 04Team Leader 2006-12-31 00:00:00.000D
Kind of messed up but I get 6 records back which is correct. Not all the consultantID had repromotions.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 4:10 pm
Try coping your create table from within the stored procedure and replace the create table #test. Then rename the table to #temp. Maybe something is off with #temp's definition.
February 12, 2008 at 4:16 pm
Same error. THe line, I think, that the error indicates in the message is
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))
ORDER BY PeriodEndDate
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 4:42 pm
I figured out what the issue was, I was calling a old version of the proc. It seems to be working now but this lead to another question. I also need to ignore a repromote if it is there currentlevel.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 5:52 pm
INSERT INTO #C
SELECT a.ConsultantID
,a.AchieveLevel
,a.AchieveTitle
,a.PeriodEndDate
, a.Repflag
FROM Volume a
INNER JOIN (SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]
FROM @x.nodes('//i') x(i) ) as b
on a.ConsultantID = b.ConsultantID
WHERE b.ConsultantID IS NOT NULL
AND a.Level <> (SELECT TOP 1 c.Level
FROM Volume c
WHERE c.ConsultantID = a.ConsultantID
ORDER BY c.PeriodEndDate DESC)
February 12, 2008 at 6:25 pm
Did not work. I think for this clause we need something like
SELECT a.ConsultantID
,a.AchieveLevel
,a.AchieveTitle
,a.PeriodEndDate
, a.Repflag
FROM Volume a
INNER JOIN (SELECT x.i.value('.', 'VARCHAR(20)') AS [ConsultantID]
FROM @x.nodes('//i') x(i) ) as b
on a.ConsultantID = b.ConsultantID
AND a.AchieveLevel <> (SELECT TOP 1 c.AchieveLevel
FROM Volume c
WHERE c.ConsultantID = a.ConsultantID AND MAX(c.PeriodEnDate)
ORDER BY c.PeriodEndDate DESC)
WHERE b.ConsultantID IS NOT NULL
Update #C
Set RepFlag='X'
But I don't know if that is doable.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 6:34 pm
I edited my post try having the filter in the where clause. I tested this locally with success. Actually I tested both versions successfully.
February 12, 2008 at 6:39 pm
i had to add the distinct clause to make it work for me.
INSERT INTO @C
SELECT distinct
you may have to do this too.
February 12, 2008 at 6:43 pm
Your right it is working but not quite right.
INSERT INTO #C
SELECT a.ConsultantID
,a.AchieveLevel
,a.AchieveTitle
,a.PeriodEndDate
, a.Repflag
FROM Volume a
INNER JOIN (SELECT x.i.value('.', 'VARCHAR(20)') AS [ConsultantID]
FROM @x.nodes('//i') x(i) ) as b
on a.ConsultantID = b.ConsultantID
AND a.AchieveLevel <> (SELECT TOP 1 c.AchieveLevel
FROM Volume c
WHERE c.ConsultantID = a.ConsultantID
ORDER BY c.PeriodEndDate DESC)
WHERE b.ConsultantID IS NOT NULL
But its not supressing the current AchieveLevel if its a repromote.
This is the scenerio, a consultant has repromoted serveral times (that is working) but in the event that the last repromote leave them at their current level then we don't want to see it as a repromote.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 6:46 pm
I get what you are saying now. I thought that you didnt want to see any of the repromotes at the current level.
February 12, 2008 at 6:48 pm
Tried adding the DISTINCT clause and it still doing the same. I am think there needs to be a tie between the AchieveLevel and the latest periodenddate in order to get the clause to work right.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 8:07 pm
To accomplish what you want you will need to filter the query not the insert statement.
your query should look like this:
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 d.Periodenddate
FROM #C d
WHERE d.ConsultantID = Con.ConsultantID
ORDER BY d.PeriodEndDate DESC)
ORDER BY PeriodEndDate
February 12, 2008 at 8:09 pm
You should remove the additional where clause we put into the insert statement and use the code above instead.
This should set you right 😉
February 12, 2008 at 8:55 pm
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
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 12, 2008 at 9:23 pm
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?
Viewing 15 posts - 91 through 105 (of 124 total)
You must be logged in to reply to this topic. Login to reply