Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [ The Fishbone Salary Puzzle ]  – In this puzzle we have to find which employee is getting salary hikes across different months in a fishbone structure. Please check out the sample input and expected output for details.

Sample Input

emp_name sal_date sal_amt
Dick 20-06-1996 500
Harry 20-07-1996 500
Harry 20-09-1996 700
Tom 20-06-1996 500
Tom 20-08-1996 700
Tom 20-10-1996 800
Tom 20-12-1996 900

Expected Output

emp_name 20-06-1996 20-07-1996 20-08-1996 20-09-1996 20-10-1996 20-12-1996
Dick 500 NULL NULL NULL NULL NULL
Harry NULL 500 NULL 700 NULL NULL
Tom 500 NULL 700 NULL 800 900

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.


--Create Table

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 Data
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);

--Verify Data
SELECT * FROM Salaries

Update May 14 | Solution


--

/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/

SELECT Emp_Name,[1996-06-20],[1996-07-20],[1996-08-20],[1996-09-20],[1996-10-20],[1996-12-20] 
FROM Salaries
PIVOT 
(
	MAX(sal_amt) FOR sal_date IN ([1996-06-20],[1996-07-20],[1996-08-20],[1996-09-20],[1996-10-20],[1996-12-20])
)p



--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com

Advertisements