Solved

Increment one year for each row in given group.

Posted on 2010-11-11
5
523 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 250 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 250 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

749 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