A service of Daily Data, Inc.
Contact Form

Unix Server Tech Knowledge Base

User Tools

Site Tools

Trace: • The WITH RECURSIVE syntax
software:mysql:withrecursive

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
software:mysql:withrecursive [2026/02/06 22:01] – rodolicosoftware:mysql:withrecursive [2026/02/06 22:05] (current) – rodolico
Line 1: Line 1:
 ====== The WITH RECURSIVE syntax ====== ====== The WITH RECURSIVE syntax ======
  
-Using FrontAccounting (https://www.frontaccounting.com/), I needed a way to project recurring revenue for the next 12 months. I had a combination of Perl script and mysql query to do this, but wanted to just have a single query that I could plug into my report tool+Using [[https://www.frontaccounting.com/|FrontAccounting]], I needed a way to project recurring revenue for the next 12 months. I had a combination of a Perl script and a MySQL query to do this, but I wanted a single query that I could plug into my report tool.
  
 ===== Warnings ===== ===== Warnings =====
  
-Just like it says, this is recursion, generally used to retrieve data from a hierarchial configuration.+Just like it says, this is recursion, generally used to retrieve data from a hierarchical configuration.
  
-It is very resource intensive if it has to recurse very deep. Be careful when implenting this in the real world asa poorly written CTE can blow up your database server.+It is very resource intensive if it has to recurse very deep. Be careful when implementing this in the real world, as a poorly written CTE can blow up your database server.
  
 ===== Original Task ===== ===== Original Task =====
  
-The basic query, after some looking around, turned out to be:+After some looking around, the basic query turned out to be:
  
 <code sql> <code sql>
Line 31: Line 31:
 </code> </code>
  
-I then ran the output of this through a Perl script which would calculate when invoices would come up and increment a monthly amount by the invoice amount. Some invoices recur monthly, some quarterly, some semi-annually and some annually (the integer value monthly in the query).+I then ran the output of this through a Perl script that calculated when invoices would come up and incremented a monthly amount by the invoice amount. Some invoices recur monthly, some quarterly, some semi-annually, and some annually (the integer value ''monthly'' in the query).
  
-I was able to bypass the Perl script by learning the new CTE (Common Table Expressions) syntax for MySQL (and MariaDB). This is similar to processing queries into temp tables, then running an additional query against them.+I was able to bypass the Perl script by learning the CTE (Common Table Expressions) syntax for MySQL (and MariaDB). This is similar to processing queries into temp tables, then running an additional query against them.
  
 ===== Solution ===== ===== Solution =====
Line 103: Line 103:
 </code> </code>
  
-  * This will project into the future 12 months. Since our recurrent invoices have that as a maximum (annual), it works for us. However, if you wanted to change it to six, or 24, you can change the constant 12 near the middle of the query //INTERVAL 12 MONTH// +   * This projects 12 months into the future. Since our recurrent invoices have that as a maximum (annual), it works for us. If you want 6 or 24 months, change the constant 12 near the middle of the query: //INTERVAL 12 MONTH//. 
-  * Our invoicing is done on the 15th of every month, so in three locations, we have that hard coded also.  Look for //'%Y-%m-15'//. Since we are working with monthly events, the day is pretty arbitrary (so long as the invoices are due the 15th each time).+   * Our invoicing is done on the 15th of every month, so in three locations we hard-code the date format. Look for //'%Y-%m-15'//. Since we are working with monthly events, the day is arbitrary as long as the invoices are due on the 15th each time.
  
 ===== Explanation ===== ===== Explanation =====
Line 140: Line 140:
 The result returns one row per month: The result returns one row per month:
  
-  * ''month_start'': first day of the month (YYYY-MM-01)+  * ''month_start'': 15th of the month (YYYY-MM-15)
   * ''invoiced_total'': total invoice amount expected for that month   * ''invoiced_total'': total invoice amount expected for that month
  
Line 147: Line 147:
  
 <code sql> <code sql>
-DATE_ADD(DATE_FORMAT(MIN(nextDue), '%Y-%m-01'), INTERVAL 12 MONTH)+DATE_ADD(DATE_FORMAT(MIN(nextDue), '%Y-%m-15'), INTERVAL 12 MONTH)
 </code> </code>
  
software/mysql/withrecursive.1770436883.txt.gz · Last modified: by rodolico