title: Find total time worked with multiple jobs / orders with overlap / overlapping times on each worker and job / order
body: <p>I searched night and day back when I was first starting out in the sql world for an answer to this question. Could not find anything similar to this for my needs so I decided to ask and answer my own question in case others need help like I did.</p>
  
  <p>Here is an example of the data I have. For simplicity, it is all from the Job table. Each JobID has it's own Start and End time that are basically random and can overlap, have gaps, start and end at the same time as other jobs etc.</p>
  
  <pre><code>--Available--
  JobID  WorkerID  JobStart             JobEnd
  1      25        '2012-11-17 16:00'  '2012-11-17 17:00'
  2      25        '2012-11-18 16:00'  '2012-11-18 16:50'
  3      25        '2012-11-19 18:00'  '2012-11-19 18:30'
  4      25        '2012-11-19 17:30'  '2012-11-19 18:10'
  5      26        '2012-11-18 16:00'  '2012-11-18 17:10'
  6      26        '2012-11-19 16:00'  '2012-11-19 16:50'
  </code></pre>
  
  <p>What I wanted the result of the query to show would be:</p>
  
  <pre><code>WorkerID  TotalTime(in Mins)
  25        170
  26        120
  </code></pre>
  
  <p>EDIT: Forgot to mention that the overlaps need to be ignored. Basically this is supposed to treat these workers and their jobs like you would an hourly employee and not a contractor. Like if I worked two jobIDs and started and finished them both from 12:00pm to 12:30pm, as an employee I would only get paid for 30 mins, whereas a contractor would likely get paid 60 mins, since their jobs are treated individually and get paid per job. The point of this query is to analyze jobs in a database that are tied to a worker, and need to find out if that worker was treated as an employee, what would his total hours worked in a given set of time come out to be.</p>
  
  <p>EDIT2: won't let me answer my own question for 7 hours, will move it there later.</p>
  
  <p>Ok, Answering Question now. Basically, I use temp table to build each minute between the min and max datetime of the jobs I am looking up.</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#time') IS NOT NULL
  BEGIN
  drop table #time
  END
  DECLARE @FromDate AS DATETIME,
       @ToDate AS DATETIME,
       @Current AS DATETIME
  SET @FromDate = '2012-11-17 16:00'
  SET @ToDate = '2012-11-19 18:30'
  
  create table #time  (cte_start_date datetime)
  set @current = @FromDate
  while (@current &lt; @ToDate)
  begin
  
  insert into #time (cte_start_date)
  values (@current)
  
  set @current = DATEADD(n, 1, @current)
  
  end
  </code></pre>
  
  <p>Now I have all the mins in a temp table. Now I need to join all the Job table info into it and select out what I need in one go.</p>
  
  <pre><code>SELECT J.WorkerID
  ,COUNT(DISTINCT t.cte_start_date) AS TotalTime
  FROM #time AS t
  INNER JOIN Job AS J ON t.cte_start_date &gt;= J.JobStart AND t.cte_start_date &lt; J.JobEnd --Thanks ErikE
  GROUP BY J.WorkerID --Thanks Martin Parkin
  
  drop table #time
  </code></pre>
  
  <p>That is the very simplified answer and is good to get someone started.</p>