SQL Puzzle | The Date Puzzle – II

Tags

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


SQL Puzzle | The Date Puzzle – II

This puzzle is simple. Here you need to write a query to get minimum and maximum value records.

For details please check out the sample input and the expected output below.

Sample Inputs

Date value time
2016-09-20 17:48:23.447 63 17:48:00.0000000
2016-09-20 17:38:08.880 64 17:38:00.0000000
2016-09-20 17:32:03.247 65 17:32:00.0000000
2016-09-20 17:16:23.273 68 17:16:00.0000000
2016-09-20 17:06:27.223 69 17:06:00.0000000
2016-09-20 16:58:27.693 70 16:58:00.0000000
2016-09-20 16:46:27.030 71 16:46:00.0000000
2016-09-20 16:38:19.490 72 16:38:00.0000000
2016-09-20 16:27:07.743 74 16:27:00.0000000
2016-09-20 16:22:19.243 75 16:22:00.0000000
2016-09-20 16:08:24.197 77 16:08:00.0000000
2016-09-20 15:57:02.757 79 15:57:00.0000000
2016-09-20 15:47:23.227 80 15:47:00.0000000
2016-09-20 15:38:23.937 77 15:38:00.0000000
2016-09-20 15:26:22.017 73 15:26:00.0000000
2016-09-20 15:17:23.277 73 15:17:00.0000000
2016-09-20 15:06:36.990 74 15:06:00.0000000
2016-09-20 14:56:42.550 76 14:56:00.0000000
2016-09-20 14:47:07.823 77 14:47:00.0000000
2016-09-20 14:37:23.907 79 14:37:00.0000000
2016-09-20 14:27:38.387 81 14:27:00.0000000
2016-09-20 14:17:23.307 83 14:17:00.0000000
2016-09-20 14:07:23.687 85 14:07:00.0000000
2016-09-20 13:57:08.230 87 13:57:00.0000000
2016-09-20 13:47:23.307 88 13:47:00.0000000
2016-09-20 13:37:23.463 89 13:37:00.0000000
2016-09-20 13:27:44.067 90 13:27:00.0000000
2016-09-20 13:16:41.130 91 13:16:00.0000000
2016-09-20 13:06:22.003 92 13:06:00.0000000
2016-09-20 13:01:33.437 93 13:01:00.0000000
2016-09-20 12:47:23.217 95 12:47:00.0000000
2016-09-20 12:37:04.723 96 12:37:00.0000000

Expected Output

MinDate MaxDate Value Time
9/20/2016 12:37 9/20/2016 17:48 63 5:48:00 PM
9/20/2016 12:37 9/20/2016 17:48 96 12:37:00 PM

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE testDate
(
       [Date] DATETIME
       ,[value] INT
       ,[time] TIME
)
GO
 
INSERT INTO testDate VALUES
('2016-09-20 17:48:23.447',      63      ,'17:48'),
('2016-09-20 17:38:08.880',      64      ,'17:38'),
('2016-09-20 17:32:03.247',      65      ,'17:32'),
('2016-09-20 17:16:23.273',      68      ,'17:16'),
('2016-09-20 17:06:27.223',      69      ,'17:06'),
('2016-09-20 16:58:27.693',      70      ,'16:58'),
('2016-09-20 16:46:27.030',      71      ,'16:46'),
('2016-09-20 16:38:19.490',      72      ,'16:38'),
('2016-09-20 16:27:07.743',      74      ,'16:27'),
('2016-09-20 16:22:19.243',      75      ,'16:22'),
('2016-09-20 16:08:24.197',      77      ,'16:08'),
('2016-09-20 15:57:02.757',      79      ,'15:57'),
('2016-09-20 15:47:23.227',      80      ,'15:47'),
('2016-09-20 15:38:23.937',      77      ,'15:38'),
('2016-09-20 15:26:22.017',      73      ,'15:26'),
('2016-09-20 15:17:23.277',      73      ,'15:17'),
('2016-09-20 15:06:36.990',      74      ,'15:06'),
('2016-09-20 14:56:42.550',      76      ,'14:56'),
('2016-09-20 14:47:07.823',      77      ,'14:47'),
('2016-09-20 14:37:23.907',      79      ,'14:37'),
('2016-09-20 14:27:38.387',      81      ,'14:27'),
('2016-09-20 14:17:23.307',      83      ,'14:17'),
('2016-09-20 14:07:23.687',      85      ,'14:07'),
('2016-09-20 13:57:08.230',      87      ,'13:57'),
('2016-09-20 13:47:23.307',      88      ,'13:47'),
('2016-09-20 13:37:23.463',      89      ,'13:37'),
('2016-09-20 13:27:44.067',      90      ,'13:27'),
('2016-09-20 13:16:41.130',      91      ,'13:16'),
('2016-09-20 13:06:22.003',      92      ,'13:06'),
('2016-09-20 13:01:33.437',      93      ,'13:01'),
('2016-09-20 12:47:23.217',      95      ,'12:47'),
('2016-09-20 12:37:04.723',      96      ,'12:37')
GO


