c# - How to make calculation on time intervals? -
i have problem ,i solve have written long procedure , can't sure covers possible cases .
the problem:
if have main interval time (from b), , secondary interval times (many or no)
(`from x y , x` y` , x`` y`` , ....`) i want sum parts of main interval time (ab) out of secondary intervals in minutes in efficient , least number of conditions (sql server procedure , c# method)?
for example : if main interval 02:00 10:30 , 1 secondary interval 04:00 08:00
now want result : ((04:00 - 02:00) + (10:30 -08:00))* 60
example graph :
in first case result :
((x-a) + (b-y)) * 60 and more complicated when have many secondary periods.
note:
may overlap among secondary intervals happening when have compare main period [a,b] union of at 2 parallel sets of secondary intervals .the first set have contain 1 secondary interval , the second set contains (many or no ) of secondary intervals .for example in graph comparing [a,b] (sets of 2,5)the first set (2) consists of 1 secondary interval , second set (5) consists of 3 secondary intervals . , 's worst case ,i need handle.
for example :
if main interval [15:00,19:40] , have 2 sets of secondary intervals .according rule @ least 1 of these sets should consists of 1 secondary interval. first set [11:00 ,16:00] , second set consists of 2 secondary intervals [10:00,15:00],[16:30,17:45] want result (16:30 -16:00) +(19:40 -17:45)
according comments :
my table :
the first table contains secondary periods ,at 2 sets of secondary periods in same date specific employee. first set contains 1 secondary period in work day (w) [work_st,work_end],and set empty if day weekend [e] , in case no overlap among secondary periods. , second set may contain many secondary periods in same date [check_in,check_out] ,because employee may check_in_out many times in same day.
emp_num day_date work_st work_end check_in check_out day_state 547 2015-4-1 08:00 16:00 07:45 12:10 w 547 2015-4-1 08:00 16:00 12:45 17:24 w 547 2015-4-2 00:00 00:00 07:11 13:11 e the second table contains main period[a,b] , it's 1 period employee @ day (one record)
emp_num day_date mission_in mission_out 547 2015-4-1 15:00 21:30 547 2015-4-2 8:00 14:00 in previous example if have procedure or method required procedure should take 2 parameters :
- the date
- the emp_num
in previous example should ('2015-4-1' ,547)
according explanation :
the main period (mission period)
[a,b]second table : should 1 period in date employee[15:00,21:30]the secondary period passed date
('2015-4-1')employee 2 sets of secondary periods (the worst case) first tablethe first set should contain 1 secondary period (or 0 periods)
[08:00,16:00]second set contain many secondary periods (or 0 periods)[07:45,12:10],[12:45,17:24]
the output should [17:24,21:30] converted minutes
note
all day_date,mission_in,mission_out,work_st,work_end,check_in,check_out datetime fields put time in example simplification , want ignore date part except day_date because it's date calculate based on in addition emp_num.
i've updated answer data example , i'm adding example employee 248 uses case 2 , 5 graph.
--load example data emply 547 select convert(int, 547) emp_num, convert(datetime, '2015-4-1') day_date, convert(datetime, '2015-4-1 08:00') work_st, convert(datetime, '2015-4-1 16:00') work_end, convert(datetime, '2015-4-1 07:45') check_in, convert(datetime, '2015-4-1 12:10') check_out, 'w' day_state #secondaryintervals insert #secondaryintervals select 547, '2015-4-1', '2015-4-1 08:00', '2015-4-1 16:00', '2015-4-1 12:45', '2015-4-1 17:24', 'w' insert #secondaryintervals select 547, '2015-4-2', '2015-4-2 00:00', '2015-4-2 00:00', '2015-4-2 07:11', '2015-4-2 13:11', 'e' select convert(int, 547) emp_num, convert(datetime, '2015-4-1') day_date, convert(datetime, '2015-4-1 15:00') mission_in, convert(datetime, '2015-4-1 21:30') mission_out #mainintervals insert #mainintervals select 547, '2015-4-2', '2015-4-2 8:00', '2015-4-2 14:00' --load more example data employee 548 overlapping secondary intervals insert #secondaryintervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 9:00', '2015-4-1 10:00', 'w' insert #secondaryintervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 10:30', '2015-4-1 12:30', 'w' insert #secondaryintervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 13:15', '2015-4-1 16:00', 'w' insert #mainintervals select 548, '2015-4-1', '2015-4-1 8:00', '2015-4-1 14:00' --populate offline table intervals in #secondaryintervals select row_number() on (order emp_num, day_date, startdatetime, enddatetime) rownum, emp_num, day_date, startdatetime, enddatetime #offline (select emp_num, day_date, work_st startdatetime, work_end enddatetime #secondaryintervals day_state = 'w' group emp_num, day_date, work_st, work_end union select emp_num, day_date, check_in startdatetime, check_out enddatetime #secondaryintervals group emp_num, day_date, check_in, check_out ) secondaryintervals --populate online table select row_number() on (order emp_num, day_date, mission_in, mission_out) rownum, emp_num, day_date, mission_in startdatetime, mission_out enddatetime #online #mainintervals group emp_num, day_date, mission_in, mission_out ------------------------------- --find overlaping offline times ------------------------------- declare @finished tinyint set @finished = 0 while @finished = 0 begin update #offline set #offline.enddatetime = overlapenddates.enddatetime #offline join ( select #offline.rownum, max(overlap.enddatetime) enddatetime #offline join #offline overlap on #offline.emp_num = overlap.emp_num , #offline.day_date = overlap.day_date , overlap.startdatetime between #offline.startdatetime , #offline.enddatetime , #offline.rownum <= overlap.rownum group #offline.rownum ) overlapenddates on #offline.rownum = overlapenddates.rownum --remove online times inside of online times delete #offline #offline join #offline overlap on #offline.emp_num = overlap.emp_num , #offline.day_date = overlap.day_date , #offline.startdatetime between overlap.startdatetime , overlap.enddatetime , #offline.enddatetime between overlap.startdatetime , overlap.enddatetime , #offline.rownum > overlap.rownum --look if there more chains left if not exists( select #offline.rownum, max(overlap.enddatetime) enddatetime #offline join #offline overlap on #offline.emp_num = overlap.emp_num , #offline.day_date = overlap.day_date , overlap.startdatetime between #offline.startdatetime , #offline.enddatetime , #offline.rownum < overlap.rownum group #offline.rownum ) set @finished = 1 end ------------------------------- --modify online times offline ranges ------------------------------- --delete online times inside offline range delete #online #online join #offline on #online.emp_num = #offline.emp_num , #online.day_date = #offline.day_date , #online.startdatetime between #offline.startdatetime , #offline.enddatetime , #online.enddatetime between #offline.startdatetime , #offline.enddatetime --find online times offline range @ beginning update #online set #online.startdatetime = #offline.enddatetime #online join #offline on #online.emp_num = #offline.emp_num , #online.day_date = #offline.day_date , #online.startdatetime between #offline.startdatetime , #offline.enddatetime , #online.enddatetime >= #offline.enddatetime --find online times offline range @ end update #online set #online.enddatetime = #offline.startdatetime #online join #offline on #online.emp_num = #offline.emp_num , #online.day_date = #offline.day_date , #online.startdatetime <= #offline.startdatetime , #online.enddatetime between #offline.startdatetime , #offline.enddatetime --find online times offline range punched in middle select #online.rownum, #offline.rownum offlinerow, #offline.startdatetime, #offline.enddatetime, row_number() on (partition #online.rownum order #offline.rownum desc) offlineholenumber #offlineholes #online join #offline on #online.emp_num = #offline.emp_num , #online.day_date = #offline.day_date , #offline.startdatetime between #online.startdatetime , #online.enddatetime , #offline.enddatetime between #online.startdatetime , #online.enddatetime declare @holenumber integer select @holenumber = isnull(max(offlineholenumber),0) #offlineholes --punch holes out of online times while @holenumber > 0 begin insert #online select -1 rownum, #online.emp_num, #online.day_date, #offlineholes.enddatetime startdatetime, #online.enddatetime enddatetime #online join #offlineholes on #online.rownum = #offlineholes.rownum offlineholenumber = @holenumber update #online set #online.enddatetime = #offlineholes.startdatetime #online join #offlineholes on #online.rownum = #offlineholes.rownum offlineholenumber = @holenumber set @holenumber=@holenumber-1 end --output total hours select emp_num, day_date, sum(datediff(second,startdatetime, enddatetime)) / 3600.0 totalhr, sum(datediff(second,startdatetime, enddatetime)) / 60.0 totalmin #online group emp_num, day_date order 1, 2 --see how split online intervals select emp_num, day_date, startdatetime, enddatetime #online order 1, 2, 3, 4 output is:
emp_num day_date totalhr totalmin ----------- ----------------------- --------------------------------------- --------------------------------------- 547 2015-04-01 00:00:00.000 4.100000 246.000000 547 2015-04-02 00:00:00.000 0.816666 49.000000 548 2015-04-01 00:00:00.000 0.750000 45.000000 (3 row(s) affected) emp_num day_date startdatetime enddatetime ----------- ----------------------- ----------------------- ----------------------- 547 2015-04-01 00:00:00.000 2015-04-01 17:24:00.000 2015-04-01 21:30:00.000 547 2015-04-02 00:00:00.000 2015-04-02 13:11:00.000 2015-04-02 14:00:00.000 548 2015-04-01 00:00:00.000 2015-04-01 12:30:00.000 2015-04-01 13:15:00.000 (3 row(s) affected) i left other answer posted because it's more generic in case else wants snag it. see added bounty question. let me know if there's specific answer doesn't satisfy , i'll try out. process thousands of intervals method , returns in few seconds.