Series of datetime entries in sql - verify available 10 hours in a 24 hour interval

Transact-SQL
1

Hello, refer to the following #temptable. It has a datetime field and in real case scenario the field could contain multiple rows. I want to make sure in a 24 hour interval for any combination of rows that there is atleast 10 hours interval and show it as an additional calculated column. So for example as shown below the first 3 colums are in a 24 hour interval. I want to show a calculated field with values atleast 10 hours. If all the datetime differences are less than 10, I want to display 'Custom'. How do you do this?

create table #temptable(mdatetime datetime, mvalue int, mhours int )

insert into #temptable
select '2016-12-28 10:00', 0, 0 union
select '2016-12-28 13:00', 3, 0 union
select '2016-12-29 9:00', 20, 0 union --rest
select '2016-12-30 8:00', 23, 0 union --rest
select '2016-12-30 10:00', 2, 0 union
select '2016-12-31 12:00', 26, 0
select * From #temptable
drop table #temptable

2

that's tricky. If you think of a solution for an OOP language, you'll wind up writing a loop. Do the same in SQL with a CURSOR. You can probably do it with a recursive CTE but then only for small tables.

3

If you generated row numbers then joined to row number - 1, you could get the value and calculate the interval.

4

yeah andrewbb could you please share a sample code of this? I can do the rownumber - 1 join. However, isn't this the same as using a LAG function which gives the previous row? I have used the LAG function which i think is the same as joining the table with rownumber-1 idea. Anyways, it would be cool if you could give me some sql code. Thanks!

5 
create table #t(d datetime)

insert into #t
values
('2016-12-28 10:00'),
('2016-12-28 13:00'),
('2016-12-29 9:00'),
('2016-12-30 8:00'),
('2016-12-30 10:00'),
('2016-12-31 12:00')


with withrownumbers as
(	select
		row = ROW_NUMBER() over (order by d),
		*
	from #t
)
select
	StartDate = a.d,
	NextStartDate = b.d,
	IntervalHours = datediff(hour, a.d, isnull(b.d, getdate()))
from withrownumbers a
	left join withrownumbers b
		on (b.row - 1) = a.row
6

Ok Thanks andrewbb! Here is a follow-up question. in the interval hours, you have the difference between startdate and NextStartDate. That's good but can you also think about how this query could be altered a bit for the following condition: I want to make sure in a 24 hour interval for any combination of rows that there is at least 10 hours interval and show it as an additional calculated column. In our example, 2016-12-28 10:00:00.000,
2016-12-28 13:00:00.000 and 2016-12-29 09:00:00.000 all are in a 24 hour interval and there is a gap of 20 hours between 2016-12-28 13:00:00.000 and 2016-12-29 09:00:00.000 (>10 which is a condition i need to have met). So basically the first 3 datetime entries qualify under my business logic. However, if there wasn't atleast 10 hours gap between any combinations in a 24 hour window, I need the sql query return an "N/A" or "Custom". Can you please try to see if you can add this scenario into a sql code? Thanks!

7

I don't mind helping here and there, because I've done all this many years ago. It is occasionally interesting to review, because I haven't thought about it in a long time.

But, it's not my job to do your job. You'd have to pay me. AND you'd have to tell your boss who is doing the work.

8

I can't see how that will work. that won't cover any 24 hour period, since a 24-hour period could be over 10 rows or two 24 hour periods could be on just one row. Plus if you use 6 hours from one row covering 18 hours, you need to hold on to those 18 hours to compute the next 24-hour period. At least, that's what "24 hour interval for any combination of rows" means to me.

9

Thanks for your contribution andrewbb. I am not asking you to do my job but ideas on a fairly complex issue. This is a forum where ideas flow right? Thanks anyway!

10

Thanks gbritton!

11 
with cteData (mRow, mDatetime, mvalue)
as
(select 1, '2016-12-28 10:00', 0 union
 select 2, '2016-12-28 13:00', 3 union
 select 3, '2016-12-29 9:00', 20 union --rest
 select 4, '2016-12-30 8:00', 23 union --rest
 select 5, '2016-12-30 10:00', 2 union
 select 6, '2016-12-31 12:00',26)
select
	  d1.mdatetime
	, d1.mvalue
	, DateDiff(hh, d2.mdatetime, d1.mdatetime) mHours
from cteData d1
left join ctedata d2 on d2.mrow = d1.mrow - 1
order by d1.mDatetime;

image

12

Hi Joseph, thank you for the response. I have got to this point as well but how do you do the following?
image

13 
with 
   cteData (mRow, mDatetime, mvalue)
      as
      (select 1, '2016-12-28 10:00', 0 union
       select 2, '2016-12-28 13:00', 3 union
       select 3, '2016-12-29 9:00', 20 union --rest
       select 4, '2016-12-30 8:00', 23 union --rest
       select 5, '2016-12-30 10:00', 2 union
       select 6, '2016-12-31 12:00',26),
   cteNum1(num) AS (SELECT n FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(n)),
   cteNum2(num) AS (SELECT a.num FROM cteNum1 a CROSS JOIN cteNum1 b),
   cteNum3(num) AS (SELECT a.num FROM cteNum2 a CROSS JOIN cteNum2 b),
   cteNum(num) AS (SELECT Row_Number() OVER(ORDER BY (SELECT NULL)) FROM cteNum3 UNION SELECT 0),
   cteHours(num, hour) AS (SELECT num, DateAdd(hh, num, (SELECT Min(mDatetime) FROM cteData)) FROM cteNum)
select
	  d1.mDatetime d1mDatetime
	, d1.mvalue d1mValue
	, d1.mRow d1mRow
   , d1.mvalue d1mValue
   , d2.mRow d2mRow
   , d2.mDatetime d2mDatetime
   , d2.mvalue d2mValue
   , CASE WHEN Abs(h.num - IsNull(h1.num,0)) <= 10 THEN 'Green' ELSE 'Red' END Satus
from cteData d1
JOIN cteHours h ON d1.mDatetime=h.hour
left join ctedata d2 on d2.mrow = d1.mrow - 1
LEFT JOIN cteHours h1 ON d2.mDatetime = h1.hour
order by d1.mDatetime;

image

1 Like
14

This sets me in the right direction! Thank you much Joseph!