--

SOLUTION – 1

Solution will be uploaded tomorrow🙂

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | 2 Digit Puzzle [No Reverse Function] (Solution Time :))

Tags

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


SQL Puzzle | 2 Digit Puzzle

Here you need to write a query to get distinct digits from the table. Now here half data has been reversely inserted. e.g. For 21 we have inserted 12. So out of 12 and 21 we need 21 as a single row.

For details please check out the sample input and the expected output below.

Sample Inputs

Id
21
12
34
43
29
92

Expected Output

Id
21
43
92

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Reverse function is not allowed.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--



CREATE TABLE [2Digits]
(
	Id INT
)
GO

INSERT INTO [2Digits] VALUES
(21),
(12),
(34),
(43),
(29),
(92)



--

SOLUTION – 1


--

SELECT 
	DISTINCT CASE WHEN Id % 10 < (id / 10) THEN (id / 10)*10 + (Id % 10) ELSE (Id % 10 )*10+(id / 10) END Id 
FROM 
[2Digits]

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | The Date Puzzle – 1

Tags

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


SQL Puzzle | The Date Puzzle – 1

Here you need to write a query that get the number of hours each employee worked on 09/01/2014 between 10am to 1pm.

For details please check out the sample input and the expected output below.

Sample Inputs

DECLARE @Date AS DATE = ‘2014-09-01′
DECLARE @StartTime AS TIME = ’10:00′
DECLARE @EndTime AS TIME = ’13:00’

EmployeeID StartDateTime EndDateTime
1 2014-09-01 08:00:00.000 2014-09-01 16:45:00.000
2 2014-09-01 15:15:00.000 2014-09-02 18:00:00.000
3 2014-09-01 11:00:00.000 2014-09-01 18:45:00.000
4 2014-09-01 10:00:00.000 2014-09-01 18:45:00.000
5 2014-09-01 09:00:00.000 2014-09-01 18:45:00.000
6 2014-09-01 10:00:00.000 2014-09-01 18:45:00.000

Expected Output

Output
Emp 1 has 3 Hours
Emp 2 has 0 Hours
Emp 3 has 2 Hours
Emp 4 has 3 Hours
Emp 5 has 3 Hours
Emp 6 has 3 Hours

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE testDt
(
        EmployeeID INT              
       ,StartDateTime DATETIME                
       ,EndDateTime DATETIME
)
GO
 
INSERT INTO testDt VALUES
(1   ,'09/01/2014 08:00:00'    ,'09/01/2014 16:45:00'),
(2   ,'09/01/2014 15:15:00'    ,'09/02/2014 18:00:00'),
(3   ,'09/01/2014 11:00:00'    ,'09/01/2014 18:45:00'),
(4   ,'09/01/2014 10:00:00'    ,'09/01/2014 18:45:00'),
(5   ,'09/01/2014 09:00:00'    ,'09/01/2014 18:45:00'),
(6   ,'09/01/2014 10:00:00'    ,'09/01/2014 18:45:00')
GO
 
