SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to update the year an employee has been in a specific TITLE


How to update the year an employee has been in a specific TITLE

Author
Message
anil.varghese
anil.varghese
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 26
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with
CREATE TABLE #mytable
(
EMP_ID INT,
Title varchar(50),
DateValue DATETIME,
TITLE_YEAR INT,
)

--=======INSERT DATA
INSERT INTO #mytable
(EMP_ID, TITLE, DATEVALUE)
SELECT'1','Managing Director','Jan 1 2009 12:00AM'UNION ALL
SELECT'2','Director','Jan 1 2009 12:00AM'UNION ALL
SELECT'3','Senior Consultant','Jan 1 2009 12:00AM'UNION ALL
SELECT'3','Director','Jan 1 2010 12:00AM'UNION ALL
SELECT'2','Director','Jan 1 2010 12:00AM'UNION ALL
SELECT'1','SENIOR MANAGING DIRECTOR','Jan 1 2010 12:00AM'UNION ALL
SELECT'2','Director','Jan 1 2011 12:00AM'UNION ALL
SELECT'3','Director','Jan 1 2011 12:00AM'UNION ALL
SELECT'1','SENIOR MANAGING DIRECTOR','Jan 1 2011 12:00AM'UNION ALL
SELECT'3','Director','Jan 1 2012 12:00AM'UNION ALL
SELECT'2','Senior Director','Jan 1 2012 12:00AM'UNION ALL
SELECT'1','Senior Managing Director','Jan 1 2012 12:00AM'UNION ALL
SELECT'2','Senior Director','Jan 1 2013 12:00AM'UNION ALL
SELECT'1','Senior Managing Director','Jan 1 2013 12:00AM'UNION ALL
SELECT'3','Director','Jan 1 2013 12:00AM'UNION ALL
SELECT'2','Senior Director','Jan 1 2014 12:00AM'UNION ALL
SELECT'1','Senior Managing Director','Jan 1 2014 12:00AM'UNION ALL
SELECT'3','Senior Director','Jan 1 2014 12:00AM'

I am new to this level of coding in SQL SERVER 2012, but I am looking to update the TITLE_YEAR field in the temp table with the Year the employee is in that title. For example for employee 11127 the data should look like this:

EMP_ID Title DateValue TITLE_YEAR
3 Senior Consultant 2009-01-01 00:00:00.000 1
3 Director 2010-01-01 00:00:00.000 1
3 Director 2011-01-01 00:00:00.000 2
3 Director 2012-01-01 00:00:00.000 3
3 Director 2013-01-01 00:00:00.000 4
3 Senior Director 2014-01-01 00:00:00.000 1




I feel like a recursive CTE might accomplish this, but I do not have much experience with recursive CTEs. Any help would be greatly appreciated.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31992 Visits: 18551
Out of curiosity, how are you determining how many years a person is in a position? In other words, what are their promotion dates? Or when are their title change dates?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16331 Visits: 19073
You might be able to use the Quirky update or the LEAD/LAG functions available on 2012.
However, I did this using an islands solution that might fit your problem.


WITH Groups AS(
SELECT EMP_ID,
Title,
DateValue,
TITLE_YEAR,
DATEADD(YYYY, - ROW_NUMBER() OVER (PARTITION BY EMP_ID, Title ORDER BY DateValue), DateValue) Grouper
FROM #mytable
), Added AS(
SELECT EMP_ID,
Title,
DateValue,
TITLE_YEAR,
ROW_NUMBER() OVER (PARTITION BY EMP_ID, Title, Grouper ORDER BY DateValue) Years
FROM Groups
)
UPDATE Added SET
TITLE_YEAR = Years

SELECT * FROM #mytable




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
anil.varghese
anil.varghese
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 26
Thanks, this worked great. As to a response to a previous question, A promotion is identified by a change in title and the year of that change.
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16331 Visits: 19073
anil.varghese (4/2/2014)
Thanks, this worked great. As to a response to a previous question, A promotion is identified by a change in title and the year of that change.

That's great! I just hope that you understand how does it work. If you have any questions, feel free to ask them. You need to understand the code to be able to replicate it , support it and explain it if necessary.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16331 Visits: 19073
anil.varghese (4/2/2014) (From PM)

Luis

Thanks for your assistance. I believe I do understand what you are doing. This is new to me, so if there is some documentation on what you did, I would love to read up on it. It's a recursive query I believe where you created this grouper column to Identify when a title change occurred. The update of a SET is new to me though.

ACV

It's not a recursive query, that's a different concept.
To read about this, you could look for nested or cascade CTE. Books On Line (the help that comes with SQL Server) has explanations and examples http://msdn.microsoft.com/en-us/library/ms175972.aspx
Updating a CTE is basically as updating a view. You can find documentation here: http://technet.microsoft.com/en-us/library/ms180800.aspx
The logic behind the grouper column is explained in this article: http://www.sqlservercentral.com/articles/T-SQL/71550/. The only difference is the time period (the article uses days and your query uses years).


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31992 Visits: 18551
Since my question went unanswered, it would seem that it is not an issue for somebody to be in a new title for 5 months but potentially be reported as having been in that position for a year. This would seem inaccurate. Is it not important to know when the title change actually occurred?



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search