I'm on SQL Server 2019.
I'm stuck in a problem that seems to be quite simple, but I'm stuck.
I need to create a view in SQL that calculates the total financial exposure for warranty of our projects per year.
Each project has multiple warranty terms (but they don't matter for this calculation). I'm interested only in the financial exposure for the whole company.
The example table is:
| Project number | Warranty term | Financial exposure | Warranty period start | Warranty period end |
|---|---|---|---|---|
| Project 1 | painting | 1,000,000 | 01-Jan-2021 | 01-Jan-2023 |
| Project 2 | structure | 500,000 | 15-May-2021 | 15-May-2031 |
| Project 3 | painting | 1,000,000 | 25-Mar-2022 | 24-Mar-2042 |
| Project 3 | performance | 5,000,000 | 25-Mar-2022 | 25-Mar-2030 |
| Project 4 | uptime | 10,000,000 | 01-Jan-2023 | 01-Jan-2033 |
I'm looking for something like:
| Year | Financial Exposure |
|---|---|
| 2021 | 1,500,000 |
| 2022 | 7,500,000 |
| 2023 | 17,500,000 |
| 2024 | 16,500,000 |
| 2025 | 16,500,000 |
| 2026 | 16,500,000 |
| 2027 | 16,000,000 |
| 2028 | 16,000,000 |
| 2029 | 16,000,000 |
| 2030 | 16,000,000 |
| 2031 | 11,000,000 |
| 2032 | 11,000,000 |
| 2033 | 11,000,000 |
| 2034 | 1,000,000 |
| 2035 | 1,000,000 |
| 2036 | 1,000,000 |
| 2037 | 1,000,000 |
| 2038 | 1,000,000 |
| 2039 | 1,000,000 |
| 2040 | 1,000,000 |
| 2041 | 1,000,000 |
| 2042 | 1,000,000 |
Thank you so much!
you need a tally table. Or generate one on the fly in your query using recursive cte. CROSS JOIN your project table to the tally table
with years as -- the tally table
(
select year = min(year(period_start)), max_year = max(year(period_end))
from project
union all
select year = year + 1, max_year
from years
where year < max_year
)
select y.year, sum(exposure) As [Financial Exposure]
from project p
cross join years y
where year(p.period_start) <= y.year
and year(p.period_end) >= y.year
group by y.year