select * from testDt

DECLARE @Date AS DATE = '2014-09-01'
DECLARE @StartTime AS TIME = '10:00'
DECLARE @EndTime AS TIME = '13:00'

--

SOLUTION – 1

Solution will be uploaded tomorrow🙂

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | Matches won & lost by Each team [Asia Cup Puzzle – II] (Solution Time :) )

Tags

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


SQL Puzzle | Matches won & lost by Each team [Asia Cup Puzzle – II]

In this puzzle you have a table called Team. Here you have information like which team has played with which team and who won that match. You have write a T-SQL to get all teams, number of matches they have played and the number of matches won by each team.

For details please check out the sample input and the expected output below.

Sample Inputs

TeamA TeamB WonBy
Pakistan India India
Sri Lanka India India
Bangladesh India India
Sri Lanka Pakistan Sri Lanka
Bangladesh Pakistan Bangladesh
Bangladesh Sri Lanka Sri Lanka
India Sri Lanka India

Expected Output

Team MatchesPlayed MatchesWon
Bangladesh 3 1
India 4 4
Pakistan 3 0
Sri Lanka 4 2

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--


CREATE TABLE [dbo].[AsiaCup](
	[TeamA] [varchar](10) NULL,
	[TeamB] [varchar](10) NULL,
	[WonBy] [varchar](10) NULL
) ON [PRIMARY]

GO

INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Pakistan', N'India', N'India')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Sri Lanka', N'India', N'India')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Bangladesh', N'India', N'India')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Sri Lanka', N'Pakistan', N'Sri Lanka')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Bangladesh', N'Pakistan', N'Bangladesh')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'Bangladesh', N'Sri Lanka', N'Sri Lanka')
GO
INSERT [dbo].[AsiaCup] ([TeamA], [TeamB], [WonBy]) VALUES (N'India', N'Sri Lanka', N'India')
GO

--

SOLUTION – 1


--

SELECT tbl.Team, COUNT(1) MatchesPlayed, SUM(CASE WHEN team = wonby THEN 1 ELSE 0 end) MatchesWon
FROM 
(
	SELECT a.teamA team, a.wonby
	FROM    asiacup a 
	UNION all
	select b.teamb, b.wonby FROM asiacup b
) tbl
GROUP BY tbl.team


--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

SQL Puzzle | The Asia Cup Puzzle [Solution Time :)]

Tags

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


SQL Puzzle | The Asia Cup Puzzle

In this puzzle you have a table called Team and each team from the team table will play with each other once. You have a write a T-SQL for the same. This was asked to my friend at a recent technical interview.

For details please check out the sample input and the expected output below.

Sample Inputs

TeamName
India
Pakistan
Sri Lanka
Bangladesh

Expected Output

TeamA TeamB
Bangladesh India
India Pakistan
Bangladesh Pakistan
India Sri Lanka
Pakistan Sri Lanka
Bangladesh Sri Lanka

Rules/Restrictions

  • The challenge is to do it with T-SQL statements only.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data


--

CREATE TABLE Team
(
	TeamName VARCHAR(10)
)
GO

INSERT INTO Team
VALUES ('India'),('Pakistan'),('Sri Lanka'),('Bangladesh')
GO

--

SOLUTION – 1


--

SELECT 
	T1.TeamName TeamA, T2.TeamName TeamB 
FROM 
	Team T1 CROSS APPLY Team T2 
WHERE 
	T1.TeamName < T2.TeamName

--

SOLUTION – 2


--

SELECT 
	T1.TeamName TeamA, T2.TeamName TeamB 
FROM 
	Team T1 JOIN Team T2 
ON 
	T1.TeamName < T2.TeamName

--

SOLUTION – 3


--

SELECT a.TeamName TeamA , r.TeamName TeamB FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM Team) a 
CROSS APPLY
( 
	SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM Team) b WHERE b.rnk < a.rnk 
)r

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com