Is it possible to build this table using SQL?

Question

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?

You *could* but such a result set is by far better done in the presentation layer (or more likely, the reporting layer). Presumably the date columns need to be dynamic, which means dynamic SQL, which is a path best only walked down if you *really* know what you are doing. — Thom A, Mar 03 '22 at 18:43
Impossible is impossible. for better help update your question and add script instead of image. Also try more googling and see what is PIVOT. It helps you alot — Meyssam Toluie, Mar 03 '22 at 18:44
I do not understand your green lines. For example, you have `50` on the intersection of `Feb-22` & `Feb-22`. How did you come up with this number? — PM 77-1, Mar 03 '22 at 18:49
Because the ContractID was sold in feb 2022 and also we received a payment for said contractID in feb 2022. If it was sold in January it would have been added to the 400 figure — Jess8766, Mar 03 '22 at 18:52
As alluded to by others, do the formatting in your application/reporting/presentation layer, not in the database. In SQL you should do the calcuations and keep the results in a SQL friendly (normalised) structure. Such as 4 columns; sale_month, payment_month, sale_amount, payment_amount *(each row of data then represents one cell in your pivot table)*. — MatBailie, Mar 03 '22 at 19:07
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Mar 03 '22 at 19:09
Thank you @MatBailie, really useful takeaway for me to have a think about :) — Jess8766, Mar 03 '22 at 19:17

score 0 · Answer 1 · answered Mar 03 '22 at 20:46

0

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

answered Mar 03 '22 at 20:46

Ömer Faruk Ocakoğlu

27
4

score 0 · Answer 2 · answered Mar 03 '22 at 22:23

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

Test on db<>fiddle here

Ooh this is really useful & similar to the approach I was first thinking of. The only thing is it's not fully dynamic? i.e each month year needs to be coded in manually.. but then gain I guess I'd only have to update it once a month :) — Jess8766, Mar 04 '22 at 09:05
Dynamic Sql is required the moment you want to have [column names that depend on the data](https://stackoverflow.com/a/54678913/4003419) and you don't want to hardcode them. — LukStorms, Mar 04 '22 at 09:58
You could also opt for more generic names. Like only the months without year. `[Sold], [SoldYear], [Jan], [Feb], [Mar],... ` — LukStorms, Mar 04 '22 at 10:02

Is it possible to build this table using SQL?

2 Answers2