Dynamic values SQL

Question

MasterInput-1

Location	2020	2021	2022	2023
India	2.3	2.4	2.4	2.4
Australia	1.2	1.3	1.3	1.3

Input-2

S No	Name	Location	YearMonth	Value
1	A1	India	Mar-22	2.060
2	A2	India	Jun-22	1.547
3	A3	India	Jan-23	0.790
4	A4	India	Jul-21	3.080
5	A5	Australia	Oct-22	2.745
6	A6	Australia	Mar-21	1.823
7	A7	Australia	Sep-21	14.350
8	A8	Australia	Aug-22	0.621

InterimCalcualation

S No	Name	Location	YearMonth	Value	Value 2023	Value 2022	Value 2021	Value 2020
1	A1	India	Mar-22	2.060	4.944	4.944	4.944	4.738
2	A2	India	Jun-22	1.547	3.712	3.712	3.712	3.558
3	A3	India	Jan-23	0.790	1.895	1.895	1.895	1.816
4	A4	India	Jul-21	3.080	7.392	7.392	7.392	7.084
5	A5	Australia	Oct-22	2.745	3.568	3.568	3.568	3.294
6	A6	Australia	Mar-21	1.823	2.370	2.370	2.370	2.188
7	A7	Australia	Sep-21	14.350	18.655	18.655	18.655	17.220
8	A8	Australia	Aug-22	0.621	0.807	0.807	0.807	0.745

FinalOutput

Year	FinalValue
2020	40.642
2021	43.344
2022	43.344
2023	43.344

(
ID INT IDENTITY (1,1) 
,LocationName VARCHAR(50)
,YearName   INT
,CBValue FLOAT
)

CREATE TABLE MasterMigration
(
ID INT IDENTITY(1,1)
,AppName VARCHAR(50)
,LocationName VARCHAR(50)
,YearMigrated INT
,MigValue FLOAT
)

INSERT into MasterMigration VALUES('A1','India',2022,2.06)
INSERT into MasterMigration VALUES('A2','India',2022,1.547)
INSERT into MasterMigration VALUES('A3','India',2023,0.79)
INSERT into MasterMigration VALUES('A4','India',2021,3.08)
INSERT into MasterMigration VALUES('A5','Australia',2022,2.745)
INSERT into MasterMigration VALUES('A6','Australia',2021,1.823)
INSERT into MasterMigration VALUES('A7','Australia',2021,14.35)
INSERT into MasterMigration VALUES('A8','Australia',2022,0.621)


INSERT INTO MAsterLocation VALUES('India',2020,2.3)
INSERT INTO MAsterLocation VALUES('India',2021,2.4)
INSERT INTO MAsterLocation VALUES('India',2022,2.4)
INSERT INTO MAsterLocation VALUES('India',2023,2.4)
INSERT INTO MAsterLocation VALUES('Australia',2020,1.2)
INSERT INTO MAsterLocation VALUES('Australia',2021,1.3)
INSERT INTO MAsterLocation VALUES('Australia',2022,1.3)
INSERT INTO MAsterLocation VALUES('Australia',2023,1.3)

DECLARE @EndLoop INT
,@StartLoop INT
,@Diff INT
,@StartYear INT 
SELECT @StartYear=MIN(YearMigrated) 
FROM MasterMigration
SET @Diff=DATEPART(yyyy,GETDATE()) -@StartYear
    SET @StartLoop=@Diff+1
    SET @EndLoop=0

IF OBJECT_ID ('tempdb..#YearDimension') IS NOT NULL  
DROP TABLE #YearDimension


IF OBJECT_ID ('tempdb..#InterimOutput') IS NOT NULL  
DROP TABLE #InterimOutput
CREATE TABLE #YearDimension
(
YearID INT
)
WHILE @StartLoop >= @EndLoop
BEGIN 
    INSERT INTO #YearDimension
    SELECT DATEPART (yyyy,DATEADD(YEAR, -@StartLoop, GETDATE())) X
    SET @StartLoop=@StartLoop-1
END


--SELECT * FROM #YearDimension

SELECT *
INTO #InterimOutput
FROM (
SELECT AppName,X.LocationName,Yearmigrated,MigValue,YearID,YearNAme,CBValue,(MigValue*CBValue)FinalValue 
FROM MasterMigration X
CROSS APPLY #YearDimension Y
JOIN MasterLOcation Z
ON X.LocationName=Z.LocationName
AND Y.YearID=Z.YearName
)MM

and finally:

-- SELECT * FROM #InterimOutput

SELECT YearID,SUM(FInalValue) FV
FROM #InterimOutput
GROUP BY YearID
--ORDER BY APPNAMEx

Final Output Years Logic : based on minyear to current Year..

It will be dynamic and MasterMigration contains many records.

Is there any better way to get the outputs when we have huge records..Any alternate design which helps for easy retrieval.. Pls help..

Answer 1

You can put all in one got, but the following needs SQL Server 2022 for generate_series, where it was finally implemented.

WITH CTE AS 
(SELECT 
--  AppName
--  ,X.LocationName
--  ,Yearmigrated
--  ,MigValue
    --,
  YearID
--  ,[YearName]
--  ,CBValue
    ,(MigValue*CBValue)FinalValue 
FROM MasterMigration X
CROSS APPLY (SELECT value as YearID 
FROM GENERATE_SERIES((SELECT MIN(YearMigrated) 
FROM MasterMigration
) - 1, DATEPART(yyyy,GETDATE()) +1)) Y
JOIN MasterLocation Z
ON X.LocationName = Z.LocationName 
  AND Y.[YearID] = Z.YearName)
SELECT YearID,SUM(FInalValue) FV
FROM CTE
GROUP BY YearID

YearID	FV
2020	40.6439
2021	43.3455
2022	43.3455
2023	43.3455

fiddle

If you have an older version, then you can make another prior recursive CTE to get your year see Years separated by comma CTE in SQL Server