How to return values by comparing consecutive fields in a table?

[dvenkat]

I have the following fields in a table-
Name, Age, Sex, Designation, Perf1, Perf2, Perf3, Perf4, Perf5, Perf6....Perf12.

I need to return the name, age, sex and designation if
Perf2-Perf1 > 4 or
Perf3-Perf2 > 4 or
Perf4-Perf3 > 4 or
.....
Perf12-Perf11 > 4.

I need to return the name, age, sex and designation if
Perf4-Perf1 > 4 or
Perf5-Perf2 > 4 or
Perf6-Perf3 > 4 or
.............
Perf12-Perf9 > 4.

How do I accomplish this......Any help will be appreciated.

Thanks

 

[AWithers]

Should be exactly as as you say with lots of 'or's

e.g.

select name, age, sex,designation
from yourtable
where
(Perf2-Perf1) > 4 or
(Perf3-Perf2) > 4 or
(Perf4-Perf3) > 4 or
etc etc.
(Perf4-Perf1) > 4 or
(Perf5-Perf2) > 4 or
(Perf6-Perf3) > 4 or
etc etc.

Andy

 

[IFELSETHEN]

Hi, is this what you are wanting? If not let me know.

Regards,

SQL Statement 1
************************************
select *
FROM tblTest
WHERE ((Perf2 - Perf1 > 4)
OR (Perf3 - Perf2 > 4)
OR (Perf4 - Perf3 > 4)
OR (Perf5 - Perf4 > 4)
OR (Perf6 - Perf5 > 4)
OR (Perf7 - Perf6 > 4)
OR (Perf8 - Perf7 > 4)
OR (Perf9 - Perf8 > 4)
OR (Perf10 - Perf9 > 4)
OR (Perf11 - Perf10 > 4)
OR (Perf12 - Perf11 > 4))
*************************************
SQL Statement #2
****************************
select *
FROM tblTest
WHERE
((Perf4 - Perf1 > 4)
OR (Perf5 - Perf2 > 4)
OR (Perf6 - Perf3 > 4)
OR (Perf7 - Perf4 > 4)
OR (Perf8 - Perf5 > 4)
OR (Perf9 - Perf6 > 4)
OR (Perf10 - Perf7 > 4)
OR (Perf11 - Perf8 > 4)
OR (Perf12 - Perf9 > 4))
*****************************

 

[dvenkat]

Thanks. This is exactly what I needed.

Thx for the help guys. I will try it out and let you know if the query worked out.

Thx again