Solved

Increment one year for each row in given group.

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now