SQL - Create row for filling in NULL into missing dates but repeating ID Column Value

Question

I have the following tables. One haves the TimeRegister for each Employee (each row equals a IN and a OUT in asc sequence):

Employee         TimeRegister
   15        2014-04-01 11:51:43.000
   15        2014-04-01 14:03:52.000
   15        2014-04-01 14:17:01.000
   15        2014-04-01 16:01:12.000
   15        2014-04-03 09:48:33.000
   15        2014-04-03 12:13:43.000

The other table haves all dates:

          Date
2014-04-01 00:00:00.000
2014-04-02 00:00:00.000
2014-04-03 00:00:00.000
2014-04-04 00:00:00.000  

As you can notice on first table there's no record for employee 15 for 2014-04-02. But I wanted that date with a NULL Time Register so it appears like this:

Employee         TimeRegister
   15        2014-04-01 11:51:43.000
   15        2014-04-01 14:03:52.000
   15        2014-04-01 14:17:01.000
   15        2014-04-01 16:01:12.000
   15                NULL
   15        2014-04-03 09:48:33.000
   15        2014-04-03 12:13:43.000  

I don't want to INSERT into the table itself but rather get this with a VIEW.

All the help will be greatly appreciated. Thanks in advance!

Answer 1

I've used a recursive cte to generate all dates for a range, but if as you say you have another table with all dates (or all relevant / working dates), use that instead of the cteDateGen. Similarly, I've scraped the unique employees from the same table as TimeRegister - again you may have another table of Employee. Once you have a list of all dates and employees, you can Left Outer join to the table with TimeRegisters to ensure at least one row per Date / Employee combination, even if there is no such row in the table:

With cteDateGen AS
(
  SELECT 0 as Offset, CAST(DATEADD(dd, 0, '2014-04-01') AS DATE) AS WorkDate
  UNION ALL 
  SELECT Offset + 1, CAST(DATEADD(dd, Offset, '2014-04-01') AS DATE)
  FROM cteDateGen
  WHERE Offset < 1000
),
cteAllEmployees AS
(
  SELECT DISTINCT(Employee) AS Employee
  FROM TimeRegister
)
SELECT d.WorkDate,
       e.Employee, t.TimeRegister
  FROM cteDateGen d
    CROSS JOIN cteAllEmployees e
    LEFT JOIN TimeRegister t
      ON e.Employee = t.Employee AND CAST(t.TimeRegister AS DATE) = d.WorkDate
  WHERE d.WorkDate BETWEEN '2014-04-01' AND '2014-04-10'
  OPTION(MAXRECURSION 0);    

I'm assuming the same table structure as in the fiddle here