Visualize SQL | Find The Last Two Salaries(part 4)

# Beginning
This technique is based on SQL Puzzle and Answers # 15 Find The Last Two Salaries
It is better with 『SQL Puzzle and Answers』 to read this article.

It explains the idea of algorithm and
You can check what each quiz is intended for ・・・

Let's start !!

『Part 4』 is  similar to 『Part 2』・・・Different people approach differently
I think it will be good learn to see SQL that has different approach

`This quiz is to get current and previous salary information on one line`
The format to display is below

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d2f94308-1a0a-819e-b577-bc0e08b43349.png)

**I have confirmed that it works with PostgreSQL**

# Salaries table
```SQL:SQL
CREATE TABLE Salaries (
	emp_name CHAR(10) NOT NULL,
	sal_date DATE 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),
       ('Tom',   '1996-08-20', 700.00),
       ('Tom',   '1996-10-20', 800.00),
       ('Tom',   '1996-12-20', 900.00),
       ('Dick',  '1996-06-20', 500.00),
       ('Harry', '1996-07-20', 500.00),
       ('Harry', '1996-09-20', 700.00);
```

▼ Output

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/69f3a5a2-9457-a3a9-fccd-aefff503c6ee.png)

# Creating original data 1st
```SQL:SQL
SELECT S0.*, S1.*
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name
```

▼ Output (right side of image)

There are three employees data and seven records in the Salaries table
`Creating all combinations of data using CROSS JOIN`
and taking out the combination of the same employees

Tom has four data : 4 x 4 = 16
Marry has two data : 2 x 2 = 4
Dick has one data : 1 x 1 = 1

Below is the image of the original data
**Taking out current salary information from S0 side**
**and  previous salary information from S1 side**

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/43749895-7f03-07e7-22ad-28a6ebaf77fc.png)

until here, it's the same as 『Part 2』


# Edit original data 1st
```SQL:SQL
SELECT S0.*, S1.*
FROM Salaries AS S0, Salaries AS S1
WHERE S0.emp_name = S1.emp_name AND S0.sal_date <= S1.sal_date
```

▼ Output(right side of image)

Add 『S0.sal_date <= S1.sal_date』in WHERE clause
What this condition is doing is ・・・

S0.sal_date is current promotion, S1.sal_date is previous promotion
Previous promotion is supposed to be later date than current promotion
**combinations that S0.sal_date is later date than S1.sal_date are not needed**
　⇒　left side of image is original data
　⇒　pick rows that S1.sal_date is later than or equal to S0.sal_date



![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/bfae076d-3250-f520-3be9-8c976e94ee6e.png)




# Edit original data 2nd
```SQL:SQL
SELECT S0.emp_name AS emp_name, 
       S0.sal_date AS sal_date,
       MAX(S0.sal_amt) AS sal_amt,
       COUNT(*)
FROM Salaries AS S0, Salaries AS S1
WHERE S0.sal_date <= S1.sal_date AND S0.emp_name = S1.emp_name
GROUP BY S0.emp_name, S0.sal_date
```

▼ Output(left side of image)

Add GROUP BY S0.emp_name, S0.sal_date ・・・then
Count how many combinations start at `S0.sal_date`

![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a3012309-2d83-d8a8-5d73-64357e18cfdf.png)


# Edit original data 3rd
```SQL:SQL
SELECT S0.emp_name AS emp_name, 
       S0.sal_date AS sal_date,
       MAX(S0.sal_amt) AS sal_amt,
       COUNT(*)
FROM Salaries AS S0, Salaries AS S1
WHERE S0.sal_date <= S1.sal_date AND S0.emp_name = S1.emp_name
GROUP BY S0.emp_name, S0.sal_date
HAVING COUNT(*) <= 2  -- add here
```

▼ Output

|emp_name|sal_date|sal_amt|count|
|---|---|---|---|
|Tom|1996-10-20|800|2|
|Tom|1996-12-20|900|1|
|Harry|1996-07-20|500|2|
|Harry|1996-09-20|700|1|
|Dick|1996-06-20|500|1|



![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/87b2e15c-8b6a-57fa-a325-0ff598ed8414.png)


Why taking out rows which COUNT(*) value is less than or equal to two?

Tom's case ・・・
Count(＊) value is more than 3 means he has been promoted more than two times

**If we see S0.sal_date as the current promotion**
**S1.sal_date should has less than or equal to two sal_date combination**
　⇒　COUNT(＊) = 1 is current promotion
　⇒　COUNT(＊) = 2 is previous promotion
　⇒　check above image

We are almost done ・・・but a few more steps left
We have already got current and previous salary information
But they are lined vertically. We need to change to horizontally

`To change data format, use CROSS JOIN to make a combination again`


