CODEWITHSUNDEEP
sqlsql-server-2008
user1654753
user1654753

Reputation: 21

SQL Query Comparing datetime overlaps and displaying them

I have directors that pay helpers based on if the helper worked or not when the director did.

Have 2 views as follows:-

Director

Name    TimeIn                      TimeOut

Bob     2012-07-17 07:00:00.000     2012-07-17 16:00:00.000
Sam     2012-07-17 10:00:00.000     2012-07-17 20:00:00.000
Beatly  2012-07-17 14:00:00.000     2012-07-17 23:00:00.000
Mac     2012-07-17 21:00:00.000     2012-07-18 07:00:00.000

Helper

Name    TimeIn                      TimeOut
Fred    2012-07-17 15:59:00.000     2012-07-18 00:19:00.000

Now figuring out who worked during that time isn't the hard part, or at least it isn't the part I am having an issue wrapping my brain around. What is is finding where and what mins and by who this overlap by the min. And group them together by the time used. Because they will all split the hourly pay with the scribe. So ideally the following would be the result.

DirName HelperStart                 HelperEnd
Bob     2012-07-17 15:59:00.000     2012-07-17 16:00:00.000
Sam     2012-07-17 15:59:00.000     2012-07-17 16:00:00.000
Beatly  2012-07-17 15:59:00.000     2012-07-17 16:00:00.000
Sam     2012-07-17 16:01:00.000     2012-07-17 20:00:00.000
Beatly  2012-07-17 16:01:00.000     2012-07-17 20:00:00.000
Beatly  2012-07-17 20:01:00.000     2012-07-17 21:00:00.000
Beatly  2012-07-17 21:01:00.000     2012-07-17 23:00:00.000
Mac     2012-07-17 21:01:00.000     2012-07-17 23:00:00.000
Mac     2012-07-17 23:01:00.000     2012-07-18 00:19:00.000

Using SQL server 2008. I hope the bare bones simple breakdown is enough. Found enough queries to see if someone conflicts with the time, but no breakdown of the above.

Upvotes: 1

Views: 439

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

If I understand correctly, this just a "simple" join, where you are using overlapping time intervals. The second part is measuring the period when the helper overlaps with the directors time. This period starts at the later of the director and helper TimeIn and ends at the earlier of their TimeOut.

I think the following query captures this logic:

select d.name as DirectorName, h.name as HelperName,
       (case when d.timeIn > h.timeIn then d.timeIn else h.timeIn end) as HelperStart,
       (case when d.timeOut > h.timeOut then h.timeOut else d.timeOut end) as HelperEnd
from director d join
     helper h
     on d.timeIn < h.timeOut and
        d.timeOut >= h.timeIn

Upvotes: 1

Related Questions