Is it possible to build this table using SQL?-CodePudding

I have 2 tables, a Sales table and a Payment table structured like the below.

The 2 are joined using the ContractID column. What I want to see is a matrix that shows me at the top, the sum of (sold amount) per monthyear. Then on the left, I want to see the payment dates by month year, and any payments that have been made. My ideal output would look like the below.

The yellow line being the total sold by month-year, and the green lines being all the payments that have been made from the payments table. I don't really know where to start with this one, does anyone have any advice on how to achieve this? I am going to unpivot the sold table first to get my dates across the top, just pondering the next step to pull this table together?

CodePudding user response：

If I didn't understand wrong, it should be like this.

  WITH PaymentMatrix
  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
  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(PaymentAmount)
      FOR SoldMonth
      IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] )
  ) AS pvtMonth
  )

  SELECT
   PaymentMonth, 
   SUM(SoldAmount) AS Sold,
   sum(Jan)as Jan , sum(Feb) as Feb, sum(Mrz) as Mrz, sum(Apr) as Apr, sum(Mai) as Mai, 
   sum(Jun)as Jun , sum(Jul) as Jul, sum(Aug) as Aug, sum(Sep) as Sep, sum(Okt) as Okt,
   sum(Nov) as Nov, sum(Dez) as Dez   
  FROM PaymentMatrix
  GROUP BY PaymentMonth

Fidler Sample

Sample Image

CodePudding user response：

I suggest using conditional aggregation and a union.
Since the PIVOT syntax is more limited.

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
) s ON s.ContractID = p.ContractID
GROUP BY m.Seq, m.PaymentMonth
) q
ORDER BY Seq;

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

Sold	Jan-22	Feb-22
Paid	2500	100
Jan-22	300	0
Feb-22	400	50
Mar-22	0	0

Test on db<>fiddle here