<div>
First you do a cross join between a year table and customer table, then left join this to your CustomerOrderTable. That ensures you have a combination of year and customerID for all rows.
</div>

<div>
Here is what I came up with: 
 

<pre><code class="code-10 nolinks" id="code-20-43087040-1">select id, year, amount 
from (
select id, o.year,
 case when o.year=c.year then amount else 0 end amount,
 row_number() over(partition by id, o.year order by case when o.year=c.year then amount else 0 end desc) rn
from CustomerOrder c
	cross join OrderYear o
) x
where rn=1
order by id, year
;
</code></pre>
 
Example here: 
<a href="https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c632116aecdecfacb32e43db7c91652e" rel="ugc">https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c632116aecdecfacb32e43db7c91652e</a>
</div>

Senior Associate

Norie

<div>
You also don't really need the OrderYear table as long as you know the start and end years. &nbsp;You can use recursive CTEs to generate the list: 

<pre><code class="code-10 nolinks" id="code-20-43087045-1">with years(year) as
(
 select 2016
 union all
 select year+1 from years where year &lt; 2020
)
select id, year, amount 
from (
select id, o.year,
 case when o.year=c.year then amount else 0 end amount,
 row_number() over(partition by id, o.year order by case when o.year=c.year then amount else 0 end desc) rn
from CustomerOrder c
	cross join years o
) x
where rn=1
order by id, year
;
</code></pre>
</div>

<div>
<pre><code class="code-10 nolinks" id="code-20-43087047-1">DECLARE @CustomerOrder TABLE
(
&nbsp; &nbsp;ID&nbsp; &nbsp; &nbsp;&nbsp;INT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;NOT NULL,
&nbsp; &nbsp;[Year]&nbsp; &nbsp;INT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;NOT NULL,
&nbsp; &nbsp;Amount&nbsp; &nbsp;DECIMAL(9,2)&nbsp; &nbsp;NOT NULL DEFAULT (0)
);

DECLARE @OrderYear TABLE
(
&nbsp; &nbsp;[Year]&nbsp; &nbsp;INT&nbsp; &nbsp; &nbsp;&nbsp;NOT NULL
);

INSERT @CustomerOrder (ID, [Year], Amount)
VALUES
&nbsp; &nbsp;(1, 2017, 200.0),
&nbsp; &nbsp;(1, 2019, 300.0),
&nbsp; &nbsp;(2, 2010, 500.0);

INSERT @OrderYear ([Year])
VALUES (2016),
&nbsp; &nbsp;(2017),
&nbsp; &nbsp;(2018),
&nbsp; &nbsp;(2019),
&nbsp; &nbsp;(2020);

SELECT *
FROM @CustomerOrder;

SELECT *
FROM @OrderYear;

WITH cteCustomerYearlyRevenue AS
(
&nbsp; &nbsp;SELECT O.ID, O.[Year], SUM(O.Amount) AS Amount
&nbsp; &nbsp;FROM @CustomerOrder AS O
&nbsp; &nbsp;GROUP BY O.ID, O.[Year]
)
SELECT C.ID,
&nbsp; &nbsp;Y.[Year],
&nbsp; &nbsp;COALESCE(CYR.Amount, 0) AS Amount
FROM @OrderYear AS Y
CROSS JOIN
(
&nbsp; &nbsp;SELECT DISTINCT ID
&nbsp; &nbsp;FROM cteCustomerYearlyRevenue
) AS C
LEFT OUTER JOIN cteCustomerYearlyRevenue AS CYR
&nbsp; &nbsp;ON C.ID = CYR.ID
&nbsp; &nbsp;AND &nbsp;Y.[Year] = CYR.[Year]
ORDER BY C.ID, Y.[Year]


</code></pre>
</div>

<div>
Another version using recursive CTE's without the cross join: 

<pre><code class="code-10 nolinks" id="code-20-43087049-1">with all_years(id, cte_year, amount) as
(
 select distinct id, 2016 cte_year, 0 amount from CustomerOrder
 union all
 select id, cte_year+1, 0 from all_years where cte_year &lt; 2020
)
select y.id, cte_year year, isnull(y.amount + c.amount,0) amount
from CustomerOrder c
 right join all_years y on c.id=y.id and c.year=y.cte_year
order by y.id,cte_year;
</code></pre>
</div>

Senior DBA (Microsoft MVP)

Scott Pletcher

johnnyg123

<div>
Thanks for all the responses ! All responses gave the desired output. &nbsp;As usual, more than one way to get to desired result. I chose Sharath's answer because it seemed short and sweet but definitely wanted to give credit to all that took time to respond as it opened my eyes to other approaches that may be useful in the future. Thanks for all your responses <a href="https://www.experts-exchange.com/members/slightwv.html" rel="ugc">slightwv.</a>&nbsp; &nbsp;I did want the orderyear table because the number of years could change and didn't want to hard code years. &nbsp;I appreciate you providing responses that did take into account orderyear table Thanks Again Everyone!
</div>

<div>
&gt;&gt;I chose Sharath's answer because it seemed short and sweet 
 
I should point out that it does an implicit cross join. &nbsp;You should get in the habit of explicitly performing joins, data type conversions, etc... 
 
That will make the code easier to read later or by someone else looking at it.
</div>

<div>
<div class="content wysiwyg-content">
Here is some data from 2 sql 2014 tables 
 
CustomerOrder 
 
id &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;year &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; Amount 
1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2017 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;200 
1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2019 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;300 
2 &nbsp; &nbsp; &nbsp;2010 &nbsp; &nbsp; &nbsp; 500 
 
OrderYear 
 
Year 
2016 
2017 
2018 
2019 
2020 
 
Trying to write a query that will return how much customer order from 2016 to 2020 
 
If customer didn't order anything in a given year, would like to have a row returned with 0 amount 
 
Given the sample data 
 
Would like the query results to be 
 
ID &nbsp;Year &nbsp; &nbsp; &nbsp; Amount 
1 &nbsp; &nbsp; 2016 &nbsp; &nbsp; 0 
1 &nbsp; &nbsp; 2017 &nbsp; &nbsp;200 
1 &nbsp; &nbsp; 2018 &nbsp; &nbsp; 0 
1 &nbsp; &nbsp; 2019 &nbsp; &nbsp; 300 
1 &nbsp; &nbsp; 2020 &nbsp; &nbsp; 0 
2 &nbsp; &nbsp; 2016 &nbsp; &nbsp; 0 
2 &nbsp; &nbsp; 2017 &nbsp; &nbsp; 0 
2 &nbsp; &nbsp; 2018 &nbsp; &nbsp; 0 
2 &nbsp; &nbsp; 2019 &nbsp; &nbsp; 0 
2 &nbsp; &nbsp; 2020 &nbsp; &nbsp; 0
</div>
</div>

Here is some data from 2 sql 2014 tables

CustomerOrder

id	year	        Amount
1	2017	200
1	2019	300
2      2010       500

OrderYear

Year
2016
2017
2018
2019
2020

Trying to write a query that will return how much customer order from 2016 to 2020

If customer didn't order anything in a given year, would like to have a row returned with 0 amount

Given the sample data

Would like the query results to be

ID  Year       Amount
1     2016     0
1     2017    200
1     2018     0
1     2019     300
1     2020     0
2     2016     0
2     2017     0
2     2018     0
2     2019     0
2     2020     0

Force sql rows

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.