how to compare time slots?

I have a table called pricelist

startHour
endHour
Price

and another table that containes the actualuse

startDate
endDate
jobID

My data is

pricelist

startHour | endHour | price
----------------------------
00:00     | 07:59   | 10
08:00     | 15:59   | 20
16:00     | 23:59   | 5

actualUse

startDate        | endDate         | jobId
-------------------------------------------
12/10/2014 08:30 | 12/10/2014 15:20| 1
12/10/2014 07:30 | 12/10/2014 18:20| 2
12/10/2014 07:30 | 13/10/2014 16:20| 3
12/10/2014 09:30 | 13/10/2014 00:20| 4

I am trying to get, for every job, all rows in pricelist that belong to it. For example for jobId 1 I will get

startDate        | endDate         | jobId  |price
---------------------------------------------------
12/10/2014 08:30 | 12/10/2014 15:20| 1      |10

for jobId 2

startDate        | endDate         | jobId  |price
---------------------------------------------------
12/10/2014 07:30 | 12/10/2014 07:59| 2      |10    
12/10/2014 08:00 | 12/10/2014 15:59| 2      |20
12/10/2014 16:00 | 12/10/2014 18:20| 2      |5

for jobId 3

startDate        | endDate         | jobId  |price
---------------------------------------------------
12/10/2014 07:30 | 12/10/2014 07:59| 3      |10
12/10/2014 08:00 | 12/10/2014 15:59| 3      |20
12/10/2014 16:00 | 12/10/2014 23:59| 3      |5
13/10/2014 00:00 | 13/10/2014 07:59| 3      |10
13/10/2014 08:00 | 13/10/2014 15:59| 3      |20
13/10/2014 16:00 | 13/10/2014 16:20| 3      |5

Interesting problem. I'd use a calendar table:

calendar

calendar_date
--------------
2014-01-01
2014-01-02
...
2014-12-01
2014-12-02
..
2014-12-31
2015-01-01
...

and restructure the pricelist to use closed-open intervals, instead of closed ones:

pricelist

startHour | endHour | price
----------------------------
00:00     | 08:00   | 10
08:00     | 16:00   | 20
16:00     | 24:00   | 5

It would not work exactly as it is above because TIME datatype cannot hold the value of 24:00:00 hours, so you probably have to use datetime. (In the linked example, I used datetime2(0) but I think it would work with datetime as well.

Then you could use this query to check for overlapping intervals between your pricelist (statDate, enddate) intervals and the ones calculated from the calendar and the pricelist values:

SELECT 
    startDate = CASE WHEN a.startDate < x.startD THEN x.startD ELSE a.startDate END,  -- max
    endDate = CASE WHEN a.endDate < x.endD THEN a.endDate ELSE x.endD END,            -- min
    a.jobID,
    p.price
FROM actualUse AS a
  CROSS JOIN calendar AS c
  CROSS JOIN pricelist AS p
  CROSS APPLY
    ( SELECT
          startD = DATEADD(day, DATEDIFF(day,'19000101',c.calendar_date), p.startHour),
          endD = DATEADD(day, DATEDIFF(day,'19000101',c.calendar_date), p.endHour)
     ) AS x 
WHERE a.startDate < x.endD
  AND x.startD < a.endDate ;

The complicated DATEADD(day, DATEDIFF(day, ...) expression is there for adding a DATE and a DATETIME value:

SQL-Fiddle is down today, but (thank you SO!) we can test at dba.se Data Explorer

StackExchange Database Administrators Q#82768, answer score: 4