Tags

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


SQL Puzzle | Time spent by employee in an organization puzzle

In this puzzle we have two tables named tblEmployee and tblTransfer. An employee can transfer to many jobs in one or several sites.
Here we need to calculate the total time that an employee had spent in one particular site. In this specific case we need to find out how much employee 1 & 2 had spent in site 2 only.

Please check out the sample input values and sample expected output below.

Sample Input

table tblTransfere

empNo empName empHireDate empHireSite billet retireDate
1 ABC 2000-05-12 00:00:00.000 1 1 2017-08-20 00:00:00.000
2 EFG 1998-10-11 00:00:00.000 2 2 NULL
3 XYZ 1985-03-24 00:00:00.000 1 3 NULL

table tblTransfere

empNo trfFromSite trfToSite trfDate trfBillet
1 1 2 2001-06-10 00:00:00.000 4
1 2 2 2003-01-10 00:00:00.000 5
1 2 2 2005-11-10 00:00:00.000 6
1 2 3 2010-06-25 00:00:00.000 7
1 3 2 2015-08-27 00:00:00.000 8
2 2 2 1998-10-11 00:00:00.000 9
2 2 2 2000-01-14 00:00:00.000 10
2 2 2 2002-11-07 00:00:00.000 11
2 2 3 2003-02-01 00:00:00.000 12
2 3 3 2005-09-07 00:00:00.000 13
2 3 1 2007-03-03 00:00:00.000 14
2 1 1 2008-09-28 00:00:00.000 15
2 1 1 2011-12-26 00:00:00.000 16
2 1 2 2013-01-06 00:00:00.000 17
2 2 2 2015-04-06 00:00:00.000 18
2 2 1 2016-11-07 00:00:00.000 19

Expected Output

empno DaysSpendinSite2
1 4026
2 2975

Script

Use below script to create table and insert sample data into it.

--

/****** Object:  Table [dbo].[tblEmployee]    Script Date: 10/1/2017 8:56:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblEmployee](
	[empNo] [int] NULL,
	[empName] [varchar](7) NULL,
	[empHireDate] [datetime] NULL,
	[empHireSite] [int] NULL,
	[billet] [int] NULL,
	[retireDate] [datetime] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tblTransfere]    Script Date: 10/1/2017 8:56:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblTransfere](
	[empNo] [int] NULL,
	[trfFromSite] [int] NULL,
	[trfToSite] [int] NULL,
	[trfDate] [datetime] NULL,
	[trfBillet] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[tblEmployee] ([empNo], [empName], [empHireDate], [empHireSite], [billet], [retireDate]) VALUES (1, N'ABC', CAST(0x00008F3000000000 AS DateTime), 1, 1, CAST(0x0000A7D500000000 AS DateTime))
GO
INSERT [dbo].[tblEmployee] ([empNo], [empName], [empHireDate], [empHireSite], [billet], [retireDate]) VALUES (2, N'EFG', CAST(0x00008CED00000000 AS DateTime), 2, 2, NULL)
GO
INSERT [dbo].[tblEmployee] ([empNo], [empName], [empHireDate], [empHireSite], [billet], [retireDate]) VALUES (3, N'XYZ', CAST(0x0000799800000000 AS DateTime), 1, 3, NULL)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (1, 1, 2, CAST(0x000090BA00000000 AS DateTime), 4)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (1, 2, 2, CAST(0x000092FD00000000 AS DateTime), 5)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (1, 2, 2, CAST(0x0000970800000000 AS DateTime), 6)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (1, 2, 3, CAST(0x00009DA000000000 AS DateTime), 7)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (1, 3, 2, CAST(0x0000A50100000000 AS DateTime), 8)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (2, 2, 2, CAST(0x00008CED00000000 AS DateTime), 9)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (2, 2, 2, CAST(0x00008EB900000000 AS DateTime), 10)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (2, 2, 2, CAST(0x000092BD00000000 AS DateTime), 11)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (2, 2, 3, CAST(0x0000931300000000 AS DateTime), 12)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (2, 3, 3, CAST(0x000096C800000000 AS DateTime), 13)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (2, 3, 1, CAST(0x000098E600000000 AS DateTime), 14)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (2, 1, 1, CAST(0x00009B2500000000 AS DateTime), 15)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (2, 1, 1, CAST(0x00009FC500000000 AS DateTime), 16)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (2, 1, 2, CAST(0x0000A13E00000000 AS DateTime), 17)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (2, 2, 2, CAST(0x0000A47200000000 AS DateTime), 18)
GO
INSERT [dbo].[tblTransfere] ([empNo], [trfFromSite], [trfToSite], [trfDate], [trfBillet]) VALUES (2, 2, 1, CAST(0x0000A6B700000000 AS DateTime), 19)
GO
 

--

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

Solution – 1


--
 
select empno, SUM(Diffs) [DaysSpendinSite2] 
FROM
(
	SELECT *,DATEDIFF(day,trfDate,nxt) Diffs FROM
	(
		
		SELECT u.* , CASE WHEN LEAD(trfDate) OVER (PARTITION BY e.empNo ORDER BY trfDate) IS NULL AND retireDate IS NOT NULL THEN retireDate
						WHEN LEAD(trfDate) OVER (PARTITION BY e.empNo ORDER BY trfDate) IS NULL AND retireDate IS NULL THEN GETDATE()
					ELSE 
					LEAD(trfDate) OVER (PARTITION BY e.empNo ORDER BY trfDate) END nxt
		FROM 
		(
			select empno , empHireSite trfFromSite , empHireSite trfToSite , empHireDate trfDate , billet trfBillet from tblEmployee
			UNION ALL
			select empNo	,trfFromSite,	trfToSite,	trfDate	,trfBillet  from tblTransfere
			
		)u INNER JOIN tblEmployee e ON u.empno = e.empno
	)i
)p
WHERE trfToSite = 2
GROUP BY empno

--

Output


/*------------------------
Output
------------------------*/
empno       DaysSpendinSite2
----------- ----------------
1           4026
2           2975

(2 row(s) affected)

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com