Repeating rows from right join

Question

My application saves logs that need to be taken at least one time during each of the 3 different time periods during the day. So ideally 3 logs per day, each with a unique time period ID. I need to write an exception report (MSSQL 2008) that will show when a time period is missed for any given day. I have a LogTimePeriods table that contains 3 rows for each of the time periods. The Logs table contains the LogTimePeriodID so I do not need to do any logic to see what Time period the log belongs in (that is done via the application).

I know I need something along the lines of a right/left join to try to match all the LogTimePeriodID for every Log row for a given date. I cant seem to make any progress. Any help is appreciated! Thanks for reading.

SQL Fiddle

EDIT: Desired output below

Date | LogPeriodID
6/3 | 3
6/5 | 2
6/5 | 3

Answer 1

Your SQL Fiddle is set to use MYSQL, not SQL Server 2008, so I can't test my answer against your data: however, based on my understanding of your requirements and assuming you are querying a SQL 2008 database, the following example should work for you (the references to my table variables would obviously be replaced with your actual tables).

DECLARE @StartDate DATE = '06/04/2014'
DECLARE @EndDate DATE = GETDATE();
DECLARE @LogTimePeriod TABLE (LogTimePeriodID INT IDENTITY(1,1), TimePeriod VARCHAR(20))
INSERT INTO @LogTImePeriod (TimePeriod) SELECT '00:00 - 07:59'
INSERT INTO @LogTImePeriod (TimePeriod) SELECT '08:00 - 15:59'
INSERT INTO @LogTImePeriod (TimePeriod) SELECT '16:00 - 23:59'


DECLARE @logs TABLE (LogDataID INT IDENTITY(1,1), LogDate DATE, SomeInformation VARCHAR(10), LogTimePeriodID INT)
INSERT INTO @logs (SomeInformation, LogDate, LogTimePeriodID) SELECT 'abc', '6/4/2014', 1
INSERT INTO @logs (SomeInformation, LogDate, LogTimePeriodID) SELECT 'def', '6/4/2014', 2
INSERT INTO @logs (SomeInformation, LogDate, LogTimePeriodID) SELECT 'ghi', '6/4/2014', 3
INSERT INTO @logs (SomeInformation, LogDate, LogTimePeriodID) SELECT 'abc', '6/5/2014', 1
INSERT INTO @logs (SomeInformation, LogDate, LogTimePeriodID) SELECT 'def', '6/5/2014', 2;


WITH dates AS (
     SELECT CAST(@StartDate AS DATETIME) 'date'
     UNION ALL
     SELECT DATEADD(dd, 1, t.date) 
       FROM dates t
      WHERE DATEADD(dd, 1, t.date) <= @EndDate)

SELECT ltp.LogTimePeriodID, ltp.TimePeriod, dates.date
FROM 
    @LogTimePeriod ltp
     INNER JOIN 
    dates ON 1=1
     LEFT JOIN 
    @logs ld ON 
        ltp.LogTimePeriodID = ld.LogTimePeriodID AND
        dates.date = ld.LogDate
WHERE ld.LogDataID IS NULL
 OPTION (MAXRECURSION 1000) -- 0 is unlimited, 1000 limits to 1000 rows