SELECT
dbo.CST_MS_Quote.MSCo,
dbo.CST_MS_Quote.JCCo,
dbo.CST_MS_Quote.Quote,
dbo.CST_MS_Quote.MSHQStatus,
dbo.CST_MS_Quote.QuoteType,
dbo.CST_MS_Quote.Job,
dbo.CST_MS_Quote.Status,
dbo.CST_MS_Quote.Description,
dbo.CST_MS_Quote.QuotedBy,
dbo.RJDateLookup.MonthNumber,
dbo.RJDateLookup.MonthName,
dbo.RJDateLookup.DateFull,
dbo.CST_MS_Quote.QuoteDate,
dbo.CST_MS_Quote.ReqDate,
dbo.CST_MS_Quote.ExpDate,
dbo.CST_MS_Quote.FromLoc,
dbo.CST_MS_Quote.Location2 AS Location,
dbo.CST_MS_Quote.MaterialCode,
dbo.CST_MS_Quote.MaterialDescription,
dbo.CST_MS_Quote.UM,
dbo.CST_MS_Quote.QuoteUnits

FROM dbo.RJDateLookup
Left JOIN dbo.CST_MS_Quote
ON dbo.RJDateLookup.DateFull = dbo.CST_MS_Quote.QuoteDate
and dbo.CST_MS_Quote.QuoteType = 'C'
and dbo.RJDateLookup.MonthNumber <= month(QuoteDate) + 6
WHERE
dbo.RJDateLookup.DateFull >= '08/01/2012'
AND
Quote = 'AC1586'

All I did was add the quote clause so I could see if it works in one instance.

All it did was return the one record and no dates from the datelookup past August.

I was expecting it to return the quote record and the next 6 months from the datelookup table for a total of 7 records.

Well it looks like the Datelookup table is missing all dates for Sept. Oct. Nov, Dec, Jan and Feb for each year. Makes no sense. Here is the SQL used to populate the table.

DECLARE @Date DATETIME
SET @Date = '1/1/1900'     

WHILE @Date < '1/1/2100'
BEGIN
     INSERT INTO RJDateLookup
     (
        DateKey, DateFull, FullYear,
        QuarterNumber, WeekNumber, WeekDayName,
        MonthDay, MonthName, YearDay,
        DateDefinition,
               CharacterDate,
               WeekDay,
               MonthNumber
     )
     SELECT
        CONVERT(VARCHAR(8), @Date, 112), @Date, YEAR(@Date),
        DATEPART(qq, @Date), DATEPART(ww, @Date), DATENAME(dw, @Date),
        DATEPART(dd, @Date), DATENAME(mm, @Date), DATEPART(dy,@Date),
               DATENAME(mm, @Date) + ' ' + CAST(DATEPART(dd, @Date) AS CHAR(2)) + ',   
           ' + CAST(DATEPART(yy, @Date) AS CHAR(4)),
           CONVERT(VARCHAR(10), @Date, 101),
           DATEPART(dw, @Date),
           DATEPART(mm, @Date)
    
     SET @Date = DATEADD(dd, 1, @Date)
END

Add the quote number to the join instead of the where.

You have two mutually exclusive conditions in your join:

But this