Total Financial exposure per year for warranty in SQL

user_16485531 0
Viewed 81

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!

finance sql-server sql
1 Answers

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

dbfiddle demo

user_1856470
Related