Increment one year for each row in given group.

I have a query were I need to increment by one year for each row within an employee group. So for each row for a given employee I need to add one year and restart on the next employee group.

I have:
EmployeeNo, EligibilityDate
001, HireDate
001, HireDate
001, HireDate
002, HireDate
002, HireDate

What I am looking to accomplish is:
EmployeeNo, EligibilityDate
001, DateAdd(yy, +1, HireDate)
001, DateAdd(yy, +2, HireDate)
001, DateAdd(yy, +3, HireDate)
002, DateAdd(yy, +1, HireDate)
002, DateAdd(yy, +2, HireDate)

Any suggestions on how I may be able to complete this would be greatly appreciated!

SELECT	lawson_temp.employee_nbr,
		3 AS SurveyType_DataSort,
		'Annual Survey' AS SurveyType,
		(CASE WHEN AnnualSurvey_DateCompleted.DateCompleted IS NOT NULL THEN LEFT(CONVERT(VARCHAR(10), adj_hire_date, 101), 5) + '/' + CONVERT(VARCHAR(4), YEAR(AnnualSurvey_DateCompleted.DateCompleted)) ELSE CONVERT(VARCHAR(10), DATEADD(yy, +1, adj_hire_date), 101) END) AS EligibilityDate,
		AnnualSurvey_EmailDate.EmailDate AS EmailDate,
		AnnualSurvey_DateCompleted.DateCompleted
FROM	lawson_temp
		LEFT OUTER JOIN (SELECT	employee_nbr, 
								CONVERT(VARCHAR(10), email_date, 101) AS EmailDate
						FROM	survey_email
						WHERE	survey_id = 20) AS AnnualSurvey_EmailDate ON lawson_temp.employee_nbr = AnnualSurvey_EmailDate.employee_nbr
		LEFT OUTER JOIN (SELECT	TOP (100) PERCENT user_survey_id,
								employee_nbr,
								DateCompleted
						FROM	vwSurveys_SPSS
						WHERE	survey_id = 20
						ORDER BY	DateCompleted) AS AnnualSurvey_DateCompleted ON lawson_temp.employee_nbr = AnnualSurvey_DateCompleted.employee_nbr

Open in new window

Levi MartinSenior Data AnalystAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jvejskrabConnect With a Mentor Commented:


You can use ranking function wirhin a subquerry.
Try something like this


CREATE TABLE #Employees (
      ID INT NOT NULL IDENTITY(1,1),
      EmployeeNo VARCHAR(3) NOT NULL,
      Name VARCHAR(100) NULL,
      EligibilityDate DATETIME NOT NULL DEFAULT(GETDATE())
)


INSERT INTO #Employees (EmployeeNo, Name) VALUES ('001', 'Pete')
INSERT INTO #Employees (EmployeeNo, Name) VALUES ('001', 'John')
INSERT INTO #Employees (EmployeeNo, Name) VALUES ('001', 'Jack')
INSERT INTO #Employees (EmployeeNo, Name) VALUES ('002', 'Ann')
INSERT INTO #Employees (EmployeeNo, Name) VALUES ('002', 'Michael')
INSERT INTO #Employees (EmployeeNo, Name) VALUES ('003', 'Jim')
INSERT INTO #Employees (EmployeeNo, Name) VALUES ('003', 'Jane')
INSERT INTO #Employees (EmployeeNo, Name) VALUES ('003', 'Angela')


SELECT *
      FROM #Employees

SELECT ID, EmployeeNo, Name, DATEADD(YY, EligibilityDateAdd, EligibilityDate) EligibilityDate
FROM (SELECT
            ID,
            EmployeeNo,
            Name,
            EligibilityDate,
            RANK() OVER(PARTITION BY EmployeeNo ORDER BY Name) EligibilityDateAdd
        FROM #Employees) Empl
      

DROP TABLE #Employees
0
 
chinawalCommented:
Are you okay with using multiple queries? Possibly, using temporary table?
0
 
Levi MartinSenior Data AnalystAuthor Commented:
I've used rank with temp tables though for this if I could stay away from temp tables I would prefer it...and multiple queries would be just fine. Thanks both for your post, I will continue to work on this!
0
 
Anthony PerkinsConnect With a Mentor Commented:
Something like this perhaps:

zSELECT  l.employee_nbr,
	DATEADD(year, ROW_NUMBER() OVER (PARTITION BY employee_nbr ORDER BY employee_nbr), adj_hire_date)
FROM    lawson_temp l
    LEFT OUTER JOIN (SELECT employee_nbr
		     FROM   survey_email
		     WHERE  survey_id = 20
		    ) e ON l.employee_nbr = e.employee_nbr
    LEFT OUTER JOIN (SELECT employee_nbr
		     FROM   vwSurveys_SPSS
		     WHERE  survey_id = 20
		    ) d ON l.employee_nbr = d.employee_nbr

Open in new window

0
 
Anthony PerkinsCommented:
Typo: Please remove the z in zSELECT
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.