SQLTeam.com | Weblogs | Forums

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


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


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.


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


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!

create table #t(d datetime)

insert into #t
('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
	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


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!


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.


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.


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!


Thanks gbritton!