December 10, 2007 at 8:58 am
I have the following table:
Selecta.ConsultantID,
a.PeriodEndDate,
a.AchieveLevel,
a.AchieveTitle,
a.ConsultantName,
a.SponsorID,
a.SponsorName,
a.PaidLevel,
a.PaidTitle,
'RepromoteFlag' = NULL
FROM Volume a
The scenerio is based on the AchieveLevel and PeriodEndDate, if the AchieveLevel (20,30,40,50,60,70) is less than the previous Achievelevel for a give periodEndDate, I need to populate the RepromoteFlag column with a 'X'.
I am not sure how to go about doing this.
Thanks
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 10, 2007 at 9:16 am
What do you mean based off Previous AchieveLevel?
Your talking about comparing values from two different rows?
Please post sample data and desired output so we can see what your looking for..
Thx
😎
December 10, 2007 at 9:25 am
Yes, I am talking about comparing two different rows, this is what I need to see:
ID PeriodEndDate AchieveLev Title RepFlag
0002566 2006-09-30 00:00:00.00006 Sr Team Manager
0002566 2006-08-31 00:00:00.00006 Sr Team Manager
0002566 2006-07-31 00:00:00.00006 Sr Team Manager
0002566 2006-06-30 00:00:00.00006 Sr Team Manager
0002566 2006-05-31 00:00:00.00006 Sr Team Manager
0002566 2006-04-30 00:00:00.00006 Sr Team Manager
0002566 2006-03-31 00:00:00.00006 Sr Team Manager
0002566 2006-02-28 00:00:00.00006 Sr Team Manager
0002566 2006-01-31 00:00:00.00006 Sr Team Manager
0002566 2005-12-31 00:00:00.00006 Sr Team Manager
0002566 2005-11-30 00:00:00.00006 Sr Team Manager
0002566 2005-10-31 00:00:00.00006 Sr Team Manager
0002566 2005-09-30 00:00:00.00006 Sr Team Manager
0002566 2005-08-31 00:00:00.00006 Sr Team Manager
0002566 2005-07-31 00:00:00.00006 Sr Team Manager
0002566 2005-06-30 00:00:00.00006 Sr Team Manager
0002566 2005-05-31 00:00:00.00006 Sr Team Manager
0002566 2005-04-30 00:00:00.00006 Sr Team ManagerX
0002566 2005-03-31 00:00:00.00007 Team Mentor
0002566 2005-02-28 00:00:00.00007 Team Mentor
0002566 2005-01-31 00:00:00.00007 Team Mentor
Based on the Achievelevel and Period enddate I need to mark an X if it is a demotion
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 10, 2007 at 10:10 am
Try this on:
select a.*
update a
set Repflag='X'
from volume a
where exists (
select *
from volume b
where
b.id=a.id
and b.perEndDate=dateadd(day,-1,dateadd(month,-1,dateadd(day,1,a.perenddate)))
and a.AchieveLev<b.achieveLev)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 10, 2007 at 10:30 am
Thank you, that was it. You nailed it. Thank you:D
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 10, 2007 at 10:36 am
Happy to help!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 10, 2007 at 11:51 am
I was just told that the criteria changed:
It's only to be flagged if they go to a lower level then back up to the same level so in this case it would be the person went from a 60 to a 50 then back up to a 60. That would be a re-promote. Does that make sense?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 10, 2007 at 12:35 pm
I'm sure we could collapse this into a single statement, but...
Careful about the "reset" at the beginning, in case you're using the repflag for anything else.
update volume set repflag=''
update a
set Repflag='D'
from volume a
where exists (
select *
from volume b
where
b.id=a.id
and b.perEndDate=dateadd(day,-1,dateadd(month,1,dateadd(day,1,a.perenddate)))
and a.AchieveLev>b.achieveLev
)
update a
set Repflag='X'
from volume a
where exists (
select *
from volume b
where Repflag='D' and
b.id=a.id
and b.perEndDate<a.perenddate
and a.AchieveLev=b.achieveLev)
update volume set repflag='' where repflag='D'
update a
set Repflag=''
from volume a
where exists (
select *
from volume b
where --Repflag='X' and
b.id=a.id
and b.perEndDate=dateadd(day,-1,dateadd(month,-1,dateadd(day,1,a.perenddate)))
and a.AchieveLev=b.achieveLev)
select *,dateadd(day,-1,dateadd(month,1,dateadd(day,1,a.perenddate)))
from volume a
where repflag='X'
order by perenddate
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 10, 2007 at 12:59 pm
I think that will work for the initial view which I just need to indicate that a person has had a least one re-promote in their history. The trick will be when I need to list all occurances of the re-promote. Any ideas?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 10, 2007 at 1:12 pm
I'm pretty sure that code catches multiple occurrences, but just so I'm sure - could you mock up some data as you'd expect it to look after such an update? It should help weed out the possibilities.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 10, 2007 at 1:20 pm
It would be along these lines:
ConID PeriodEndDate Ach_lev Achieve Title repflag
0014083 2007-11-30 00:00:00.00005 Team Manager
0014083 2007-10-31 00:00:00.00004 Team Leader
0014083 2007-09-30 00:00:00.00006 Sr Team Manager
0014083 2007-08-31 00:00:00.00006 Sr Team Manager
0014083 2007-07-31 00:00:00.00006 Sr Team Manager
0014083 2007-06-30 00:00:00.00006 Sr Team Manager
0014083 2007-05-31 00:00:00.00006 Sr Team Manager
0014083 2007-04-30 00:00:00.00006 Sr Team ManagerX
0014083 2007-03-31 00:00:00.00005 Team Manager
0014083 2007-02-28 00:00:00.00005 Team Manager
0014083 2007-01-31 00:00:00.00005 Team Manager
0014083 2006-12-31 00:00:00.00005 Team Manager
0014083 2006-11-30 00:00:00.00005 Team Manager
0014083 2006-10-31 00:00:00.00005 Team Manager
0014083 2006-09-30 00:00:00.00005 Team Manager
0014083 2006-08-31 00:00:00.00005 Team Manager
0014083 2006-07-31 00:00:00.00005 Team Manager
0014083 2006-06-30 00:00:00.00005 Team Manager
0014083 2006-05-31 00:00:00.00005 Team Manager
0014083 2006-04-30 00:00:00.00005 Team Manager
0014083 2006-03-31 00:00:00.00006 Sr Team Manager
0014083 2006-02-28 00:00:00.00006 Sr Team Manager
0014083 2006-01-31 00:00:00.00006 Sr Team Manager
0014083 2005-12-31 00:00:00.00006 Sr Team Manager
0014083 2005-11-30 00:00:00.00006 Sr Team Manager
0014083 2005-10-31 00:00:00.00006 Sr Team Manager
0014083 2005-09-30 00:00:00.00006 Sr Team Manager
0014083 2005-08-31 00:00:00.00006 Sr Team Manager
0014083 2005-07-31 00:00:00.00006 Sr Team ManagerX
0014083 2005-06-30 00:00:00.00005 Team Manager
0014083 2005-05-31 00:00:00.00005 Team Manager
0014083 2005-04-30 00:00:00.00005 Team Manager
0014083 2005-03-31 00:00:00.00005 Team Manager
0014083 2005-02-28 00:00:00.00005 Team Manager
0014083 2005-01-31 00:00:00.00006 Sr Team Manager
0014083 2004-12-31 00:00:00.00006 Sr Team Manager
0014083 2004-11-30 00:00:00.00006 Sr Team Manager
0014083 2004-10-31 00:00:00.00005 Team Manager
0014083 2004-09-30 00:00:00.00005 Team Manager
0014083 2004-08-31 00:00:00.00005 Team Manager
0014083 2004-07-31 00:00:00.00005 Team Manager
0014083 2004-06-30 00:00:00.00004 Team Leader
0014083 2004-05-31 00:00:00.00004 Team Leader
0014083 2004-04-30 00:00:00.00004 Team Leader
0014083 2004-03-31 00:00:00.00004 Team Leader
0014083 2004-02-29 00:00:00.00002 Consultant
0014083 2004-01-31 00:00:00.00002 Consultant
0014083 2003-12-31 00:00:00.00002 Consultant
0014083 2003-11-30 00:00:00.00002 Consultant
0014083 2003-10-31 00:00:00.00002 Consultant
0014083 2003-09-30 00:00:00.00002 Consultant
0014083 2003-08-31 00:00:00.00002 Consultant
0014083 2003-07-31 00:00:00.00002 Consultant
0014083 2003-06-30 00:00:00.00002 Consultant
0014083 2003-05-31 00:00:00.00002 Consultant
0014083 2003-04-30 00:00:00.00002 Consultant
0014083 2003-03-31 00:00:00.00002 Consultant
0014083 2003-02-28 00:00:00.00002 Consultant
0014083 2003-01-31 00:00:00.00002 Consultant
So in this case there would have been two repromotes.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
December 11, 2007 at 12:18 pm
If I understand correctly - you only want repromotes when they return to their previous highest level.
This ought to do it:
--run this once
alter table volume
ADD PrevMaxLev int
create index ix_volume on volume(ID,PERENDDATE) INCLUDE (ACHIEVELEV)
go
DECLARE @DUMMY INT
Declare @dummy2 varchar(20)
DECLARE @PrevMaxLev int
DECLARE @PrevLev int
declare @prevID varchar(20)
select @prevLev=0,@prevID='',@PrevMaxLev=0
--Populate the new PrevMaxLev to track the highest point of promotion until now
update volume
set@prevmaxlev=PrevMaxlev=case when @prevID<>ID then achievelev
when @PrevMaxLev>achieveLev then @PrevMaxLev else achieveLev end,
@dummy=@PrevMaxLev,
@dummy2=@previd,
@PrevID=ID
from volume with (index(ix_volume),tablock)
--use this with an inner join to figure out if you've repromoted to your previous level
update a
set Repflag=case when b.achievelev<a.AchieveLev and a.AchieveLev=b.PrevMaxLev then 'X' else '' end
from volume a
inner join volume b
on a.id=b.id and b.perEndDate=dateadd(day,-1,dateadd(month,-1,dateadd(day,1,a.perenddate)))
select * from volume
order by id,perenddate
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply