An Old Celko Puzzle

If you go over to http://www.dbdebunk.com/page/page/666711.htm, you will find a letter to the editor from someone named PV about an old column of mine. Here is the jist of it:

Back in June of 1996, Jack Wells submitted this SQL problem to my SQL FOR SMARTIES column.His situation is pretty typical for SQL programmers who work with 3GL people. The programmers are writing a report on the employees, and they want information about each employee's current and previous salary status. The report will show the date of their promotion and the salary amount.

Jack spoke with Fabian Pascal, the week he was working on this problem, and Mr. Pascal replied that this query could not be done. He said, 'In a truly relational language it could be done, but since SQL is not relational it isn't possible, not even with SQL-92.' Sounds like a challenge to me!

Oh, I forgot to mention an addition constraint; the answer had to be in 1996 Oracle, which had no proper outer joins, no general scalar subexpressions, and so on), so your query had to run under the old SQL-86 or SQL-89 rules back then. Assume that you have this test data:

CREATE TABLE Salaries (emp_id_id CHAR(10) NOT NULL, sal_date DATE NOT NULL, sal_amt DECIMAL (8, 2) NOT NULL, PRIMARY KEY (emp_id, sal_date));

INSERT INTO Salaries VALUES ('Tom', '1996-06-20', 500.00); INSERT INTO Salaries VALUES ('Tom', '1996-08-20', 700.00); INSERT INTO Salaries VALUES ('Tom', '1996-10-20', 800.00); INSERT INTO Salaries VALUES ('Tom', '1996-12-20', 900.00); INSERT INTO Salaries VALUES ('Dick', '1996-06-20', 500.00); INSERT INTO Salaries VALUES ('Harry', '1996-07-20', 500.00); INSERT INTO Salaries VALUES ('Harry', '1996-09-20', 700.00);

Tom has had two promotions, Dick is a new hire, and Harry has had one promotion. My old solution was:

SELECT S0.emp_id, S0.sal_date, S0.sal_amt, S1.sal_date, S1.sal_amt FROM Salaries AS S0, Salaries AS S1 WHERE S0.emp_id = S1.emp_id AND S0.sal_date = (SELECT MAX(S2.sal_date) FROM Salaries AS S2 WHERE S0.emp_id = S2.emp_id) AND S1.sal_date = (SELECT MAX(S3.sal_date) FROM Salaries AS S3 WHERE S0.emp_id = S3.emp_id AND S3.sal_date < S0.sal_date) UNION ALL SELECT S4.emp_id, MAX(S4.sal_date), MAX(S4.sal_amt), NULL, NULL FROM Salaries AS S4 GROUP BY S4.emp_id HAVING COUNT(*) = 1;

+