Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
–Verify Data
SELECT emp_name,
sum(case when sal_date=’19960620′ then sal_amt end) As ‘1996-06-20′,
sum(case when sal_date=’19960720’ then sal_amt end) As ‘1996-07-20′,
sum(case when sal_date=’19960820’ then sal_amt end) As ‘1996-08-20′,
sum(case when sal_date=’19960920’ then sal_amt end) As ‘1996-09-20′,
sum(case when sal_date=’19961020’ then sal_amt end) As ‘1996-10-20′,
sum(case when sal_date=’19961220’ then sal_amt end) As ‘1996-12-20’
FROM #Salaries
group by emp_name
LikeLiked by 2 people
Excellent bro !!
LikeLike
Declare @val nvarchar(MAX)
declare @sql nvarchar(max)
Select @val = COALESCE(@val + ‘], [‘ + cast(sal_date as varchar(100)),cast(sal_date as varchar(100)))
From (select distinct top 100 sal_date from Salaries order by sal_date asc) sal
set @sql = ‘select emp_name, [‘ + @val +
‘] from salaries
pivot (
max(sal_amt)
for sal_date
in ([‘+ @val +’])
)pivottable’;
exec (@sql)
LikeLike