Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to update the year an employee has been in a specific TITLE Expand / Collapse
Author
Message
Posted Tuesday, April 1, 2014 3:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 2:18 PM
Points: 7, Visits: 22
--===== 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.

Post #1557226
Posted Tuesday, April 1, 2014 3:22 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 17,947, Visits: 15,941
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
Post #1557235
Posted Tuesday, April 1, 2014 3:41 PM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 3,908, Visits: 8,860
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1557247
Posted Wednesday, April 2, 2014 8:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 2:18 PM
Points: 7, Visits: 22
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.
Post #1557531
Posted Wednesday, April 2, 2014 9:24 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 3,908, Visits: 8,860
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1557561
Posted Wednesday, April 2, 2014 10:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:57 PM
Points: 3,908, Visits: 8,860
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1557582
Posted Wednesday, April 2, 2014 1:17 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:45 PM
Points: 17,947, Visits: 15,941
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
Post #1557664
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse