RSFORM combine 2 submission form results in one Plotalot view

Question

After requesting help from local db-masters with no result, I would like to ask this question here. The output required is this for example:

Shelter ClientID    Year    Month   Fees       Amount     Result
Homes   T0ZK9KZM    2020    7       600.00     49.00      551.00 

Here is an online demo: https://www.db-fiddle.com/f/uZraahz6huHqUBp3qjuk9z/3

The first part of above result (Shelter - Fees) is a query based on formid 25.

The second part (Amount) is a query based on formid 23.

The third part (Result) is a calculation: Fees - Amount.

The combining factor is the ClientID.

How can I merge these pivot queries of RSform data together to generate data for my Plotalot graph?

SELECT
    Shelter,
    ClientID,
    Year,
    Month,
    FORMAT(SUM(Total), 2) AS Fees
FROM (
  SELECT
      MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
      SUM(IF(FieldName = 'Total', FieldValue, NULL)) AS Total,
      Max(IF(FieldName = 'ClientID', FieldValue, NULL)) AS ClientID,
      MAX(IF(FieldName = 'STARTDATE', YEAR(FieldValue), NULL)) AS Year,
      MAX(IF(FieldName = 'STARTDATE', MONTH(FieldValue), NULL)) AS Month  
  FROM o2pe0_rsform_submission_values
  WHERE FormId = 25
  GROUP BY SubmissionId
  Having MAX(IF(FieldName = 'ClientId', FieldValue, NULL)) = '%%P1=0%%'
) DerivedTable
GROUP BY Shelter, ClientId, Year, Month
ORDER BY Shelter, ClientId, Year, Month

SELECT
    Shelter,
    ClientID,
    Year,
    Month,
    FORMAT(SUM(Amount), 2) AS Amount
FROM (
  SELECT
      MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
      SUM(IF(FieldName = 'Amount', FieldValue, NULL)) AS Amount,
      Max(IF(FieldName = 'ClientID', FieldValue, NULL)) AS ClientID,
      MAX(IF(FieldName = 'Month_payment', YEAR(FieldValue), NULL)) AS Year,
      MAX(IF(FieldName = 'Month_payment', MONTH(FieldValue), NULL)) AS Month  
  FROM o2pe0_rsform_submission_values
  WHERE FormId = 23
  GROUP BY SubmissionId
  Having MAX(IF(FieldName = 'ClientId', FieldValue, NULL)) = '%%P1=0%%'
) DerivedTable
GROUP BY Shelter, ClientId, Year, Month
ORDER BY Shelter, ClientId, Year, Month

Answer 1

I think the simplest way forward is to use a JOIN to relate your first query to your second query. An INNER JOIN (same as JOIN) will only generate rows in the result set where both "derived" queries have data.

I am assuming that %%P1=0%% is your Plotalot placeholder.

This was a late-night endeavour, so I cannot guarantee that it is fully refined, optimised, or minimised, but it has proven successful in the dbfiddle.

SELECT
    Derived1.Shelter,
    Derived1.ClientID,
    Derived1.Year,
    Derived1.Month,
    FORMAT(SUM(Total), 2) AS Fees,
    Derived2.Amount,
    FORMAT(SUM(Total) - Derived2.Amount, 2) AS Result
FROM (
  SELECT
      MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
      SUM(IF(FieldName = 'Total', FieldValue, NULL)) AS Total,
      MAX(IF(FieldName = 'ClientID', FieldValue, NULL)) AS ClientID,
      MAX(IF(FieldName = 'STARTDATE', YEAR(FieldValue), NULL)) AS Year,
      MAX(IF(FieldName = 'STARTDATE', MONTH(FieldValue), NULL)) AS Month  
  FROM o2pe0_rsform_submission_values
  WHERE FormId = 25
  GROUP BY SubmissionId
  HAVING MAX(IF(FieldName = 'ClientId', FieldValue, NULL)) = 'T0ZK9KZM'
) Derived1
JOIN (
    SELECT
        Shelter,
        ClientID,
        Year,
        Month,
        SUM(Amount) Amount
    FROM (
        SELECT
            MAX(IF(FieldName = 'SHELTER', FieldValue, NULL)) AS Shelter,
            SUM(IF(FieldName = 'Amount', FieldValue, NULL)) AS Amount,
            MAX(IF(FieldName = 'ClientID', FieldValue, NULL)) AS ClientID,
            MAX(IF(FieldName = 'Paydate', YEAR(FieldValue), NULL)) AS Year,
            MAX(IF(FieldName = 'Paydate', MONTH(FieldValue), NULL)) AS Month  
        FROM o2pe0_rsform_submission_values
        WHERE FormId = 23
        GROUP BY SubmissionId
        HAVING MAX(IF(FieldName = 'ClientId', FieldValue, NULL)) = 'T0ZK9KZM'
    ) Subselect2
    GROUP BY Shelter, ClientId, Year, Month
) Derived2 ON Derived1.Shelter = Derived2.Shelter
              AND Derived1.ClientID = Derived2.ClientID
              AND Derived1.Year = Derived2.Year
              AND Derived1.Month = Derived2.Month
GROUP BY Shelter, ClientId, Year, Month
ORDER BY Shelter, ClientId, Year, Month;

Shelter	ClientID	Year	Month	Fees	Result	Amount
Homes	T0ZK9KZM	2020	6	696.00	141.00	555
Homes	T0ZK9KZM	2020	7	600.00	-5,178.10	5778.1
Homes	T0ZK9KZM	2021	1	132.00	-4,730.00	4862

---

View on DB Fiddle