logo
down
shadow

Find total duration of many overlapping times


Find total duration of many overlapping times

By : Tamer
Date : October 17 2020, 01:08 AM
around this issue I have a list of dates and times for employee time sheets. The times begin in column F, and end in column G. Sometimes there are overlapping times for projects. The employee does not get paid for overlapping projects, yet we need to track each project separately. I would like to be able to look at columns E, F and G and find any overlapping projects, and return a single time entry. In the example below, notice that line 1 does NOT overlap with the others, but that there is a series of overlapping entries in lines 2-6. They don't necessarily all overlap, but are more like a "chain." I want to write a formula (not a script) to solve this. , My Solution
To solve this I need 2 extra columns:
code :
ok
overlap
overlap
overlap
overlap
ok
ok
overlap
overlap
overlap
overlap
ok
1
2
2
2
2
2
3
4
4
4
4
4


Share : facebook icon twitter icon
Find total time worked with multiple jobs / orders with overlap / overlapping times on each worker and job / order

Find total time worked with multiple jobs / orders with overlap / overlapping times on each worker and job / order


By : Majordomo
Date : March 29 2020, 07:55 AM
this will help This query does the job as well. Its performance is very good (while the execution plan looks not so great, the actual CPU and IO beat many other queries).
See it working in a Sql Fiddle.
code :
WITH Times AS (
   SELECT DISTINCT
      H.WorkerID,
      T.Boundary
   FROM
      dbo.JobHistory H
      CROSS APPLY (VALUES (H.JobStart), (H.JobEnd)) T (Boundary)
), Groups AS (
   SELECT
      WorkerID,
      T.Boundary,
      Grp = Row_Number() OVER (PARTITION BY T.WorkerID ORDER BY T.Boundary) / 2
   FROM
      Times T
      CROSS JOIN (VALUES (1), (1)) X (Dup)
), Boundaries AS (
   SELECT
      G.WorkerID,
      TimeStart = Min(Boundary),
      TimeEnd = Max(Boundary)
   FROM
      Groups G
   GROUP BY
      G.WorkerID,
      G.Grp
   HAVING
      Count(*) = 2
)
SELECT
   B.WorkerID,
   WorkedMinutes = Sum(DateDiff(minute, 0, B.TimeEnd - B.TimeStart))
FROM
   Boundaries B
WHERE
   EXISTS (
      SELECT *
      FROM dbo.JobHistory H
      WHERE
         B.WorkerID = H.WorkerID
         AND B.TimeStart < H.JobEnd
         AND B.TimeEnd > H.JobStart
   )
GROUP BY
   WorkerID
;
Author  CPU  Elapsed  Reads   Scans
------  ---  -------  ------  -----
  Erik  157    166      122       2
Gordon  375    378    106964  53251
Query     CPU   Duration  Reads   Notes
--------  ----  --------  ------  ----------------------------------
Erik 1    215   231       122     query as above
Erik 2    326   379       116     alternate technique with no EXISTS
Gordon 1  578   682       106847  from j
Gordon 2  584   673       106847  from dbo.JobHistory
INSERT dbo.JobHistory
SELECT
   H.JobID + A.MaxJobID,
   H.WorkerID + A.WorkerCount,
   DateAdd(minute, Elapsed + 45, JobStart),
   DateAdd(minute, Elapsed + 45, JobEnd)
FROM
   dbo.JobHistory H
   CROSS JOIN (
      SELECT
         MaxJobID = Max(JobID),
         WorkerCount = Max(WorkerID) - Min(WorkerID) + 1,
         Elapsed = DateDiff(minute, Min(JobStart), Min(JobEnd))
      FROM dbo.JobHistory
   ) A
;
Difference between total session duration and total page visit duration

Difference between total session duration and total page visit duration


By : Krezmasters
Date : March 29 2020, 07:55 AM
Any of those help Avg Time / Session is calculated based on the times of lastPageview - firstPageview per the google documentation:
https://support.google.com/analytics/answer/1006253?hl=en https://support.google.com/analytics/answer/2731565?hl=en
How can I calculate the total duration between two date/times with MySQL

