Keboola WIKI‎ > ‎Transformations‎ > ‎MySQL‎ > ‎

Subscription Contracts into Monthly Transactions

The use case here is taking list of contracts, such as:

customerId,productId,startDate,endDate
 idproduct  startDate endDate amountotherColumn(s) 
 1 basic2012-01-01  2012-12-31 100.00 
 2 pro2012-05-16  2012-09-15 150.00 


and converting it into list of expected monthly transactions / revenue events for each month during the term of each contract, such as:

 id transactionDate monthNoproduct  startDate endDate amountotherColumn(s) 
 1 2012-01-01 1 basic2012-01-01  2012-12-31 100.00 
 1 2012-02-01 2 basic 2012-01-01 2012-12-31 100.00 
 1etc...      
 2 2012-05-16 1 pro2012-05-16  2012-09-15 150.00 
 2  2012-05-16 2 pro 2012-05-16 2012-09-15 150.00 
 2 etc...      

You can see that there are two columns added (`date` and `monthNo`), all remaining columns get also carried over, and a record gets created for each month and each contract in effect during that month. The date for the transaction is selected based on the first day of the contract (expecting to bill on the same day of the month). The `monthNo` column contains the number of the month in the series. In case a contract doesn't have end date (is open-ended), the transformation replaces it with the last day of the year following to current (this is written in November 2014, so the date inserted will be 2015-12-31) to allow for next-year outlook.

Tables required:

`contracts` - the list of contracts as per the sample above (note - dates need to be in the yyyy-mm-dd format)
`months` - list of dates, first days in each month covering at least the period:
 firstDay
 2012-01-01
 2012-02-01
 etc...
(CSV sample from 2012-01-01 to 2020-12-31 attached)

Variables:
<contracts> - name of the `contracts` table
<months> - name of the `months` table
<firstDay> - name of the column in the `months` table
<output> - name of the output table
<startDate> - name of the column in the `contracts` table that contains the first day of the contract
<endDate> - name of the column in the `contracts` table that contains the last day of the contract


MySQL query:

# extends contracts with no end date to the end of next year for reporting purposes
UPDATE `<contracts>`
set `<endDate>` = (CONCAT(year(curdate()) + 1,"-12-31"))
WHERE `<endDate>` = "";

#limits months table for faster joins
DELETE FROM `<months>` WHERE `<firstDay>` < (SELECT MIN(`<startDate>`) FROM `<contracts>`);
DELETE FROM `<months>` WHERE `<firstDay>` > (SELECT MAX(`<endDate>`) FROM `<contracts>`);

CREATE TABLE `<output>` AS 
SELECT
    c.*,
    CASE 
        WHEN RIGHT(LAST_DAY(CONCAT(LEFT(m.`<firstDay>`,7),'-01')),2) < RIGHT(c.`<startDate>`,2) THEN LAST_DAY(CONCAT(LEFT(m.`<firstDay>`,7),'-01'))
    ELSE 
        CONCAT(LEFT(m.`<firstDay>`,8),RIGHT(c.`<startDate>`,2)) 
    END AS `transactionDate`, 
    PERIOD_DIFF(REPLACE(LEFT(m.`<firstDay>`,7),'-',''),REPLACE(LEFT(c.`<startDate>`,7),'-','')) + 1 AS `monthNo`
FROM `<contracts>` c
JOIN `<months>` m ON LEFT(m.`<firstDay>`,7) >= LEFT(c.`<startDate>`,7) AND CONCAT(LEFT(m.`<firstDay>`,8),RIGHT(c.`<startDate>`,2)) < c.`<endDate>`;
ċ
months.csv
(1k)
Ondřej Popelka,
Nov 30, 2014, 3:10 PM
Comments