calculate 2 days prior and 2 days after sales

Issue

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:

enter image description here

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

Solution

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

Answered By – Alexey

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published