1

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
0
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

4
  • Thanks! The %%P1=0%% is indeed the Plotalot plugin. It works with multiple other ClientID's as well. So this query works for this section but not for calculations with numbers from thousand onwards. Example: Fees Amount Result 132.00 4,862.00 128.00 So the formula only deducts 132-4=128.
    – Gart
    Jan 15 at 9:23
  • Hi, I tried it but no result. Also added it to "Amount". I added some extra db rows for better testing purposes. db-fiddle.com/f/uZraahz6huHqUBp3qjuk9z/13 Note: Month_payment became Paydate.
    – Gart
    Jan 15 at 15:26
  • I changed Line27 in the query. db-fiddle.com/f/uZraahz6huHqUBp3qjuk9z/15 FORMAT() was converting the SUM() value to a formatted string, but at that point in the query it was still "being passed to another point in the query" (not actually part of the finished result set). FORMAT() should only be used in the outermost/parent query's SELECT clause.
    – mickmackusa
    Jan 18 at 21:09
  • Thank you so much! Really helpful explanation, learning more and more, and I used your example script already for making other queries from our db.
    – Gart
    Jan 18 at 21:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.