Tags

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


SQL Puzzle | The Time Difference Puzzle

Puzzle Statement

The Puzzle is simple. You have 2 string columns. The format of these columns are HHH:MM:SS. Here you have subtract col Check out sample input and output for details.

Sample Input

ColumnA ColumnB
555:59:17 251:11:59
367:41:23 244:10:20
10:00:00 11:12:12
118:25:43 31:13:30
129:23:34 32:32:30

Expected output

ColumnA ColumnB TimeDiff
555:59:17 251:11:59 -304:47:18
367:41:23 244:10:20 -123:31:03
10:00:00 11:12:12 01:12:12
118:25:43 31:13:30 -87:12:13
129:23:34 32:32:30 -96:51:04

Script

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

--


CREATE TABLE SubtractTime
(
	 ColumnA VARCHAR(10)                          
	,ColumnB VARCHAR(10)
)
GO

INSERT INTO SubtractTime VALUES
('555:59:17', '251:11:59'),
('367:41:23', '244:10:20'),
('10:00:00'  , '11:12:12'),
('118:25:43', '31:13:30'),
('129:23:34', '32:32:30') 
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 #

--


SELECT 
	  s.ColumnA
	, s.ColumnB 
	, z8.TimeDiff 
FROM SubtractTime s
CROSS APPLY
(
	SELECT CHARINDEX(':',ColumnA) char1 , CHARINDEX(':',ColumnB) char2 
)z
CROSS APPLY
(
	SELECT CAST(SUBSTRING(ColumnA,1, char1-1) AS INT) Hrs1, CAST(SUBSTRING(ColumnB,1, char2-1) AS INT) Hrs2
)z1
CROSS APPLY
(
	SELECT CAST(SUBSTRING(ColumnA,1, char1-1) AS INT) Hrs1, CAST(SUBSTRING(ColumnB,1, char2-1) AS INT) Hrs2
)z2
CROSS APPLY
(
	SELECT CAST(RIGHT(ColumnA,2) AS INT) Sec1, CAST(RIGHT(ColumnB,2) AS INT) Sec2
)z3
CROSS APPLY
(
	SELECT CAST(SUBSTRING(ColumnA,char1+1,2) AS INT) Mins1, CAST(SUBSTRING(ColumnB,char2+1,2) AS INT) Mins2
)z4
CROSS APPLY
(
	SELECT z2.Hrs1*60*60+Mins1*60+Sec1 T1 , z2.Hrs2*60*60+Mins2*60+Sec2 T2
)z5
CROSS APPLY
(
	SELECT CASE WHEN T2 >= T1 THEN T2-T1
	            WHEN T1 > T2 THEN T1-T2 
			END Diff
		, CASE WHEN T2>=T1 THEN '' ELSE '-' END Minus
)z6
CROSS APPLY
(
	SELECT Diff / 3600 Hrs , ( Diff / 60 ) % 60 Mins , Diff % 60 Secs
)z7
CROSS APPLY
(
	SELECT CONCAT(z6.Minus,z7.Hrs,':',RIGHT(CONCAT('0',z7.Mins),2),':',RIGHT(CONCAT('0',z7.Secs),2)) TimeDiff
)z8
GO

--

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 Kumar Khowal 

Http://MSBISkills.com