I am doing a case for a subscription-based company that wants an overview of their monthly sales. This means that I need to sum up the revenue from all active subscriptions, grouped by month. I have managed to work my way through the data to create a table that looks like the one below. Each row is a contract with a start date, end date, proposition (either standard or discount), and the corresponding price per month.

I want to have the revenue for each month, starting from the minimum ContractStartDate (the first time they received any revenue), for both Standard and Discount propositions. So I would like to have something that looks like this:

For each month and each proposition (Standard or Discount), I would need to go over every contract and sum up the proposition price if the startdate was after that month and the enddate was before that month (or there is no enddate).

This is the code I tried, but I feel like I am really far off the solution:

SELECT 
MonthYear, PropositionReference, 
SUM(CASE WHEN STRFTIME("%m %Y", ContractStartDate) <= MonthYear 
AND (ContractEndDate IS NULL OR STRFTIME("%m %Y", ContractEndDate) >= MonthYear) 
AND PropositionReference = "Standard" 
THEN PropositionPrice ELSE 0 END) AS RevenueStandard,
SUM(CASE WHEN STRFTIME("%m %Y", ContractStartDate) <= MonthYear 
AND (ContractEndDate IS NULL OR STRFTIME("%m %Y", ContractEndDate) >= MonthYear) 
AND PropositionReference = "Discount" 
THEN PropositionPrice ELSE 0 END) AS RevenueDiscount
FROM (SELECT *, STRFTIME("%m %Y", ContractStartDate) AS MonthYear FROM Combined)
GROUP BY MonthYear, PropositionReference
ORDER BY MonthYear, PropositionReference

CodePudding user response：

The following could be the basis if your question has been interpreted correctly.

WITH 
    range AS (
        SELECT min(contractstartdate) AS low, max(coalesce(contractenddate,date('now'))) AS high FROM combined
        ),
    t AS (
        SELECT 
            low AS month,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Standard'
                AND strftime('%s',low) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            ) 
            AS stnd,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Discount'
                AND strftime('%s',low) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            )
            AS dscnt
        FROM range
        UNION ALL 
            SELECT date(month,' 1 month'),
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Standard'
                AND strftime('%s',date(month,' 1 month')) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            ) 
            AS stnd,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Discount'
                AND strftime('%s',date(month,' 1 month')) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            )
            AS dscnt
            FROM t 
            WHERE date(month,' 1 month') < (SELECT max(coalesce(contractenddate,date('now'))) FROM combined) 
            LIMIT 500 /* just in case to stop continuous loop  */
    ) 
SELECT * FROM t;

So this is

• first creating a CTE (Common Table Expression (temp table that exists during the execution)) that consists of a single row with two values the lowest started date and the highest end date (if any null then current date ).

• second creating another cte, but a recursive one where the first row is the first month, the second the next month .... until the last month (or in this case for a maximum of 500 iterations (to prevent accidental endless loop)).

  • each iteration retrieves the sum of the respective type for rows from the combined table that include the date being processed.

As a demo, based upon your data then:-

DROP TABLE IF EXISTS combined;
CREATE TABLE IF NOT EXISTS combined (ContractStartDate TEXT,ContractEndDate TEXT,PropositionReference TEXT,PropositionPrice REAL);
INSERT INTO combined VALUES
    ('2018-01-03',  NULL,   'Standard', 4.5)
    ,('2019-01-17', NULL,   'Discount', 2)
    ,('2018-02-09', '2019-01-17',   'Standard', 4.5)
;
WITH 
    range AS (
        SELECT min(contractstartdate) AS low, max(coalesce(contractenddate,date('now'))) AS high FROM combined
        ),
    t AS (
        SELECT 
            low AS month,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Standard'
                AND strftime('%s',low) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            ) 
            AS stnd,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Discount'
                AND strftime('%s',low) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            )
            AS dscnt
        FROM range
        UNION ALL 
            SELECT date(month,' 1 month'),
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Standard'
                AND strftime('%s',date(month,' 1 month')) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            ) 
            AS stnd,
            (
                SELECT 
                    coalesce(sum(propositionprice),0) 
                FROM combined
                WHERE propositionreference = 'Discount'
                AND strftime('%s',date(month,' 1 month')) BETWEEN strftime('%s',contractStartDate) AND strftime('%s',coalesce(contractEndDate,date('now')))
            )
            AS dscnt
            FROM t 
            WHERE date(month,' 1 month') < (SELECT max(coalesce(contractenddate,date('now'))) FROM combined) 
            LIMIT 500 /* just in case to stop continuous loop  */
    ) 
SELECT * FROM t;
DROP TABLE IF EXISTS combined;

results in :-