Suggested way to do a 'parallel period' SQL statement-CodePudding

Let's say I want to get the profit between two dates. Then I can do something like this:

SELECT SUM(Profit)
FROM Sales
WHERE date BETWEEN '2014-01-01' AND '2014-02-01' AND <other_filters>

I would then like to compare it to a previous period offset by a fixed amount. It could be written something like this to get it in two rows:

SELECT SUM(Profit)
FROM Sales
WHERE date BETWEEN '2014-01-01' AND '2014-02-01' AND <other_filters>
UNION ALL
SELECT SUM(Profit)
FROM Sales
WHERE date BETWEEN '2014-01-01' - INTERVAL 1 YEAR AND '2014-02-01' - INTERVAL 1 YEAR AND <other_filters>

Is there a way to do this without a union? I am looking for something like this:

SELECT
    SELECT SUM(Profit),
    ???
FROM Sales
WHERE date BETWEEN '2014-01-01' AND '2014-02-01' AND <other_filters>

I think the tricky part here is how to 'un-do' the where filter for the offseted-time calculation.

CodePudding user response：

You can use conditional aggregation and OR the range checks in the WHERE clause (unless they are subsequent in which case you can combine them directly of course).

SELECT sum(CASE
             WHEN date >= '2014-01-01'
                  AND date < '2014-02-02' THEN
               profit
             ELSE
               0
           END),
       sum(CASE
             WHEN date >= '2014-01-01' - INTERVAL 1 YEAR
                  AND date < '2014-02-02' - INTERVAL 1 YEAR THEN
               profit
             ELSE
               0
           END)
       FROM sales
       WHERE date >= '2014-01-01'
             AND date < '2014-02-02'
             OR date >= '2014-01-01' - INTERVAL 1 YEAR
                AND date < '2014-02-02' - INTERVAL 1 YEAR;

Note: Prefer not to use BETWEEN here but check for a right half open range check. That way, if the precision of date changes, records on the end past midnight are still in the results.