2-nd edition of Celko Puzzle book in the works

2-nd edition of Celko Puzzle book in the works
I am getting material for a second edition of my SQL PUZZLES book
together of the next few months.
1) If anyone has a new puzzle, send it to me. You will get your name
in print, fame, glory and perhaps a free copy, depending on my
publisher.
2) If anyone has a new answer to an old puzzle, send it to me. In
particular, when I wrote the first edition, most products were still
using the SQL-86 Standards. That meant no OUTER JOIN, no CTE, no
derived tables, and limited standardized functions and so forth.
I'd like to see new answers with SQL-92 stuff and SQL-99 OLAP
extensions.
3) I will post some of the oldies on newsgroups and fish for new
answers. I will reward the best ones with an old IT book from my
library. I will try to keep my choice in the database area, but you
could get a copy of something weird, like an illustrated history of
punch cards.
4) Here is a starter, an oldie that appeared in one of my magazine
columns over a decade ago, before we had standard OUTER JOIN syntax.
My original answer was a complex nested nightmare; can you do better?
You are given an abbreviated table of personnel salary history
CREATE TABLE Salaries
(emp_name CHAR(10) NOT NULL,
sal_date DATETIME NOT NULL,
sal_amt DECIMAL (8,2) NOT NULL,
PRIMARY KEY (emp_name, 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);
The goal is to produce a query that will show us the current salary and
the effective date of each employee, and his immediately previous
salary and effective date. If he is a new employee, then his previous
salary and effective date are shown as NULL. The results of the sample
data are:
Result
emp_name curr_date curr_amt prev_date prev_amt
========================================
==========
'Tom' '1996-12-20' 900.00 '1996-10-20' 800.00
'Harry' '1996-09-20' 700.00 '1996-07-20' 500.00
'Dick' '1996-06-20' 500.00 NULL NULLA new answer to an oldie?
WITH SalariesRank(emp_name,sal_date,sal_amt,P
os) AS
(SELECT emp_name,
sal_date,
sal_amt,
RANK() OVER(PARTITION BY emp_name ORDER BY sal_date DESC)
FROM Salaries)
SELECT c.emp_name,
c.sal_date AS curr_date,
c.sal_amt AS curr_amt,
p.sal_date AS prev_date,
p.sal_amt AS prev_amt
FROM SalariesRank c
LEFT OUTER JOIN SalariesRank p ON p.emp_name=c.emp_name AND p.Pos=2
WHERE c.Pos=1|||I like it!! here is the version I came up with, but have not tested:
SELECT S1.emp_name,
MAX (CASE WHEN rn = 1 THEN sal_date ELSE NULL END) AS curr_date,
MAX (CASE WHEN rn = 1 THEN sal_amt ELSE NULL END) AS curr_amt,
MAX (CASE WHEN rn = 2 THEN sal_date ELSE NULL END) AS prev_date,
MAX (CASE WHEN rn = 2 THEN sal_amt ELSE NULL END) AS prev_amt,
FROM (SELECT emp_name, sal_date, sal_amt,
RANK()OVER (PARTITION BY S1.emp_name ORDER BY sal_date
DESC)
FROM Salaries) AS S1 (emp_name, sal_date, sal_amt, rn)
WHERE rn < 3
GROUP BY S1.emp_name;
I avoid an OUTER JOIN, but at the expense of a GROUP BY.
Anybody else got one?|||Sorry, my format is no good, but this one is outer join wth correlated subs.
.
SELECT CS.Emp_name,
CS.Sal_date AS Curr_date,
CS.Sal_amt AS Curr_amt,
PS.Sal_date AS Prev_date,
PS.Sal_amt AS Prev_amt
FROM #Salaries CS LEFT OUTER JOIN
(SELECT Emp_name, Sal_date, Sal_amt FROM #Salaries t1
WHERE t1.Sal_Date = (SELECT MAX(Sal_date)
FROM #Salaries s2
WHERE s2.Emp_name = t1.Emp_name
AND s2.Sal_date < (SELECT MAX(Sal_date)
FROM #Salaries s3
WHERE s3.Emp_name = S2.Emp_name))) PS
ON CS.Emp_name = PS.Emp_name
WHERE CS.Sal_date = (SELECT MAX(Sal_date)
FROM #Salaries s1
WHERE s1.Emp_name = CS.Emp_name)|||> f anyone has a new answer to an old puzzle, send it to me
Joe,
what's the e-mail?|||jcelko212@.earthlink.net
I have the highest possible firewall, so I will need to let you in.|||How about a simple:
SELECT S1.emp_Name, S1.sal_date, S1.sal_amt, S2.Sal_date, S2.Sal_amt,
S3.sal_date
FROM Salaries S1
LEFT JOIN Salaries S2 ON S1.Emp_name = S2.Emp_name AND
S1.Sal_date > S2.Sal_date
LEFT JOIN Salaries S3 ON S1.Emp_name = S3.Emp_name AND
(S3.Sal_date > S1.Sal_date OR (S3.Sal_date > S2.Sal_date
AND S3.Sal_date < S1.Sal_date ))
WHERE S3.Emp_name IS NULL|||this is my attempt to solve the puzzle:
SELECT S1.emp AS emp_name, S1.sal_date AS curr_date, S1.sal_amt AS curr_amt,
CASE WHEN S2.sal_date <> S1.sal_date THEN S2.sal_date END AS prev_date,
CASE WHEN S2.sal_date <> S1.sal_date THEN S2.sal_amt END AS prev_amt
FROM Salaries AS S1
INNER JOIN Salaries AS S2
ON S2.emp = S1.emp
AND S2.sal_date = COALESCE((SELECT MAX(S4.sal_date)
FROM Salaries AS S4
WHERE S4.emp = S1.emp
AND S4.sal_date < S1.sal_date), S2.sal_date)
WHERE NOT EXISTS(SELECT *
FROM Salaries AS S3
WHERE S3.emp = S1.emp
AND S3.sal_date > S1.sal_date);
Andrey Odegov
avodeGOV@.yandex.ru
(remove GOV to respond)