By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Sales (
ContractID INT,
[Sold Date] DATE,
[Sold Amount] DECIMAL(8,0)
);
CREATE TABLE Payment (
PaymentID INT,
ContractID INT,
[Payment Date] DATE,
[Payment Amount] DECIMAL(8,0)
);
INSERT INTO Sales (ContractID, [Sold Date], [Sold Amount]) VALUES
(1111, '2022-01-01', 800)
, (1112, '2022-01-02', 1000)
, (1113, '2022-01-03', 500)
, (1114, '2022-01-04', 200)
, (1115, '2022-02-01', 100)
;
5 rows affected
INSERT INTO Payment (PaymentID, ContractID, [Payment Date], [Payment Amount]) VALUES
(1, 1111, '2022-01-01', 200)
, (2, 1111, '2022-02-02', 300)
, (3, 1113, '2022-01-03', 50)
, (4, 1113, '2022-01-04', 50)
, (5, 1113, '2022-02-05', 100)
, (6, 1115, '2022-02-06', 50)
;
6 rows affected
SELECT [Sold], [Jan-22], [Feb-22], [Mar-22]
FROM
(
SELECT 0 as Seq, 'Paid' AS [Sold]
, SUM(CASE WHEN FORMAT([Sold Date],'MMM-yy') = 'Jan-22'
THEN [Sold Amount] ELSE 0 END) AS [Jan-22]
, SUM(CASE WHEN FORMAT([Sold Date],'MMM-yy') = 'Feb-22'
THEN [Sold Amount] ELSE 0 END) AS [Feb-22]
, SUM(CASE WHEN FORMAT([Sold Date],'MMM-yy') = 'Mar-22'
THEN [Sold Amount] ELSE 0 END) AS [Mar-22]
FROM Sales
UNION ALL
SELECT m.Seq, m.PaymentMonth
, SUM(CASE WHEN SoldMonth = 'Jan-22' THEN PaymentAmount ELSE 0 END) AS [Jan-22]
, SUM(CASE WHEN SoldMonth = 'Feb-22' THEN PaymentAmount ELSE 0 END) AS [Feb-22]
, SUM(CASE WHEN SoldMonth = 'Mar-22' THEN PaymentAmount ELSE 0 END) AS [Mar-22]
FROM (VALUES
(1,'Jan-22'),
(2,'Feb-22'),
(3,'Mar-22')
) m(Seq, PaymentMonth)
LEFT JOIN (
SELECT ContractID
, FORMAT(EOMONTH([Payment Date]), 'MMM-yy') AS PaymentMonth
, SUM([Payment Amount]) AS PaymentAmount
FROM Payment
GROUP BY ContractID, EOMONTH([Payment Date])
) p ON p.PaymentMonth = m.PaymentMonth
LEFT JOIN (
SELECT ContractID
, FORMAT(MAX([Sold Date]), 'MMM-yy') AS SoldMonth
, SUM([Sold Amount]) AS SoldAmount
FROM Sales
GROUP BY ContractID
| Sold | Jan-22 | Feb-22 | Mar-22 |
|---|---|---|---|
| Paid | 2500 | 100 | 0 |
| Jan-22 | 300 | 0 | 0 |
| Feb-22 | 400 | 50 | 0 |
| Mar-22 | 0 | 0 | 0 |