April 1, 2014 at 3:08 pm
--===== 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_IDTitle DateValue TITLE_YEAR
3 Senior Consultant 2009-01-01 00:00:00.0001
3 Director 2010-01-01 00:00:00.0001
3 Director 2011-01-01 00:00:00.0002
3 Director 2012-01-01 00:00:00.0003
3 Director 2013-01-01 00:00:00.0004
3 Senior Director 2014-01-01 00:00:00.0001
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.
April 1, 2014 at 3:22 pm
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[/url]
Learn Extended Events
April 1, 2014 at 3:41 pm
You might be able to use the Quirky update[/url] 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
April 2, 2014 at 8:49 am
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.
April 2, 2014 at 9:24 am
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.
April 2, 2014 at 10:03 am
anil.varghese (4/2/2014) (From PM)
LuisThanks 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).
April 2, 2014 at 1:17 pm
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[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply