By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[Payment](
[PaymentId] [int] NOT NULL,
[ContractID] [int] NULL,
[PaymentDate] [datetime] NULL,
[PaymentAmount] [decimal](18, 2) NULL,
CONSTRAINT [PK_Payment] PRIMARY KEY CLUSTERED
(
[PaymentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
INSERT INTO Payment(PaymentId, ContractID, PaymentDate, PaymentAmount)
VALUES (1, 1111, '2022-01-20 00:00:00.000', 200.00);
INSERT INTO Payment(PaymentId, ContractID, PaymentDate, PaymentAmount)
VALUES (2, 1111, '2022-02-01 00:00:00.000', 300.00)
INSERT INTO Payment(PaymentId, ContractID, PaymentDate, PaymentAmount)
VALUES (3, 1113, '2022-01-20 00:00:00.000', 50.00)
INSERT INTO Payment(PaymentId, ContractID, PaymentDate, PaymentAmount)
VALUES (4, 1113, '2022-01-25 00:00:00.000', 50.00)
INSERT INTO Payment(PaymentId, ContractID, PaymentDate, PaymentAmount)
VALUES (5, 1113, '2022-02-04 00:00:00.000', 100.00)
INSERT INTO Payment(PaymentId, ContractID, PaymentDate, PaymentAmount)
VALUES (6, 1115, '2022-02-20 00:00:00.000', 50.00)
6 rows affected
CREATE TABLE [dbo].[Sales](
[ContractID] [int] NOT NULL,
[SoldDate] [datetime] NULL,
[SoldAmount] [decimal](18, 2) NULL,
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[ContractID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO Sales(ContractID, SoldDate, SoldAmount)
VALUES(1111, '2022-01-01 00:00:00.000', 800.00);
INSERT INTO Sales(ContractID, SoldDate, SoldAmount)
VALUES(1112, '2022-01-04 00:00:00.000', 1000.00);
INSERT INTO Sales(ContractID, SoldDate, SoldAmount)
VALUES(1113, '2022-01-04 00:00:00.000', 500.00);
INSERT INTO Sales(ContractID, SoldDate, SoldAmount)
VALUES(1114, '2022-01-05 00:00:00.000', 200.00);
INSERT INTO Sales(ContractID, SoldDate, SoldAmount)
VALUES(1115, '2022-02-10 00:00:00.000', 100.00);
5 rows affected
SELECT * FROM Payment
| PaymentId | ContractID | PaymentDate | PaymentAmount |
|---|---|---|---|
| 1 | 1111 | 2022-01-20 00:00:00.000 | 200.00 |
| 2 | 1111 | 2022-02-01 00:00:00.000 | 300.00 |
| 3 | 1113 | 2022-01-20 00:00:00.000 | 50.00 |
| 4 | 1113 | 2022-01-25 00:00:00.000 | 50.00 |
| 5 | 1113 | 2022-02-04 00:00:00.000 | 100.00 |
| 6 | 1115 | 2022-02-20 00:00:00.000 | 50.00 |
SELECT * FROM Sales
| ContractID | SoldDate | SoldAmount |
|---|---|---|
| 1111 | 2022-01-01 00:00:00.000 | 800.00 |
| 1112 | 2022-01-04 00:00:00.000 | 1000.00 |
| 1113 | 2022-01-04 00:00:00.000 | 500.00 |
| 1114 | 2022-01-05 00:00:00.000 | 200.00 |
| 1115 | 2022-02-10 00:00:00.000 | 100.00 |
WITH cte_quantity
AS
(
SELECT
PaymentMonth,
SoldAmount,
[1] AS Jan,
[2] AS Feb,
[3] AS Mrz,
[4] AS Apr,
[5] AS Mai,
[6] AS Jun,
[7] AS Jul,
[8] AS Aug,
[9] AS Sep,
[10] AS Okt,
[11] AS Nov,
[12] AS Dez
FROM
(
Select
--S.ContractID ,
MONTH(S.SoldDate) as SoldMonth,
MONTH(P.PaymentDate) as PaymentMonth,
SUM(S.SoldAmount) as SoldAmount,
SUM(P.PaymentAmount) as PaymentAmount
from Sales S
INNER JOIN Payment P ON S.ContractID = P.ContractID
GROUP BY
MONTH(S.SoldDate),
MONTH(P.PaymentDate)
) source
PIVOT
(
-- SUM(SoldAmount)
SUM(PaymentAmount)
| PaymentMonth | Sold | Jan | Feb | Mrz | Apr | Mai | Jun | Jul | Aug | Sep | Okt | Nov | Dez |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1800.00 | 300.00 | null | null | null | null | null | null | null | null | null | null | null |
| 2 | 1400.00 | 400.00 | 50.00 | null | null | null | null | null | null | null | null | null | null |
Warning: Null value is eliminated by an aggregate or other SET operation.