Tags

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


T-SQL Query | [ The Friday Salary Puzzle ]  – In this puzzle we have to find the last friday of the DOJ(Month) of each person. If the DOJ is greater than 15 then we have find the last friday for next month. Please check out the sample input and expected output for details.

Sample Input

ID Name Salary DOJ
1 A 100 02-Oct-14
2 B 200 16-Mar-13
3 C 300 02-Jan-14
4 D 400 17-Feb-12
5 E 500 08-Feb-12

Expected Output

ID NAME Salary DOJ
1 A 100 31-Oct-14
2 B 200 26-Apr-13
3 C 300 31-Jan-14
4 D 400 30-Mar-12
5 E 500 24-Feb-12

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 [dbo].[EmployeeSalary]
(
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Salary] [float] NULL,
[DOJ] [datetime] NULL
)
GO

--Insert Data
INSERT [dbo].[EmployeeSalary] ([ID], [Name], [Salary], [DOJ]) VALUES (1, N'A', 100, CAST(0x0000A3B800000000 AS DateTime))
GO
INSERT [dbo].[EmployeeSalary] ([ID], [Name], [Salary], [DOJ]) VALUES (2, N'B', 200, CAST(0x0000A18300000000 AS DateTime))
GO
INSERT [dbo].[EmployeeSalary] ([ID], [Name], [Salary], [DOJ]) VALUES (3, N'C', 300, CAST(0x0000A2A700000000 AS DateTime))
GO
INSERT [dbo].[EmployeeSalary] ([ID], [Name], [Salary], [DOJ]) VALUES (4, N'D', 400, CAST(0x00009FFA00000000 AS DateTime))
GO
INSERT [dbo].[EmployeeSalary] ([ID], [Name], [Salary], [DOJ]) VALUES (5, N'E', 500, CAST(0x00009FF100000000 AS DateTime))
GO

--Verify Data
SELECT * FROM [EmployeeSalary]

UPDATE – 20-Apr-2015 – Solution 1


--


--Solution 1



;WITH CTE AS
(
     SELECT ID , NAME , Salary , DOJ , 1 Value FROM [EmployeeSalary] WHERE DAY(DOJ) > 15
     UNION ALL
     SELECT r.ID , r.NAME , r.Salary , DATEADD(M,1,r.DOJ) DOJ , Value + 1 FROM [EmployeeSalary] r INNER JOIN CTE on r.ID = CTE.ID
     WHERE Value <= 1 AND DAY(r.DOJ) > 15
)
,CTE2 AS 
(
     SELECT  ID , NAME , Salary , MAX(DOJ) DOJ
     FROM CTE 
     GROUP BY ID,Name,Salary
     UNION ALL SELECT * FROM [EmployeeSalary] WHERE DAY(DOJ) < 15
)
,CTE3 AS
(
     SELECT ID , NAME , Salary , DOJ , 1 Value FROM CTE2 a
     UNION ALL
     SELECT b.ID , b.NAME , b.Salary , DATEADD(D,1,b.DOJ) DOJ , Value + 1 FROM CTE3 b INNER JOIN CTE2 a ON a.ID = b.ID
     WHERE DAY(b.DOJ) < DAY(EOMONTH(b.DOJ))     
)
,CTE4 AS
(
     SELECT * , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE DESC) rnks FROM CTE3 WHERE DATENAME(DW,DOJ) = 'Friday' 
)
SELECT ID , NAME , Salary , DOJ FROM CTE4 WHERE rnks = 1



--

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

Keep Learning

http://MSBISkills.com