Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Increment one year for each row in given group.

Posted on 2010-11-11
5
Medium Priority
?
527 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 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