www.sql.ru/forum/actualthread.aspx

create table #temp(status int, act_time datetime)



insert #temp values(1, '19000101 12:00:00')

insert #temp values(0, '19000101 12:15:00')

insert #temp values(1, '19000101 12:16:00')

insert #temp values(0, '19000101 12:20:00')

insert #temp values(1, '19000101 12:40:00')



select sw_on, sw_on_time, sw_off, sw_off_time from

(select a.status as sw_on, a.act_time as sw_on_time, min(b.status) as sw_off, min(ISNULL(b.act_time, GETDATE())) as sw_off_time

from #temp a

left outer join #temp b on b.status <> a.status and b.act_time > a.act_time

group by a.status, a.act_time

) AS a



drop table #temp

 

declare @t table(state int, time datetime)



insert @t select 1, '20020405 10:00'

insert @t select 0, '20020405 11:00' -- +1

insert @t select 1, '20020405 12:00'

insert @t select 0, '20020405 13:00' -- +1

insert @t select 1, '20020405 14:00'





declare @d datetime

select @d='20020405 15:00' -- +1



select sum((datediff(mi,time,@d))*(state*2-1)) from @t

 

declare @my_date datetime;



-- чисто для примера





select @my_date = getdate();







select cust_number, sum(duration) from



(



SELECT DISTINCT



cust_number,



datediff(day, starttime,



(SELECT MIN(endtime)



FROM (select cust_number,subr_sw_on starttime,isnull(subr_sw_off,@my_date) endtime from Liitumised where subr_sw_on<@my_date) AS S3



WHERE S3.cust_number = S1.cust_number



AND S3.endtime >= S1.starttime



AND ISNULL(



(SELECT MIN(starttime)



FROM (select cust_number,subr_sw_on starttime,isnull(subr_sw_off,@my_date) endtime from Liitumised where subr_sw_on<@my_date) AS S4



WHERE S4.cust_number = S1.cust_number



AND S4.endtime > S3.endtime),



S3.endtime + 1) > S3.endtime)







) as duration







FROM (select cust_number,subr_sw_on starttime,isnull(subr_sw_off,@my_date) endtime from Liitumised where subr_sw_on<@my_date) AS S1







WHERE ISNULL(



(SELECT MAX(endtime)



FROM (select cust_number,subr_sw_on starttime,isnull(subr_sw_off,@my_date) endtime from Liitumised where subr_sw_on<@my_date) AS S2



WHERE S2.cust_number = S1.cust_number



AND S2.starttime < S1.starttime),



S1.starttime -1) < S1.starttime



) intervals



group by cust_number

 

T-SQL Tuesday #001: Splitting Date/Time Ranges and Intersections

sqlblog.com/blogs/jonathan_kehayias/archive/2009/12/08/t-sql-tuesday-001-splitting-date-time-ranges-and-intersections.aspx

sqlblog.com/blogs/adam_machanic/archive/2009/12/09/t-sql-tuesday-001-the-roundup.aspx