SQL Problem ,Recursive CTE

 

WITH  integer_sequence(n) AS (

  SELECT 2019 -- starting value

  UNION ALL

  SELECT n+1 FROM integer_sequence WHERE n < 2021 -- ending value

)

Select customer_id,customer_name,AVG(Amount) from (

Select A.n as bill_Year,A.customer_id ,A.customer_name,ISNULL(Amount,0) AS Amount from (

SELECT * FROM integer_sequence A  cross join  (Select distinct customer_id,customer_name from Test_SQL)B

) A

left outer join  Test_SQL B on  A.n=DATEPART(YEAR,B.ddate) and A.customer_id=B.customer_id

 

) B

group by customer_id,customer_name