We help IT Professionals succeed at work.

Force sql rows

33 Views
Last Modified: 2020-05-18
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
Comment
Watch Question

Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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 CroweDatabase Engineer
CERTIFIED EXPERT
Top Expert 2005

Commented:
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]


CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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

Data Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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!
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.