How to compare time slot in SQL Server?

Question

I have a table [pricelist]

startHour
endHour
Price

and another table that contains the [actualuse]

startDate
endDate
user

My data

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 try to get for every gob all rows in pricelist the are belong to. For example for jobId 1 I will get

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

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

Answer 1

Here is a possible working solution. I know the cursor is a bit ugly but I had to create another table with every possibles dates between the date ranges. You might be able to refactor some date computation too (I assumed you were working with TIME datatype)

/*
create table pricelist
(
    startHour time,
    endHour time,
    price decimal(18,2)
)

create table actualuse
(
    startDate datetime,
    endDate datetime,
    jobId int
)

insert pricelist values
('00:00','07:59',10),
('08:00','15:59',20),
('16:00','23:59',5)

set dateformat dmy
insert actualuse values
('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)

*/

BEGIN TRY DROP TABLE #actualUseDays END TRY
BEGIN CATCH END CATCH

CREATE TABLE #actualUseDays (
    startDate DATETIME
    ,endDate DATETIME
    ,jobId INT
    )

DECLARE @startDate DATETIME
    ,@endDate DATETIME
    ,@jobId INT;

DECLARE cur CURSOR FORWARD_ONLY FOR SELECT * FROM actualuse

OPEN cur;

FETCH NEXT FROM cur INTO @startDate ,@endDate ,@jobId

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT #actualUseDays
    SELECT @startDate
        ,iif(CAST(@endDate AS DATE) <> CAST(@startDate AS DATE), DATEADD(day, DATEDIFF(day, '19000101', cast(@startDate AS DATE)), CAST(CAST('23:59:59' AS TIME) AS 
                    DATETIME2(7))), @endDate)
        ,@jobId

    UNION

    SELECT CAST(DATEADD(DAY, number + 1, @startDate) AS DATE) [Date]
        ,iif(CAST(@endDate AS DATE) <> CAST(DATEADD(DAY, number + 1, @startDate) AS DATE), DATEADD(day, DATEDIFF(day, '19000101', CAST(DATEADD(DAY, number + 1, 
                            @startDate) AS DATE)), CAST(CAST('23:59:59' AS TIME) AS DATETIME2(7))), @endDate)
        ,@jobId
    FROM master..spt_values
    WHERE type = 'P'
        AND DATEADD(DAY, number + 1, CAST(@startDate AS DATE)) < @endDate

    FETCH NEXT FROM cur INTO @startDate ,@endDate ,@jobId
END

CLOSE cur;
DEALLOCATE cur;

/*
#actualUseDays now contains : 

startDate               endDate                 jobId
----------------------- ----------------------- -----------
2014-10-12 08:30:00.000 2014-10-12 15:20:00.000 1
2014-10-12 07:30:00.000 2014-10-12 18:20:00.000 2
2014-10-12 07:30:00.000 2014-10-12 23:59:59.000 3
2014-10-13 00:00:00.000 2014-10-13 16:20:00.000 3
2014-10-12 09:30:00.000 2014-10-12 23:59:59.000 4
2014-10-13 00:00:00.000 2014-10-13 00:20:00.000 4
*/

SELECT iif(CAST(a.startDate AS TIME) > p.startHour, startDate, DATEADD(day, DATEDIFF(day, '19000101', CAST(startDate AS DATE)), CAST(startHour AS DATETIME2(7)))) AS 
    startDate
    ,iif(CAST(a.endDate AS TIME) < p.endHour, endDate, DATEADD(day, DATEDIFF(day, '19000101', CAST(endDate AS DATE)), CAST(endHour AS DATETIME2(7)))) AS endDate
    ,jobId
    ,price
FROM #actualUseDays a
INNER JOIN pricelist p
    ON CAST(a.startDate AS TIME) <= p.endHour
        AND CAST(a.endDate AS TIME) >= p.startHour
ORDER BY jobId
    ,iif(CAST(a.startDate AS TIME) > p.startHour, startDate, DATEADD(day, DATEDIFF(day, '19000101', CAST(startDate AS DATE)), CAST(startHour AS DATETIME2(7))))

Results :

startDate                   endDate                     jobId       price
--------------------------- --------------------------- ----------- ---------------------------------------
2014-10-12 08:30:00.0000000 2014-10-12 15:20:00.0000000 1           20.00
2014-10-12 07:30:00.0000000 2014-10-12 07:59:00.0000000 2           10.00
2014-10-12 08:00:00.0000000 2014-10-12 15:59:00.0000000 2           20.00
2014-10-12 16:00:00.0000000 2014-10-12 18:20:00.0000000 2           5.00
2014-10-12 07:30:00.0000000 2014-10-12 07:59:00.0000000 3           10.00
2014-10-12 08:00:00.0000000 2014-10-12 15:59:00.0000000 3           20.00
2014-10-12 16:00:00.0000000 2014-10-12 23:59:00.0000000 3           5.00
2014-10-13 00:00:00.0000000 2014-10-13 07:59:00.0000000 3           10.00
2014-10-13 08:00:00.0000000 2014-10-13 15:59:00.0000000 3           20.00
2014-10-13 16:00:00.0000000 2014-10-13 16:20:00.0000000 3           5.00
2014-10-12 09:30:00.0000000 2014-10-12 15:59:00.0000000 4           20.00
2014-10-12 16:00:00.0000000 2014-10-12 23:59:00.0000000 4           5.00
2014-10-13 00:00:00.0000000 2014-10-13 00:20:00.0000000 4           10.00