I have a year master with values 2019 to 2023. For each year I have created range as for 2019 to 2019, 2019 to 2020, 2019 to 2021, 2019 to 2022, 2019 to 2023. Because I want to add each year NetValue to next year based on projects enddate.
SUM() on NetValue of each project as if any project starts in 2019 and ends in 2019 then should add in that year only.enddate null then it means it is continued till max year value. Then it's Net Value should add in 2019, 2020, 2021, 2022, 2023.Kindly provide some ideas to implement this logic. So far what I have tried is below:
;WITH YearRanges AS
(
SELECT 2019 AS StartYear, 2019 AS EndYear
UNION ALL
SELECT StartYear, EndYear + 1
FROM YearRanges
WHERE EndYear + 1 <= 2023
)
SELECT
YR.EndYear AS Year,
SUM(CASE
WHEN YEAR(T.projectstartdate) = YR.EndYear THEN cast(T.NetValue as decimal(18,2))
WHEN YEAR(T.enddate) >= YR.EndYear THEN cast(T.NetValue as decimal(18,2))
ELSE 0
END) AS TotalAmount
FROM
YearRanges YR
JOIN
TrackerMainEntry T ON YEAR(T.projectstartdate) >= YR.StartYear
GROUP BY
YR.EndYear
OPTION (MAXRECURSION 0);
Tried: I have tried to select according to the specified range.
Resulted: I get wrong values for each year. I have also applied Cross Join but still giving me wrong values.
Below is the logic that actually I have to implement
SELECT
NetValue, projectstartdate, enddate
FROM
[TrackerMainEntry]
WHERE
YEAR(projectstartdate) IN (2019)
UNION
SELECT
NetValue, projectstartdate, enddate
FROM
[TrackerMainEntry]
WHERE
YEAR(projectstartdate) IN (2019)
AND YEAR(enddate) IS NULL
UNION
SELECT
NetValue, projectstartdate, enddate
FROM
[TrackerMainEntry]
WHERE
YEAR(projectstartdate) IN (2019)
AND YEAR(enddate) = 2022
UNION
SELECT
NetValue, projectstartdate, enddate
FROM
[TrackerMainEntry]
WHERE
YEAR(projectstartdate) IN (2020)
Sample data:
CREATE TABLE [dbo].[SampleData]
(
[productname] [varchar](100) NULL,
[NetValue] [varchar](100) NULL,
[ProjectStartDate] [date] NULL,
[EndDate] [date] NULL);
GO
INSERT [dbo].[SampleData] ([productname], [NetValue], [ProjectStartDate], [EndDate])
VALUES ('Project_22', '-11224.68', CAST('2020-03-01' AS Date), CAST('2021-12-15' AS Date)),
('Project_64', '261706.4', CAST('2019-11-01' AS Date), CAST('2022-08-18' AS Date)),
('Project_64', '21309.44', CAST('2021-01-01' AS Date), CAST('2022-08-18' AS Date)),
('Project_4', '3057.2', CAST('2020-03-01' AS Date), NULL),
('Project_39', '88298.272', CAST('2020-07-01' AS Date), CAST('2022-08-08' AS Date)),
('Project_33', '256230.16', CAST('2019-12-01' AS Date), CAST('2022-08-30' AS Date)),
('Project_10', '219442.44', CAST('2021-10-01' AS Date), CAST('2021-11-26' AS Date)),
('Project_61', '-18707.8', CAST('2021-06-01' AS Date), NULL),
('Project_44', '40444.52', CAST('2021-10-01' AS Date), CAST('2022-09-01' AS Date)),
('Project_37', '989082', CAST('2021-11-01' AS Date), CAST('2021-12-15' AS Date)),
('Project_62', '113845.344', CAST('2019-01-01' AS Date), NULL),
('Project_63', '143278.56', CAST('2021-05-01' AS Date), CAST('2022-09-15' AS Date)),
('Project_68', '33998.896', CAST('2021-05-01' AS Date), CAST('2022-08-01' AS Date)),
('Project_65', '56889.04', CAST('2020-04-01' AS Date), NULL),
('Project_56', '279507.92', CAST('2020-10-01' AS Date), NULL),
('Project_20', '145405.92', CAST('2022-05-01' AS Date), CAST('2022-05-27' AS Date)),
('Project_60', '365556.16', CAST('2022-08-22' AS Date), CAST('2022-05-27' AS Date)),
('Project_5', '5322.264', CAST('2020-08-01' AS Date), CAST('2022-09-01' AS Date)),
('Project_51', '31690.9', CAST('2020-12-01' AS Date), NULL),
('Project_67', '28117.984', CAST('2021-06-01' AS Date), NULL),
('Project_59', '10735.488', CAST('2021-03-01' AS Date), NULL),
('Project_12', '2974.98', CAST('2022-05-03' AS Date), CAST('2022-05-13' AS Date)),
('Project_29', '18307.36', CAST('2019-09-01' AS Date), NULL),
('Project_47', '147818.38', CAST('2020-09-01' AS Date), NULL),
('Project_2', '-8660.24', CAST('2021-01-01' AS Date), CAST('2021-12-15' AS Date)),
('Project_16', '14490.552', CAST('2020-10-01' AS Date), NULL),
('Project_45', '188519.088', CAST('2021-03-01' AS Date), NULL),
('Project_15', '161817.76', CAST('2021-02-01' AS Date), CAST('2022-09-01' AS Date)),
('Project_55', '39743.344', CAST('2022-01-01' AS Date), CAST('2022-05-30' AS Date)),
('Project_35', '139378.08', CAST('2020-12-01' AS Date), CAST('2022-08-18' AS Date)),
('Project_40', '12552.72', CAST('2021-01-01' AS Date), CAST('2023-10-02' AS Date)),
('Project_43', '9998.896', CAST('2021-07-01' AS Date), CAST('2023-10-02' AS Date)),
('Project_53', '94926.32', CAST('2022-08-01' AS Date), CAST('2022-01-10' AS Date)),
('Project_7', '35094.992', CAST('2021-03-01' AS Date), NULL),
('Project_50', '20534.18', CAST('2021-05-01' AS Date), CAST('2022-09-01' AS Date)),
('Project_8', '674.5', CAST('2020-07-01' AS Date), NULL),
('Project_3', '4380.568', CAST('2019-11-01' AS Date), NULL),
('Project_10', '42712.64', CAST('2022-09-22' AS Date), CAST('2023-10-02' AS Date)),
('Project_33', '129340.44', CAST('2020-10-01' AS Date), NULL),
('Project_33', '119190.2', CAST('2021-01-01' AS Date), NULL),
('Project_33', '102820.46', CAST('2021-10-01' AS Date), NULL),
('Project_33', '150575.48', CAST('2022-09-01' AS Date), NULL),
('Project_23', '55964.16', CAST('2020-06-01' AS Date), NULL),
('Project_21', '-16.32', CAST('2020-08-01' AS Date), NULL),
('Project_6', '-544.66', CAST('2021-02-01' AS Date), NULL),
('Project_31', '-411', CAST('2020-08-01' AS Date), NULL),
('Project_42', '-378.41', CAST('2021-03-01' AS Date), NULL),
('Project_19', '-9460.23', CAST('2020-12-01' AS Date), NULL),
('Project_1', '71573.1', CAST('2021-03-01' AS Date), NULL),
('Project_26', '282114.4', CAST('2020-12-01' AS Date), NULL),
('Project_63', '19964.16', CAST('2022-08-22' AS Date), NULL),
('Project_37', '986980', CAST('2022-05-22' AS Date), CAST('2022-09-15' AS Date)),
('Project_57', '1349', CAST('2021-07-01' AS Date), NULL),
('Project_41', '998.896', CAST('2021-06-01' AS Date), NULL),
('Project_17', '12489.04', CAST('2022-08-22' AS Date), NULL),
('Project_53', '16853.2', CAST('2022-08-01' AS Date), CAST('2022-10-07' AS Date)),
('Project_20', '21003.184', CAST('2022-11-01' AS Date), CAST('2023-02-03' AS Date)),
('Project_37', '15302.56', CAST('2022-10-01' AS Date), NULL),
('Project_28', '60000', CAST('2023-01-01' AS Date), CAST('2022-10-07' AS Date)),
('Project_15', '108967.68', CAST('2022-12-01' AS Date), CAST('2023-02-01' AS Date));
GO
Is this what you're looking for? (Note: I modified your CTE a little...)
;WITH YearRanges AS
(
SELECT 2019 AS [PeriodYear]
UNION ALL
SELECT [PeriodYear] + 1
FROM YearRanges
WHERE [PeriodYear] + 1 <= 2023
)
SELECT
yr.[PeriodYear],
SUM(sd.NetValue) AS TotalAmount
FROM YearRanges yr
INNER JOIN dbo.SampleData sd
ON YEAR(sd.ProjectStartDate) <= yr.[PeriodYear]
AND
(
YEAR(sd.EndDate) IS NULL
OR YEAR(sd.EndDate) >= yr.[PeriodYear]
)
GROUP BY yr.[PeriodYear]
ORDER BY yr.[PeriodYear]
/*
Results
-------
PeriodYear TotalAmount
2019 654469.83
2020 1867903.70
2021 4050471.22
2022 4885286.36
2023 2060527.91
*/