this?;with CTE( SELECT i.*,StartMonthNo AS MonthNo,EndMonthNo FROM TBL_ID i INNER JOIN (SELECT ID ,MIN(MONTH([DATE])) AS StartMonthNo,MAX(MONTH([DATE])) AS EndMonthNo FROM TABLE_TRN GROUP BY ID)m ON m.ID = i.ID UNION ALL SELECT i.*,MonthNo+ 1,EndMonthNo FROM CTE WHERE MonthNo + 1 <= EndMonthNo)SELECT c.*FROM CTE cLEFT JOIN TABLE_TRN tON t.id = c.id AND MONTH(t.[DATE]) = c.MonthNoWHERE t.id IS NULL
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/