SQL Puzzle | Calculate Seconds from Day Hour Minute and Seconds Puzzle | SQL Interview Question

In this puzzle you have to parse the nvarchar column and calculate the number of seconds in that column. Can you do that in a single select?. Please check the sample input and the expected output.

Sample Input

Expected Output

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

--

CREATE TABLE Times
(
	 Id INT
	,Tmt NVARCHAR(100)
)
GO

INSERT INTO Times VALUES
(1,'19m'),                       
(2,'1h 17m'),                     
(3,'9m 3s'),          
(4,'8h 1m'),                 
(5,'8h 2m 2s'),                
(6,'5d 2h 12m 13s'),
(7,NULL)
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 e.Id,e.TMT,SUM(rt) InSeconds
FROM
(
	SELECT T.Id , T.TMT , SUBSTRING(Value,0,LEN(value)) V ,RIGHT(Value,1) D 
	, u1.rt
	FROM Times T
	CROSS APPLY (SELECT * FROM STRING_SPLIT(T.TMT,' '))u
	CROSS APPLY ( SELECT CASE 
			WHEN RIGHT(u.Value,1) = 'd' THEN SUBSTRING(Value,0,LEN(value)) * 24 * 60 * 60
			WHEN RIGHT(u.Value,1) = 'h' THEN SUBSTRING(Value,0,LEN(value)) * 60 * 60
			WHEN RIGHT(u.Value,1) = 'm' THEN SUBSTRING(Value,0,LEN(value)) * 60
			WHEN RIGHT(u.Value,1) = 's' THEN SUBSTRING(Value,0,LEN(value)) END rt )u1
	)e GROUP BY e.Id,e.TMT
GO


--

Output-1

--                            

Id          TMT      InSeconds
----------- ---------------------- -----------
1           19m                    1140
2           1h 17m                 4620
6           5d 2h 12m 13s          439933
4           8h 1m                  28860
5           8h 2m 2s               28922
3           9m 3s                  543

(6 rows affected)

--

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

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com