Home SQL select hours from two time fields but between a range
Reply: 1

SQL select hours from two time fields but between a range

Claudio Stevanato
1#
Claudio Stevanato Published in 2017-12-06 08:25:58Z

I am using SQL Server 2014 and I have tried several things before asking here.

What I need to accomplish is this: I have a table which stores two time columns, which have values for example like 9:00 or 2:34 etc etc.

I need to calculate the daily hours and the night hours of works. The daily hours have to be counted from 8:00 to 20:00 and the night ones from 20:00 to 8:00

If I have two time fields there can be values as

4:00 and 12:00

12:00 and 20:00

22:00 and 6:00

What I want to do is to retrieve two decimal values that correspond to the daily hours and the night hours, maybe there's a SQL function i don't know which passing two values and a range do this task for me, or maybe it's the case to build one on my own

Leran2002
2#
Leran2002 Reply to 2017-12-06 10:41:21Z

I think you can use an auxiliary table

CREATE TABLE HourInfo(
  h time NOT NULL,
  isNight bit NOT NULL
CONSTRAINT PK_HourInfo PRIMARY KEY(h)
)
GO

INSERT HourInfo(h,isNight)VALUES
('00:00',1),('01:00',1),('02:00',1),('03:00',1),('04:00',1),('05:00',1),('06:00',1),('07:00',1),('08:00',1),
('09:00',0),('10:00',0),('11:00',0),('12:00',0),('13:00',0),('14:00',0),('15:00',0),('16:00',0),('17:00',0),('18:00',0),('19:00',0),
('20:00',1),('21:00',1),('22:00',1),('23:00',1)
GO

CREATE TABLE MinuteInfo(
  m time NOT NULL,
  isNight bit NOT NULL
CONSTRAINT PK_MinuteInfo PRIMARY KEY(m)
)
GO

INSERT MinuteInfo(m,isNight)
SELECT DATEADD(MINUTE,m.m,h.h) m,h.isNight
FROM HourInfo h
CROSS JOIN
  (
    SELECT i*10+j AS m
    FROM (VALUES(0),(1),(2),(3),(4),(5)) n1(i)
    CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n2(j)
  ) m
GO

And use the following query

SELECT CAST('04:00' AS time) t1,CAST('12:00' AS time) t2 INTO #TestData
UNION ALL
SELECT CAST('12:00' AS time) t1,CAST('20:00' AS time) t2
UNION ALL
SELECT CAST('22:00' AS time) t1,CAST('06:00' AS time) t2
UNION ALL
SELECT CAST('00:00' AS time) t1,CAST('05:15' AS time) t2

/*
SELECT
  d.t1,
  d.t2,
  COUNT(CASE WHEN h.isNight=1 THEN 1 END) [night hours],
  COUNT(CASE WHEN h.isNight=0 THEN 1 END) [daily hours]
FROM #TestData d
JOIN HourInfo h
ON
     (d.t1>d.t2 AND ((h.h>=d.t1 AND h.h<=CAST('23:00' AS time)) OR (h.h>=CAST('00:00' AS time) AND h.h<d.t2)))
  OR (d.t1<=d.t2 AND h.h>=d.t1 AND h.h<=d.t2)
GROUP BY d.t1,d.t2
*/

SELECT
  d.t1,
  d.t2,
  COUNT(CASE WHEN m.isNight=1 THEN 1 END)/60. [night hours],
  COUNT(CASE WHEN m.isNight=0 THEN 1 END)/60. [daily hours]
FROM #TestData d
JOIN MinuteInfo m
ON
     (d.t1>d.t2 AND ((m.m>=d.t1 AND m.m<=CAST('23:59' AS time)) OR (m.m>=CAST('00:00' AS time) AND m.m<d.t2)))
  OR (d.t1<=d.t2 AND m.m>=d.t1 AND m.m<d.t2)
GROUP BY d.t1,d.t2

DROP TABLE #TestData
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.30254 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO