Hi,
try this solution.
;WITH MonthPayCTE AS
(
SELECT 0 monthOrder, DateAdd(d, -DatePart(dy,getdate()-1),getdate()) as PayMonth
UNION ALL
SELECT MonthPayCTE.monthOrder+1 , DATEADD(m,1,MonthPayCTE.PayMonth) As PayMonth
FROM MonthPayCTE WHERE PayMonth < DateAdd(d, -DatePart(d,getdate()),
Dateadd(m,12-datepart(m,getdate()),getdate()))
)
SELECT A.Month,ISNULL(A.Pay,0) As Pay FROM (
SELECT ROW_NUMBER() OVER(Partition By MonthPayCTE.PayMonth,PayRoll.Pay
Order By MonthPayCTE.PayMonth) RowNum, DATENAME(month, MonthPayCTE.PayMonth) As Month,
Payroll.Pay FROM Payroll RIGHT OUTER JOIN MonthPayCTE
ON LTRIM(RTRIM(DATENAME(month,MonthPayCTE.PayMonth))) = LTRIM(RTRIM(Payroll.month))
) AS A WHERE A.RowNum < 2