| Product ID | YearBought | Sales | Min_Year | Max_Year |
| 1 | 2016 | $20 | 2011 | 2016 |
| 2 | 2016 | $10 | 2016 | 2018 |
| 2 | 2017 | $30 | 2016 | 2018 |
| 3 | 2017 | $5 | 2015 | 2019 |
| 3 | 2018 | $10 | 2015 | 2019 |
| 3 | 2018 | $20 | 2015 | 2019 |
| 3 | 2019 | $5 | 2015 | 2019 |
| 3 | 2019 | $30 | 2015 | 2019 |
| 4 | 2018 | $5 | 2018 | 2020 |
| 4 | 2019 | $10 | 2018 | 2020 |
| 4 | 2020 | $20 | 2018 | 2020 |
Min_Year = the year the product was first introduced
Max_Year + 1 = Product drop off year
Above is a sample of the table I'm working with. Trying to find:
the sum of sales new products brought in the year they were first introduced
the sum of "dropped sales" aka the sum of sales from products the year after they dropped off (had no sales). (Ex. Product brought in $15 in 2018 but had no sales in 2019, want to show $15 as dropped sales in 2019)
Expected Output:
| YearBought | New Product Sales | Dropped Product Sales |
| 2016 | $10 | |
| 2017 | | $20 |
| 2018 | $5 | |
| 2019 | | |
| 2020 | | $35 |
was thinking something like this but it's not working. any help would be appreciated!
select
YearBought,
sum(case when yearbought=min_year then sales else 0 end) as NewSales,
sum(case when yearbought=max_year+1 then sales else 0 end) as DropSales
from
#t
group by
yearbought
Aggregate the dropped product sales separately from the new product sales, then join the aggregations. You can do this with subqueries, or, as I have done below, with common table expressions.
with
droppedProds as (
select droppedYear = yearBought + 1,
foregoneSales = sum(sales)
from @t t
where YearBought = Max_Year
group by YearBought
),
newSales as (
select YearBought,
sales = sum(sales),
newSales = sum(case when yearBought = min_year then sales end)
from @t t
group by YearBought
)
select YearBought,
n.sales,
n.newSales,
d.foregoneSales
from newSales n
left join droppedProds d on n.yearBought = d.droppedYear
order by YearBought;
Results in:
+------------+-------+----------+---------------+
| YearBought | sales | newSales | foregoneSales |
|------------|-------|----------|---------------|
| 2016 | 30 | 10 | |
| 2017 | 35 | | 20 |
| 2018 | 35 | 5 | |
| 2019 | 45 | | |
| 2020 | 20 | | 35 |
+------------+-------+----------+---------------+