February 10, 2008 at 9:07 am
Mark,
Good code, I knew this could be done with a CTE, although I am just starting to learn/use them. I took the logic from your code and transformed the query into a non CTE/cursor query.
Thanks for opening my eyes to a new method 😀
SELECT *
FROM(
SELECT DISTINCT ConB.ConsultantID, ConB.Level, ConB.Title, ConB.PeriodEndDate
FROM @C ConA INNER JOIN @C ConB ON ConA.ConsultantID = ConB.ConsultantID
AND ConA.Level = ConB.Level AND ConA.PeriodEndDate < ConB.PeriodEndDate
) as CON
WHERE EXISTS (SELECT * FROM @C b
WHERE b.ConsultantID = CON.ConsultantID
AND b.Level < CON.Level
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
February 11, 2008 at 6:02 am
I would have used Group By and Having Clauses
Select Title, Max(PeriodEndDate) as PeriodEndDate
from YourTableName
group by ConsultantId, Level, Title
having Count(PeriodEndDate) > 1
February 11, 2008 at 6:50 am
Ok... what would you do to return more information from each row using that method?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 11, 2008 at 7:11 am
Every column in the select list has to be in the Group By Clause except columns involved in aggregates. Ex:
Select A, B, C, SUM(D) as Dsum
from Table
Group By A, B, C --Only column D is involved in an aggregate...hence Group By A, B and C
Select A, B, COUNT(C) as Ccount, SUM(D) as Dsum
from Table
Group By A, B --Column C and D is involved in aggregates...hence Group By A and B
Select A, B, C, SUM(D) as Dsum
from Table
Group By A, B --C should be in the group by clause and it will result in an error...
"Column C is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
February 11, 2008 at 7:12 am
I would have used Group By and Having Clauses
Select Title, Max(PeriodEndDate) as PeriodEndDate
from YourTableName
group by ConsultantId, Level, Title
having Count(PeriodEndDate) > 1
Unfortuantely, this does not produce the desired result. The desired result is to display all details for levels that have already been achieved. For example, a person is at a level 1 then gets promoted to 2,3,4 etc. Next year the person gets demoted to level 3. The OP wants to capture when the user gets promoted back to level 4. Using a group by,with a having clause, simply cannot deliver the desired result alone.
February 11, 2008 at 7:14 am
Additonally, the results of a group by are skewed because a person can maintain a level for months at a time, thus the count would be false because it would be > than 1.
February 11, 2008 at 8:35 am
You're correct...I didn't understand the issue...but...do I understand the issue now?
If you copy your own code in an earlier post...
Declare @C Table....
Some inserts....
Down to...Declare @r table ... eg skip the while loop and try this instead...
Select c1.ConsultantID, c1.Level, c2.Level, c1.Title, c1.PeriodEndDate
from @C c1 join @C c2 on c1.Row_ID = (c2.Row_Id + 1)
where c2.level < c1.level
and exists (Select NULL from @C where Level = c1.Level and PeriodEndDate < c1.PeriodEndDate)
February 11, 2008 at 9:05 am
Thanks everybody for there replies. I trying the CTE version of it and it seems to be working. It has to go throught the Volume table which contains 2.7 million rows so it still executing. But I did test it with a IN list clause for three consultantID and it seems to work fine. Any suggests on how to speed this up. I put a overing index on the 4 columns from the volume table but it doesn't seem to have helped much. The quesy is up to 9 minutes now. THis is the code as it sits now.
DECLARE @C TABLE(Row_ID INT IDENTITY(1,1),
ConsultantID CHAR(20),
AchieveLevel CHAR(2),
AchieveTitle CHAR(50),
PeriodEndDate DATETIME,
RepFlag char(1))
INSERT INTO @C
SELECTConsultantID
,AchieveLevel
,AchieveTitle
,PeriodEndDate
,RepFlag
FROM volume a
WHERE EXISTS
(SELECT * FROM volume b WHERE
b.consultantid=a.consultantid);
--AND ConsultantID IN ('0002354','0002617');
WITH Promotions AS
(SELECT a.ConsultantID,
a.AchieveLevel,
a.AchieveTitle,
a.PeriodEndDate,
ROW_NUMBER() OVER(PARTITION BY a.ConsultantID, a.AchieveLevel ORDER BY a.PeriodEndDate) as rn
FROM @C a
WHERE EXISTS (SELECT * FROM @C b
WHERE b.ConsultantID=a.ConsultantID
AND b.AchieveLevel<a.AchieveLevel
AND b.PeriodEndDate<a.PeriodEndDate
AND NOT EXISTS (SELECT * FROM @C c
WHERE c.ConsultantID=a.ConsultantID
AND c.PeriodEndDate>b.PeriodEndDate
AND c.PeriodEndDate<a.PeriodEndDate)))
SELECT ConsultantID,
AchieveLevel,
AchieveTitle,
PeriodEndDate
FROM Promotions
WHERE rn>1
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 11, 2008 at 9:06 am
If you copy your own code in an earlier post...
Declare @C Table....
Some inserts....
Down to...Declare @r table ... eg skip the while loop and try this instead...
Select c1.ConsultantID, c1.Level, c2.Level, c1.Title, c1.PeriodEndDate
from @C c1 join @C c2 on c1.Row_ID = (c2.Row_Id + 1)
where c2.level < c1.level
and exists (Select NULL from @C where Level = c1.Level and PeriodEndDate < c1.PeriodEndDate)
Good solution 😉 Very simplistic and efficient. Good work.
February 11, 2008 at 9:07 am
Your best bet would to be to use a non CTE/recursive solution like the one I posted or even better the one Jonnie posted.
February 11, 2008 at 9:33 am
Thinking about this, is there a way to call a stored procedure from a SELECT Statement?
Something like Select ConsultantID, AChieveLevel,
AchieveTItle, usp_GetRepromote(@ConsultantID), ENdPeriodDate
FROM Volume
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 11, 2008 at 10:25 am
No, you would have to make it a function that returns a table. You cannot use a stored procedure in a select statement, but you can; however, use a function. The function would have to retrun a table because a select statement cannot return more than one value.
You could do something along these lines:
SELECT a.ConsultantID,
a.AChieveLevel,
a.AchieveTItle,
b.Repromote,
a.ENdPeriodDate
FROM Volume a
INNER JOIN dbo.myfunctionname(@ConsultantID) b
on a.ConsultantID = b.ConsultantID
February 11, 2008 at 10:29 am
Upon futher invesitgation I don't think a UDF will work here as a UDF can not perform DMLs statement against a base table. SO I am back to square one.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
February 11, 2008 at 10:36 am
You could dump the results of the stored procedure into a temp table and then inner join it to the select.
INSERT INTO #temp
EXEC MyStoredProc 'TestVar'
SELECT a.ConsultantID,
a.AChieveLevel,
a.AchieveTItle,
b.Repromote,
a.ENdPeriodDate
FROM Volume a
INNER JOIN #temp b
on a.ConsultantID = b.ConsultantID
February 11, 2008 at 11:00 am
I am trying you suggestion but it keeps erroring:
DECLARE@ConsultantIDASnVarChar(50)
SET @ConsultantID = '0002617';
INSERT INTO #Temp
EXEC uspS_Repromotes @COnsultantID
Msg 208, Level 16, State 0, Line 4
Invalid object name '#Temp'.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 15 posts - 16 through 30 (of 124 total)
You must be logged in to reply to this topic. Login to reply