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

1 19m
2 1h 17m
3 9m 3s
4 8h 1m
5 8h 2m 2s
6 5d 2h 12m 13s
7 NULL

Expected Output

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

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”.

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.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/