I need a TSQL (SQL Server 2016) script written to list components that will reoccur in the next 30 years.
For example component A has a lifecycle of 6 years with the current programme year of 2025, it will need to list additional rows in my query programme years 2031, 2037, 2046.
*lifecycle is a field, programme year is a field.
This is the existing query and new query must be dynamic as programme years can change.
select [Component Description] ,case when nullif([Progamme Year],'') is null then year(DateAdd(yy, Lifecycle, Cast([Installation Date] As date))) else [Progamme Year] end [Progamme Year] ,Lifecycle ,cast(replace(nullif([Total Cost per install],''), '£','') as decimal(10,2))[Total Cost per install] from [DBAdmin].[dbo].[AssetsDwellingBusinessNewPlan]
PLease see existing sample and output required. Attachment: Items in white background are from the table, item in yellow is an example used, items in red are the expected output.