[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Increment one year for each row in given group.

Posted on 2010-11-11
5
Medium Priority
?
533 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:Levi Martin
5 Comments
 
LVL 5

Expert Comment

by:chinawal
ID: 34117332
Are you okay with using multiple queries? Possibly, using temporary table?
0
 
LVL 3

Accepted Solution

by:
jvejskrab earned 1000 total points
ID: 34120150


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
 

Author Comment

by:Levi Martin
ID: 34122027
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 1000 total points
ID: 34123145
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34123160
Typo: Please remove the z in zSELECT
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question