Compare 3 Consecutive rows in a table

Database
Enthusiast

Compare 3 Consecutive rows in a table

Hi I have an interesting problem. I Have an Employee Table AS Follows

CREATE TABLE EMPLOYEE(
EMPLOYEE_ID INTEGER,
SALARY DECIMAL(18,2),
PAY_PERIOD DATE)

Now the tables have employees some of whom get paid monthly,some weekly, some biweekly and some daily. What we want is to find an Indicator saying 'Y' if the salary of three consecutive Pay Periods is equal. Lets take the following example.

Employee   Pay_Period     Salary

1 01/01/2012 $500
1 08/01/2012 $200
1 15/01/2012 $200
1 22/01/2012 $200
1 29/01/2012 $700

In this case the indicator should be Yes because 3 consecutive pay periods have a salary of $200.

Since the number of pay periods is not constant I am unsure of how to write this code because I do not know from before hand how many left joins I will need.Since I am writing this in Teradata I tried using the RECURSIVE Function but got stumped. Any general ideas on how to proceed with this?I would prefer not creating a stored procedure or having PL/SQL logic.

3 REPLIES
Senior Apprentice

Re: Compare 3 Consecutive rows in a table

This is a direct translation of your narrative, it will return 'Y' starting with the 3rd consecutive row:

CASE 
WHEN salary = MIN(salary) OVER (PARTITION BY employee_id ORDER BY pay_period ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
AND salary = MIN(salary) OVER (PARTITION BY employee_id ORDER BY pay_period ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING)
THEN 'Y'
ELSE 'N'
END

Dieter

Enthusiast

Re: Compare 3 Consecutive rows in a table

Dieter,

Thanks a lot. This was exactly what I was looking for!

Re: Compare 3 Consecutive rows in a table

I have a table like this.

SELECT * FROM NDW_TEMP.TEST2015 order by C3

C1 C2                    C3                                    C4

1 O 12/5/2015 10:56:30.620000 12/6/2015 10:56:30.620000

1 O 12/6/2015 10:56:47.780000 12/7/2015 10:56:47.780000

1 O 12/7/2015 10:57:15.810000 12/9/2015 10:57:15.810000

 1 H 12/9/2015 10:57:32.800000 12/10/2015 10:57:32.800000

1 H 12/10/2015 10:57:39.780000 12/11/2015 10:57:39.780000

1 H 12/11/2015 10:57:50.510000 12/12/2015 10:57:50.510000

1 O 12/12/2015 10:58:07.650000 12/13/2015 10:58:07.650000

I want the output like this:

C1 C2                    C3                                             C4

1 O     "MIN(C3) FROM FIRST 3 COLUMNS"     "MAX(C4) FROM FIRST 3 COLUMNS"

1 H     "MIN(C3) FROM middle 3 COLUMNS"    "MAX(C4) FROM middle 3 COLUMNS"

1 O     "MIN(C3) FROM last column"                "MAX(C4) FROM last column"


I have tried this:

 SELECT C1,C2,MIN(C3),MAX(C4) FROM

 (

SELECT C1,C2 , 

MIN(C3) OVER(PARTITION BY C2   ORDER BY C2  ) C3, 

MAX(C4) OVER(PARTITION BY C2   ORDER BY C2  )  C4

 FROM  NDW_TEMP.TEST2015

 ) A GROUP BY C1,C2;

But the 1st 3 'O' and the last 'O' in C2 column is coming under same partition, which i want to be separate.

Please help.

P.S. I was not able to find where to submit my question thats why pasted it here in the comments.