Link to home
Start Free TrialLog in
Avatar of Levi Martin
Levi MartinFlag for United States of America

asked on

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

Avatar of chinawal
chinawal
Flag of United States of America image

Are you okay with using multiple queries? Possibly, using temporary table?
ASKER CERTIFIED SOLUTION
Avatar of jvejskrab
jvejskrab
Flag of Czechia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Levi Martin

ASKER

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!
SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Typo: Please remove the z in zSELECT