By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,924 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,924 IT Pros & Developers. It's quick & easy.

2-nd edition of Celko Puzzle book in the works

P: n/a
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. DB2 programmers have had more time to play with than other
SQL products, so you shoudl have an advantage.

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 NULL

Mar 31 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
--CELKO-- wrote:
So - ummm, basically, this becomes - help me write my book, and clear my
garage for me ?

Anyone want to do my next 4 customer visits for me ? I'll let you clean
my car if you do.
Mar 31 '06 #2

P: n/a
Mark Townsend wrote:
--CELKO-- wrote:
So - ummm, basically, this becomes - help me write my book, and clear my
garage for me ?

Anyone want to do my next 4 customer visits for me ? I'll let you clean
my car if you do.

Mark,

I would be delighted to do your next 4 customer visits.
Just give me the names and your corporate credit-card.
Your car will be on me of course.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 31 '06 #3

P: n/a
Ian
--CELKO-- wrote:
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 NULL


I don't care if Joe is asking us to do his work for him. It's
the worth 2 minutes of my time for all of the pleasure I've derived
reading Joe verbally bludgeon people for asking "where are BITAND()
and BITOR() functions" or other atrocities.

So,

WITH sals (
emp_name,
sal_date,
sal_amt,
relative
) as (
SELECT emp_name,
sal_date,
sal_amt,
rownumber() 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 sals c
LEFT OUTER JOIN sals p
ON (c.emp_name = p.emp_name and p.relative = 2)
WHERE c.relative = 1;
EMP_NAME CURR_DATE CURR_AMT PREV_DATE PREV_AMT
---------- ---------- ---------- ---------- ----------
Harry 09/20/1996 700.00 07/20/1996 500.00
Tom 12/20/1996 900.00 10/20/1996 800.00
Dick 06/20/1996 500.00 - -

3 record(s) selected.

Mar 31 '06 #4

P: n/a
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
av******@yandex.ru
(remove GOV to respond)

Mar 31 '06 #5

P: n/a
select emp_name, sal_date, sal_amt, prev_sal_date, prev_sal_amt
from ( select emp_name, sal_date, sal_amt,
max(sal_date) over (partition by emp_name order by
sal_date desc rows between 1 following and 1 following) as
prev_sal_date,
max(sal_amt) over (partition by emp_name order by
sal_date desc rows between 1 following and 1 following) as
prev_sal_amt,
rownumber() over (partition by emp_name order by sal_date
desc) as rn
from salaries ) tmp
where rn = 1;

Regards,
Miro

Mar 31 '06 #6

P: n/a
Noeth came up with the same answer on Teradata.

Mar 31 '06 #7

P: n/a
BTW, here's literally someone solving a puzzle with SQL, although I
doubt that you'd approve of the programming style ;-)

http://www.vsj.co.uk/articles/display.asp?id=540

Regards,
Miro

Mar 31 '06 #8

P: n/a
Here's my solution

with salaries_ordered (emp_name, sal_date, sal_amt, row_num) as
(select emp_name, sal_date, sal_amt,
row_number() over (partition by emp_name order by sal_date desc)
from salaries)
select curr.emp_name, curr.sal_date as curr_date, curr.sal_amt as
curr_amt,
prev.sal_date as prev_date, prev.sal_amt as prev_amt
from salaries_ordered curr
left outer join salaries_ordered prev on curr.emp_name = prev.emp_name
and prev.row_num = 2
where curr.row_num = 1

-Chris

Mar 31 '06 #9

P: n/a
Mark Townsend wrote:
--CELKO-- wrote:
So - ummm, basically, this becomes - help me write my book, and clear my
garage for me ?

Anyone want to do my next 4 customer visits for me ? I'll let you clean
my car if you do.


You've got it Mark. Send me the plane tickets. ;-)
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)
Mar 31 '06 #10

P: n/a
I followed some other Sodoku papers in the math forums and I have seen
the hand-held Sodoku toys. I played with the idea of doing a less
procedural SQL version of a solution.

My idea was an (i,j,k) matrix where (i,j) are the Sodoku grid and k is
the depth in cell. A known cell has a constant for (i, j, k, val) ,
and empty cells have (k = 1 to 9) then we start deletes to remove
illegal values from the (i,j,k) rows.

If I am lucky, I will be left with 81 rows in the table which will be a
unique solution. If not, I would have a trimmed down grid that I could
permute.

I never did anything with it.

Apr 3 '06 #11

P: n/a
--CELKO-- wrote:
[snip]
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 NULL


My own take on this is:
select a.emp_name, a.sal_date, a.sal_amt, b.sal_date,b.sal_amt
from
Salaries a
inner join
(select emp_name,MAX(sal_date) as sal_date from Salaries group by
emp_name) c //Find latest salary
on
a.emp_name = c.emp_name and
a.sal_date = c.sal_date
left join
Salaries b //Find earlier salary
on
a.emp_name = b.emp_name and
a.sal_date > b.sal_date
left join
Salaries d //Find latest earlier salary
on
a.emp_name = d.emp_name and
d.sal_date > b.sal_date and
d.sal_date < a.sal_date
where
d.emp_name is null

Damien

Apr 3 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.