Sql-server – Insert missing time interval rows

Having the date and time separate will allow you to do aggregates by time much easily. for eg: if you want to run a query to find what time period of the day is most busy. This is much easily performed using a separate time dimension.

Also, you should just have one timekey. Decide on either GMT/ EST time - then use this in the fact table. If you need to run reports based off the other timezone, just convert it in your application or query.

You need all combinations of Category and Value (a CROSS JOIN) except those that appear in the CategoryValue. This can be done with a LEFT JOIN / IS NULL

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v
  LEFT JOIN
    CategoryValue AS cv
      ON  cv.CategoryID = c.CategoryID
      AND cv.ValueID = v.ValueID
WHERE
   cv.CategoryID IS NULL ;

or a NOT EXISTS:

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v
WHERE
   NOT EXISTS
     ( SELECT *
       FROM CategoryValue AS cv
       WHERE cv.CategoryID = c.CategoryID
         AND cv.ValueID = v.ValueID 
     ) ;

or an EXCEPT solution:

SELECT 
    c.CategoryID, v.ValueID, v.ValueName
FROM 
    Category AS c
  CROSS JOIN
    Value AS v

EXCEPT

SELECT 
    cv.CategoryID, v.ValueID, v.ValueName
FROM 
    CategoryValue AS cv
  JOIN
    Value AS v
      ON  cv.ValueID = v.ValueID ;