Add Missing Date Range to Result Record Set

Question

I have a table containing the working hours for the company. We define the range of the dates and the number of hours for the range. The number of working hours which is not in the defined range is 9.5 hours.

I want to have the non-defined range with the value of 9.5 added to the result record set. how should I make the query to get this result?

The table definition and added records:

IF OBJECT_ID ('dbo.tbl_WorkingHours') IS NOT NULL
    DROP TABLE dbo.tbl_WorkingHours
GO

CREATE TABLE dbo.tbl_WorkingHours
    (
    Startdate    DATETIME NOT NULL,
    EndDate      DATETIME NOT NULL,
    HoursDefined FLOAT NULL,
    Description  VARCHAR (255) NULL,
    PRIMARY KEY (Startdate,EndDate)
    )

INSERT INTO dbo.tbl_WorkingHours
    (Startdate,EndDate,HoursDefined,Description)
VALUES 
    ('3/4/2010','3/29/2010',7,'')
INSERT INTO dbo.tbl_WorkingHours
    (Startdate,EndDate,HoursDefined,Description)
VALUES 
    ('5/4/2010','5/29/2010',8,'')

The Result of Select * :

Startdate  |  EndDate | HoursDefined  | Description
 ----------------------------------------------------
3/4/2010    3/29/2010   7   
5/4/2010    5/29/2010   8

My desired record set:

Startdate  |  EndDate | HoursDefined  | Description
----------------------------------------------------
1/1/1900    3/3/2010    9.5
3/4/2010    3/29/2010   7   
3/30/2010   5/3/2010    9.5
5/4/2010    5/29/2010   8
5/30/2010   1/1/2050    9.5

Answer 1

The below assumes it is not possible to have overlapping ranges or two defined ranges that are contiguous but held as separate rows.

WITH wh AS
(

SELECT     Startdate, EndDate, HoursDefined, Description,
       ROW_NUMBER() over (order by startdate) as rn
FROM         tbl_WorkingHours
) 
SELECT Startdate, EndDate, HoursDefined, Description 
FROM wh
UNION ALL
SELECT ISNULL(dateadd(day,1,w1.EndDate),'19000101'),
       ISNULL(dateadd(day,-1,w2.StartDate),'20500101') , 9.5 AS HoursDefined,''
FROM wh w1 FULL OUTER JOIN wh w2
ON w2.rn = w1.rn+1
ORDER BY Startdate  

Answer 2

try something like this:

DECLARE @tbl_WorkingHours table (Startdate    DATETIME NOT NULL
                                ,EndDate      DATETIME NOT NULL
                                ,HoursDefined FLOAT NULL
                                ,Description  VARCHAR (255) NULL
                                ,PRIMARY KEY (Startdate,EndDate)
                                )

INSERT INTO @tbl_WorkingHours (Startdate,EndDate,HoursDefined,Description) VALUES     ('3/4/2010','3/29/2010',7,'')
INSERT INTO @tbl_WorkingHours (Startdate,EndDate,HoursDefined,Description) VALUES     ('5/4/2010','5/29/2010',8,'')


;WITH OrderedRows AS
(   SELECT
        Startdate,EndDate,HoursDefined,Description, ROW_NUMBER() OVER (ORDER BY Startdate,EndDate) AS RowNumber
        FROM @tbl_WorkingHours
)
SELECT --before rows
    CONVERT(datetime,'1/1/1900') AS Startdate,ISNULL(MIN(Startdate),CONVERT(datetime,'1/2/2050'))-1 AS EndDate,9.5 AS HoursDefined, CONVERT(VARCHAR (255),'') AS Description
    FROM @tbl_WorkingHours
UNION ALL
SELECT --actual rows
    Startdate,EndDate,HoursDefined,Description
    FROM @tbl_WorkingHours
UNION ALL
SELECT --between rows
    a.EndDate+1,b.Startdate-1,9.5,''
    FROM OrderedRows            a
        INNER JOIN OrderedRows  b ON a.RowNumber=b.RowNumber-1
UNION ALL
SELECT --after rows
    ISNULL(MAX(EndDate),CONVERT(datetime,'1/1/2050')) AS Startdate,CONVERT(datetime,'1/2/2050')-1 AS EndDate,9.5 AS HoursDefined, CONVERT(VARCHAR (255),'') AS Description
    FROM @tbl_WorkingHours
    ORDER BY Startdate,EndDate

OUTPUT:

Startdate               EndDate                 HoursDefined           Description
----------------------- ----------------------- ---------------------- -----------
1900-01-01 00:00:00.000 2010-03-03 00:00:00.000 9.5                    
2010-03-04 00:00:00.000 2010-03-29 00:00:00.000 7                      
2010-03-30 00:00:00.000 2010-05-03 00:00:00.000 9.5                    
2010-05-04 00:00:00.000 2010-05-29 00:00:00.000 8                      
2010-05-29 00:00:00.000 2050-01-01 00:00:00.000 9.5                    

(5 row(s) affected)