Finding value in previous row in a partition

Database

Finding value in previous row in a partition

/*I have two views in a company’s HR database I am pulling from. 

One gives a list of hiring actions. The first time an employee is hired, they’re given a sequence of 1. Each time after they’re given a sequence of 2, 3, 4 and so on.

The second view I’m querying has similar information for employees who were terminated.

The data is, as always, quite messy. I’ve designed some logic I’d like to use when merging the two data sets in a LEFT OUTER JOIN statement, but there’s a specific part of the statement that I’m not sure how to do it in Teradata.

Here’s a sample of the ‘Hires’ database:

EmployeeID Sequence Department WorkSite HireCode HireCodeDesc HireDate
073624 1 Marketing NationalHQ 190 NewHire 12/22/2014
121908 1 HR National HQ 190 NewHire 9/29/2014
131737 3 Sales Albequerque 190 NewHire 11/23/2015
131737 2 Sales Albequerque 110 EmployeeRec 3/2/2015
131737 1 Sales Albequerque 110 EmployeeRec 12/1/2014
180317 2 CustService CallCenter 190 NewHire 2/18/2015
180317 1 CustService CallCenter 110 EmployeeRec 10/6/2014

Here’s a sample of the ‘Separations’ database:

PersonnelNumber TerminationCode TerminationCodeDesc TerminationDate
121908 317 Resignation 10/30/2015
131737 317 Resignation 1/12/2016
131737 317 Resignation 10/27/2015
131737 352 Termination 1/9/2015
131737 328 Temporary Hire 9/17/2014
180317 317 Resignation 7/2/2015
180317 317 Resignation 2/11/2015

Here’s what I want to happen:

EmployeeID Sequence Department WorkSite HireCode HireCodeDesc HireDate TerminationCode TerminationCodeDesc TerminationDate
073624 1 Marketing NationalHQ 190 NewHire 12/22/2014   
121908 1 HR National HQ 190 NewHire 9/29/2014 317 Resignation 10/30/2015
131737 3 Sales Albequerque 190 NewHire 11/23/2015 317 Resignation 1/12/2016
131737 2 Sales Albequerque 110 EmployeeRec 3/2/2015 317 Resignation 10/27/2015
131737 1 Sales Albequerque 110 EmployeeRec 12/1/2014 352 Termination 1/9/2015
180317 2 CustService CallCenter 190 NewHire 2/18/2015 317 Resignation 7/2/2015
180317 1 CustService CallCenter 110 EmployeeRec 10/6/2014 317 Resignation 2/11/2015

Here’s the code I have written to make this happen:*/

Select
Hires.EmployeeID
,Hires.Sequence
,Hires.Department
,Hires.WorkSite
,Hires.HireCode
,Hires.HireCodeDesc
,Hires.HireDate
,Seps.TerminationCode
,Seps.TerminationCodeDesc
,Seps.TerminationDate
From Hires
LEFT OUTER JOIN Seps ON (Hires.EmployeeID=Seps.EmployeeID)
WHERE((Hires.Sequence='1' AND (Seps.TerminationDate>Hires.HireDate OR Seps.TerminationDate IS NULL)) OR
(Hires.Sequence >'1' AND Seps.TerminationDate>Hires.HireDate AND Seps.TerminationDate<(Hires.HireDate WHERE Hires.Sequence=(Hires.Sequence+'1'))))

My question revolves around the part of the where statement wherein I state that the Separation Date must be greater than the hire date, but less than the hire date of the next sequence. Essentially making sure it doesn't look like someone was terminated after they were rehired.

I'd like to know how to tell teradata to reference the HireDate from the line where the employeeID matches and the sequence is the sequence of the current line +1 so it can make that evaluation.

Not all employees have separated- hence the left outer join making any line where the WHERE conditions don't apply hold null values.

I know many manipulation languages allow you to use a "previous" or "next" or "window" function to do this. Just not sure how to do this in teradata.

3 REPLIES
Senior Apprentice

Re: Finding value in previous row in a partition

What's your Teradata release?

What's the number of rows on both tables and how often do you need to run this?

What do you mean by "messy data", missing rows or additional rows? How to resolve that problem? Can you show some examples?

Re: Finding value in previous row in a partition

Thanks for your questions!

Teradata SQL Assistant 14.10

The number of rows varies based on my where clause... which is based on the group of employees I am analyzing.

The data is messy in the sense that it's difficult to merge together in a clean format. Hence my question on here. Unfortunately, I cannot got into too much detail because of the confidential nature of the data.

My question here is pretty specific and revolves around referencing another row in a where clause: 

WHERE((Hires.Sequence='1' AND (Seps.TerminationDate>Hires.HireDate OR Seps.TerminationDate IS NULL)) OR
(Hires.Sequence >'1' AND Seps.TerminationDate>Hires.HireDate AND Seps.TerminationDate<(Hires.HireDate WHERE Hires.Sequence=(Hires.Sequence+'1'))))
Senior Apprentice

Re: Finding value in previous row in a partition

SQL Assistant is a client, not the DBMS, see "Help - About SQL Assistant"  for the TD version (probably 14.10, too).

You can't reference another row in WHERE (but you might use QUALIFY).

There are several way to get what you want, but they're more or less complex and efficiency depends on the number of rows and how to resolve bad data. e.g. what to return for dates like this:

hires date   seps date
2015-01-01 -- start without end
2015-05-01
2015-10-15
2016-01-01
2016-01-15 -- two ends for one start
2016-02-01
2016-03-01