By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Create the tables
CREATE TABLE Table1 (
[Month] date,
Amount decimal(18, 2)
);
CREATE TABLE Table2 (
[Month] date,
Amount decimal(18, 2)
);
CREATE TABLE Table3 (
[Month] date,
Amount decimal(18, 2)
);
-- Insert data into the tables
INSERT INTO Table1 ([Month], Amount)
VALUES
('2023-09-01', 2.00),
('2023-10-01', 3.00),
('2023-10-01', 1.00);
INSERT INTO Table2 ([Month], Amount)
VALUES
('2023-09-01', 1.00),
('2023-10-01', 2.00),
('2023-10-01', 2.00);
INSERT INTO Table3 ([Month], Amount)
VALUES
('2023-09-01', 5.00),
('2023-10-01', 2.00),
('2023-10-01', 1.00);
9 rows affected
-- Calculate sales growth
WITH CombinedData AS (
SELECT [Month], SUM(Amount) AS monthly_sales
FROM (
SELECT [Month], Amount FROM Table1
UNION ALL
SELECT [Month], Amount FROM Table2
UNION ALL
SELECT [Month], Amount FROM Table3
) AS Combined
GROUP BY [Month]
),
SalesGrowth AS (
SELECT
FORMAT([Month], 'MMM yyyy') [Month],
monthly_sales,
LAG(monthly_sales, 1, 0) OVER (ORDER BY [Month]) AS prev_month_sales,
CASE
WHEN LAG(monthly_sales, 1, 0) OVER (ORDER BY [Month]) = 0 THEN 0
ELSE (monthly_sales - LAG(monthly_sales, 1, 0) OVER (ORDER BY [Month])) * 100.0 / LAG(monthly_sales, 1, 0) OVER (ORDER BY [Month])
END AS sales_growth
FROM CombinedData
)
SELECT [Month], monthly_sales, prev_month_sales, sales_growth
FROM SalesGrowth;
| Month | monthly_sales | prev_month_sales | sales_growth |
|---|---|---|---|
| Sep 2023 | 8.00 | 0.00 | 0.000000 |
| Oct 2023 | 11.00 | 8.00 | 37.500000 |