calculate 2 days prior and 2 days after sales

Question

wants to calculate 2 days prior and 2 days after sales

total_2_days_prior_sales = before 2 days to current date sales sum total_2_days_prior_sales = from current date to next 2 days sales sum

Requirement:

Live Code: http://sqlfiddle.com/#!9/d88bee/15

My Try:

CREATE TABLE test (
    end_date date,
    sales int
);

INSERT INTO test (end_Date, sales)
VALUES ('2022-01-01',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-01',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-02',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-02',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-03',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-04',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-05',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-06',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-07',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-08',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-08',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-09',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-10',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-11',10);
INSERT INTO test (end_Date, sales)
VALUES ('2022-01-12',10);

Code:

select 
end_Date,
-- DATE_SUB(date(end_Date), INTERVAL 2 day),
-- DATE_SUB(date(end_Date), INTERVAL -2 day),
-- DATE_SUB(date(end_Date), INTERVAL 0 day),
SUM(sales) as CurrentSales,
SUM(case when end_Date between DATE_SUB(date(end_Date), INTERVAL 2 day) and DATE_SUB(date(end_Date), INTERVAL 0 day) then sales else 0 end) total_2_days_prior_sales,
SUM(case when end_Date between DATE_SUB(date(end_Date), INTERVAL 0 day) and DATE_SUB(date(end_Date), INTERVAL -2 day) then sales else 0 end) total_2_days_after_sales
from test
group by end_Date

Answer 1

Since MySQL 5.6 does not support window functions and CTE, you can use subqueries to get the totals for prior sales and after sales, like this

SELECT
    *,
    (SELECT SUM(t.sales) 
                 FROM test t 
                 WHERE t.end_date BETWEEN DATE_SUB(s.end_Date, INTERVAL 1 day) AND s.end_date
                 GROUP BY s.end_date) total_2_days_prior_sales,
    (SELECT SUM(t.sales) 
                 FROM test t 
                 WHERE t.end_date BETWEEN s.end_date AND DATE_ADD(s.end_Date, INTERVAL 1 day)
                 GROUP BY s.end_date) total_2_days_after_sales                 
FROM (
    SELECT
        end_Date,
        SUM(sales) CurrentSales
    FROM test
    GROUP BY end_Date
) s

Output

end_Date	CurrentSales	total_2_days_prior_sales	total_2_days_after_sales
2022-01-01	20	20	40
2022-01-02	20	40	30
2022-01-03	10	30	20
2022-01-04	10	20	20
2022-01-05	10	20	20
2022-01-06	10	20	20
2022-01-07	10	20	30
2022-01-08	20	30	30
2022-01-09	10	30	20
2022-01-10	10	20	20
2022-01-11	10	20	20
2022-01-12	10	20	10

---

If to change the interval from 2 days to 3 days, output is like this

end_Date	CurrentSales	total_3_days_prior_sales	total_3_days_after_sales
2022-01-01	20	20	50
2022-01-02	20	40	40
2022-01-03	10	50	30
2022-01-04	10	40	30
2022-01-05	10	30	30
2022-01-06	10	30	40
2022-01-07	10	30	40
2022-01-08	20	40	40
2022-01-09	10	40	30
2022-01-10	10	40	30
2022-01-11	10	30	20
2022-01-12	10	30	10

You can check a sqlfiddle here

---

Also you can use a self JOIN with your table and calculate sub totals conditionally like this

SELECT
    s.end_date,
    s.sales,
    SUM(CASE WHEN t.end_date <= s.end_date
             THEN t.sales
        END) prev_sales,
    SUM(CASE WHEN t.end_date >= s.end_date 
             THEN t.sales
        END) after_sales
FROM (
    SELECT
        end_date,
        SUM(sales) sales
    FROM test
    GROUP BY end_date
) s
JOIN test t ON t.end_date BETWEEN DATE_SUB(s.end_Date, INTERVAL 1 day) AND DATE_ADD(s.end_Date, INTERVAL 1 day)
GROUP BY end_date

This query produces the same output.

You can check it here

In general, a join should perform better.

Also you need a composite index on the end_date and sales columns in your table.