How can I calculate the total duration between two date/times with MySQL


By : user2118676
Date : March 29 2020, 07:55 AM
This might help you I have a table named it_outage that looks like this: , Try this mySQL with your Joomla db object:
code :
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select("office,`start`,`end`,SEC_TO_TIME( SUM((UNIX_TIMESTAMP(`end`) - UNIX_TIMESTAMP(`start`))) ) AS total_outage");
$query->from($db->quoteName('#__outage'));
$query->group('office');
$db->setQuery($query);
foreach($result= $db->loadObjectList() as $outage) {
    echo $outage->office . "  :  " . $outage->total_outage . '<Br/>';
}
ewi : 00:30:00
nyc : 01:40:00
How to calculate elapsed times for the total duration of events?

How to calculate elapsed times for the total duration of events?


By : Andrew
Date : March 29 2020, 07:55 AM
it helps some times I have collected a dataframe that models the duration of time for events in a group problem solving session in which the members Communicate (Discourse Code) and construct models (Modeling Code). Each minute that that occurs is captured in the Time_Processed column. Technically these events occur simultaneously. I would like to know how long the students are constructing each type of model which is the total duration of that model or the time elapsed before that model changes. , UPDATED SOLUTION
code :
df %>% 
  mutate(dur = lead(Time_Processed) - Time_Processed) %>% 
  replace_na(list(dur = 0)) %>% 
  group_by(`Modeling Code`) %>% 
  summarise(tot_time = sum(dur))
df <- df %>%
  rename(m_code = `Modeling Code`,
         d_code = `Discourse Code`)
df <- df %>%
  mutate(lead_time_proc = lead(Time_Processed),
         row_n = row_number(),
         mcode_grp = "") 
mcode_ct <- df %>% 
  group_by(m_code) %>% 
  summarise(ct = 0) %>%
  mutate(m_code = as.character(m_code))
mc <- ""
for (i in 1:nrow(df)) {
  current_mc <- df$m_code[i]
  if (current_mc != mc) {
    mc <- current_mc
    mcode_ct <- mcode_ct %>% mutate(ct = ifelse(m_code == mc, ct + 1, ct))
    current_grp <- mcode_ct %>% filter(m_code == mc) %>% select(ct) %>% pull()
  }
  df <- df %>% mutate(mcode_grp = ifelse(row_n == i, current_grp, mcode_grp))
}
 df %>%
   group_by(m_code, mcode_grp) %>%
   summarise(start_time = min(Time_Processed),
             end_time = max(lead_time_proc)) %>%
   mutate(total_time = end_time - start_time) %>%
   group_by(m_code) %>%
   summarise(total_time = sum(total_time)) %>%
   replace_na(list(total_time=0))
# A tibble: 4 x 2
  m_code total_time
  <fct>       <dbl>
1 MA            12.
2 OFF            1.
3 P              0.
4 V              5.
SQL Server : calculating total duration of overlapping timespans

SQL Server : calculating total duration of overlapping timespans


By : Sonia Carré
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I have a dataset which is essentially a set of 'group of tasks' with their start and end times, as follows , Here's another option using self join
code :
declare @t table ([Group] varchar(10), Name varchar(10), StartTime datetime, EndTime datetime)
insert into @t
values 
('GroupA', 'Task1', '20180101 09:00', '20180101 11:00')
, ('GroupA', 'Task2', '20180101 10:00', '20180101 12:00')
, ('GroupA', 'Task3', '20180101 10:00', '20180101 13:00')
, ('GroupA', 'Task4', '20180101 20:00', '20180101 22:00')

select
    [Group], StartTime = min(StartTime), EndTime
from (
    select
        a.[Group], a.StartTime, EndTime = max(b.EndTime)
    from 
        @t a
        left join @t b on a.[Group] = b.[Group] and a.StartTime < b.EndTime and a.EndTime > b.StartTime
    group by a.[Group], a.StartTime
) t
group by [Group], EndTime
shadow
Privacy Policy - Terms - Contact Us © soohba.com