# Creating original data 2nd
```SQL:SQL
WITH Salaries2 AS (
  SELECT S0.emp_name AS emp_name, 
         S0.sal_date AS sal_date,
         MAX(S0.sal_amt) AS sal_amt
  FROM Salaries AS S0, Salaries AS S1
  WHERE S0.sal_date <= S1.sal_date AND S0.emp_name = S1.emp_name
  GROUP BY S0.emp_name, S0.sal_date
  HAVING COUNT(*) <= 2
)

SELECT S1.emp_name, S1.sal_date, S1.sal_amt,
       S2.sal_date, S2.sal_amt
FROM Salaries2 AS S1, salaries2 AS S2
WHERE S1.emp_name = S2.emp_name
```

▼ Output


![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/90eed3ad-748a-d0d5-d9ad-a8633f4aeb31.png)



Define above SQL using CTE as Salaries2
then CROSS JOIN Salaries2 to make original data

This original data has all combination of S1 and S2
We are going to get **current promotion from S1 side**
and **previous promotion from S2 side**

# Taking out salary information
```SQL:SQL
WITH Salaries2 AS (
  SELECT S0.emp_name AS emp_name, 
         S0.sal_date AS sal_date,
         MAX(S0.sal_amt) AS sal_amt
  FROM Salaries AS S0, Salaries AS S1
  WHERE S0.sal_date <= S1.sal_date AND S0.emp_name = S1.emp_name
  GROUP BY S0.emp_name, S0.sal_date
  HAVING COUNT(*) <= 2
)

SELECT S1.emp_name, S1.sal_date, S1.sal_amt,
       S2.sal_date, S2.sal_amt
FROM Salaries2 AS S1, salaries2 AS S2
WHERE S1.emp_name = S2.emp_name AND S1.sal_date > S2.sal_date
```

▼ Output

|S1.emp_name|S1.sal_date|S1.sal_amt|S2.sal_date|S2.sal_amt|
|---|---|---|---|---|
|Tom|1996-12-20|900|1996-10-20|800|
|Harry|1996-09-20|700|1996-07-20|500|


Add condition 『S1.sal_date > S2.sal_date 』to WHERE clause
S1.sal_date is current promotion, so previous promotion(S1.sal_date) has to be later date

**This comparison operator does not have 『=』**
**S0.sal_date and S1.sal_date can not be the same**

Dick's salary information is gone ・・・

There are two conditions in WHERE clause using AND operator
Both conditions must be fit but `Dick's data does not fit condition below`
　⇒　S1.sal_date > S2.sal_date
　⇒　Dick has only one data
　⇒　No date later than 1996-06-20(Dick's S1.sal_date)

**Dick's salary information must be taken in separately**


![rep_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/bb882c5d-340a-99f7-f840-3aa8afcc45c3.png)

# Answer SQL
```SQL:SQL
WITH Salaries2 AS (
  SELECT S0.emp_name AS emp_name, 
         S0.sal_date AS sal_date,
         MAX(S0.sal_amt) AS sal_amt
  FROM Salaries AS S0, Salaries AS S1
  WHERE S0.sal_date <= S1.sal_date AND S0.emp_name = S1.emp_name
  GROUP BY S0.emp_name, S0.sal_date
  HAVING COUNT(*) <= 2
)

SELECT S1.emp_name, S1.sal_date, S1.sal_amt,
       S2.sal_date, S2.sal_amt
FROM Salaries2 AS S1, salaries2 AS S2
WHERE S1.emp_name = S2.emp_name AND S1.sal_date > S2.sal_date

UNION ALL  -- Dick's salary information

SELECT emp_name, MAX(sal_date), MAX(sal_amt), NULL, NULL
FROM Salaries2
GROUP BY emp_name
HAVING COUNT(*) = 1  -- Dick has only one data
```

▼ Output

|S1.emp_name|S1.sal_date|S1.sal_amt|S2.sal_date|S2.sal_amt|
|---|---|---|---|---|
|Tom|1996-12-20|900|1996-10-20|800|
|Harry|1996-09-20|700|1996-07-20|500|
|Dick|1996-06-20|500|empty|empty|


Dick has one row in CTE (Salaries2)
Output of count(＊) function is one 
Taking out Dick's salary information by `HAVING COUNT(*) = 1`

Aggregate variable is only 『emp_name』
so using MAX function to show 『sal_date』『sal_amt』
Otherwise we will get an error like below

**ERROR: column "salaries2.sal_amt" must appear in the GROUP BY clause**
**or be used in an aggregate function ・・・**


![rep_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/da33251d-65ca-5ae5-f605-482f98b30cb7.png)


# References 
[Joe Celko's SQL Puzzles and Answers](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)