Tags

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


T-SQL Query | [ SQL | The Ticket Approver Puzzle ] – In the puzzle we have ticket details and approver. We have to replace the CreatedBy and ApprovedBy from Name in the approver table. Please check out the sample input and expected output for details.

Sample Input

Table – Approver

ID Name
1001 Ram
1002 Ramesh
1003 Supriya
1004 Sharlee

 

Table – TicketDetails

 

TicketID CreatedBy ApprovedBy
1 1001 1004
2 1001 1004
3 1002 1004
4 1001 1003

Expected Output

TicketID CreatedBy ApprovedBy
1 Ram Sharlee
2 Ram Sharlee
3 Ramesh Sharlee
4 Ram Supriya

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

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


--Create table

CREATE TABLE Approver
(
ID INT
,Name VARCHAR(10)
)
GO


--Insert data
INSERT INTO Approver(ID,Name)
VALUES (1001,'Ram') , (1002,'Ramesh') , (1003,'Supriya') ,(1004,'Sharlee')


--Verify data
SELECT ID,Name FROM Approver


--Create table
CREATE TABLE TicketDetails
(
TicketID INT
,CreatedBy INT
,ApprovedBy INT
)
GO

--Insert data
INSERT INTO TicketDetails(TicketID,CreatedBy,ApprovedBy)
VALUES
(1,1001,1004),
(2,1001,1004),
(3,1002,1004),
(4,1001,1003)

--Verify data
SELECT TicketID,CreatedBy,ApprovedBy FROM TicketDetails

UPDATE – 24-Apr-2015 – Solution 1 


--

SELECT 
	t.TicketID,a.Name CreatedBy , b.Name ApprovedBy 
FROM 
TicketDetails t 
	INNER JOIN Approver a ON a.ID = t.CreatedBy
	INNER JOIN Approver b ON b.ID = t.ApprovedBy
ORDER BY t.TicketID 


--

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

Keep Learning

http://MSBISkills.com