How to select last day of the month SQL if the are no entries for that month

Perhaps you might be better off with generating dates for the month start and end dates, and then joining from that to your table. Then you can use an ISNULL on the [date] column from your table and return the upper boundary date instead:

CREATE TABLE dbo.YourTable (ID int,
                            [date] date,
                            Frequency int);
GO

INSERT INTO dbo.YourTable
VALUES(1,'20140518',5),
      (1,'20140619',4),
      (1,'20140720',25),
      (2,'20140520',7),
      (3,'20140518',4),
      (3,'20140620',1),
      (4,'20140518',6);
GO

DECLARE @DateStart date = '20140518',
        @DateEnd date = '20140720';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (DATEDIFF(MONTH,@DateStart,@DateEnd)+1) --Limits to the number of months you need
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2), --Max 100 months, if you need more month add more cross joins
Months AS(
    SELECT DATEADD(DAY,1,EOMONTH(DATEADD(MONTH,T.I,@DateStart),-1)) AS MonthStart,
           EOMONTH(DATEADD(MONTH,T.I,@DateStart)) AS MonthEnd
    FROM Tally T)
SELECT I.ID,
       ISNULL(YT.[date],M.MonthEnd) AS [Date],
       ISNULL(YT.Frequency,0) AS Frequency
FROM Months M
     CROSS APPLY (SELECT DISTINCT ID FROM dbo.YourTable) I
     LEFT JOIN dbo.YourTable YT ON I.ID = YT.ID
                               AND YT.[date] BETWEEN M.MonthStart AND M.MonthEnd
                               AND YT.[date] BETWEEN @DateStart AND @DateEnd
ORDER BY ID,
         [Date];

GO
DROP TABLE dbo.YourTable;

Note I also switch to a Tally here, instead of a rCTE, as (as the name suggests) an rCTE is recursive where as a Tally is not; thus for large data sets will be (far) more performant.

This is an interesting variation on the "filling in the blanks" type of query. The difference is that you want to keep the original rows if they match, but create a new one for months that do not.

You can approach this by:

• Generating a range with one row per month.
• Cross joining to generate a row for each id and each month.
• Joining back to the original data to get any rows that already exist.

This looks like:

WITH months AS (
      SELECT DATEFROMPARTS(YEAR('20140518'), MONTH('20140518'), 1) as mon
      UNION ALL
      SELECT DATEADD(MONTH, 1, mon)
      FROM months
      WHERE mon < DATEFROMPARTS(YEAR('20140720'), MONTH('20140720'), 1)
    )
SELECT i.id,
       COALESCE(t.date, EOMONTH(m.mon)),
       COALESCE(t.frequency, 0)
FROM (SELECT DISTINCT ID FROM dbo.MyTable) i CROSS JOIN
     months m LEFT JOIN
     dbo.MyTable t
     ON t.id = i.id AND
        t.date >= m.mon AND
        t.date < DATEADD(month, 1, m.mon);

Note that you can generate the dates you want directly in a recursive CTE. There is no need to generate numbers first.

You've done some good work. Now just filter out the non-last days, probably in your range cte:

...
range AS
    (
        SELECT [date] = q.date
        FROM n
        cross apply( select DATEADD(DAY, n,'20140518') as date) as q
        where datepart(day,dateadd(day,1,q.date))=1
    ),
...

This just takes the [date] you calculated, and makes sure the next day is the first of month.