asked on

Force sql rows

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

Anders Ebro (Microsoft MVP)

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.

slightwv (䄆 Netminder)

Here is what I came up with:

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
;

Open in new window

Example here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c632116aecdecfacb32e43db7c91652e

slightwv (䄆 Netminder)

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

with years(year) as
(
    select 2016
    union all
    select year+1 from years where year < 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
;

Open in new window

Brian Crowe

DECLARE @CustomerOrder TABLE
(
   ID      INT            NOT NULL,
   [Year]   INT            NOT NULL,
   Amount   DECIMAL(9,2)   NOT NULL DEFAULT (0)
);

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

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

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

SELECT *
FROM @CustomerOrder;

SELECT *
FROM @OrderYear;

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

Open in new window

slightwv (䄆 Netminder)

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

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 < 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;

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johnnyg123

ASKER

Thanks for all the responses !

All responses gave the desired output.

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 slightwv. I did want the orderyear table because the number of years could change
and didn't want to hard code years. I appreciate you providing responses that did take into account orderyear table

Thanks Again Everyone!

slightwv (䄆 Netminder)

>>I chose Sharath's answer because it seemed short and sweet

I should point out that it does an implicit cross join. 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.