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


--

SELECT * FROM
(
       SELECT DISTINCT CAST(p.[Date] as DATE) [Date] FROM testDate p
)p
CROSS APPLY
(
       SELECT MIN([Date]) MinDate , MAX([Date]) MaxDate FROM testDate t
       WHERE CAST(t.[DATE] AS DATE) = ((p.[DATE]))
)r
CROSS APPLY
(
       SELECT [value],[Time] FROM testDate t
       WHERE (t.[Date] = r.MinDate OR  t.[Date] = r.MaxDate)
)s
 
--

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

Advertisements