calculate 2 days prior and 2 days after sales-CodePudding

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

CodePudding user response：

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

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

You can check a sqlfiddle here