Group by days interval SQL

user_14351491 0
Viewed 625

I have a table with 4 columns:

ID, Date, Office, Reports
  1. "Date" shows when the reports were created.
  2. "Office" in which the office was created.
  3. "Reports" the number of reports created.

Original table

  CREATE TABLE REPORTS (
    `ID` INTEGER,
    `Date` DATE,
    `Office` VARCHAR(2),
    `Reports` INTEGER
  );

  INSERT INTO REPORTS
    (`ID`, `Date`, `Office`, `Reports`)
  VALUES
    ('1', '2020-09-01', 'AR', '3'),
    ('2', '2020-09-01', 'ES', '2'),
    ('3', '2020-09-03', 'ES', '4'),
    ('4', '2020-09-05', 'AR', '1'),
    ('5', '2020-09-05', 'OG', '1'),
    ('6', '2020-09-05', 'ES', '4'),
    ('7', '2020-09-08', 'ES', '5'),
    ('8', '2020-09-08', 'AR', '6'),
    ('9', '2020-09-15', 'OG', '7'),
    ('10', '2020-09-16', 'ES', '9');

I need to use a sql line code to get a table that shows all the dates (not showing duplicated dates), and two new columns "Reports_7_days_prev" and "Reports_7_days_after". The result should be the following table:

date       | Reports 7 prev | Reports 7 after
:--------- | -------------: | --------------:
2020-09-01 |              5 |              26
2020-09-03 |              9 |              21
2020-09-05 |             15 |              17
2020-09-08 |             26 |              18
2020-09-15 |             18 |              16
2020-09-16 |             16 |               9

The way of calculating each column is the following:

  • "Reports 7 days prev": it should sum all the reports created on the 7 days previous to the date of each row. For example, in the row with date "2020-09-15" from the output table, the result 18 is the sum of all the values from column "Reports" within the interval of dates [2020-09-08 , 2020-09-15], corresponding to the rows with "ID" 7, 8 and 9 => (5 + 6 + 7 = 18).

  • " Reports 7 days after": it should sum all the reports created on the 7 days after the date of each row. For example, in the row with date "2020-09-05" from the output table, the result 17 is the sum of all the values from column "Reports" within the interval of dates [2020-09-05 , 2020-09-12], corresponding to the rows with "ID" 4, 5, 6, 7 and 8 => (1 + 1 + 4 + 5 + 6 = 17)

I think than my SQL query should be something like:

SELECT Date, 
       sum([QUERY 1]) as "Reports  7 days prev", 
       sum ([QUERY 2]) as "Reports 7 days after" 
FROM REPORTS 
GROUP BY Date

But I cannot figure out what should I include in [QUERY 1] and [QUERY 2] to get the 7 days previous and 7 days after.

Thank you very much,

mysql sql
2 Answers

You can use Scalar Correlated Subqueries, but don't expect good performance:

select date
  ,(select sum(reports) from REPORTS as r2 where r2.date between r.date - 7 and r.date) as `Reports 7 prev`
  ,(select sum(reports) from REPORTS as r2 where r2.date between r.date and r.date + 7) as `Reports 7 after`
from REPORTS as r
group by date

See fiddle

user_25279050

If there's no need to do it with just one select statement you can use something like this code below.

INSERT INTO OUTPUT (Date) SELECT Date FROM REPORTS GROUP BY 1; 
UPDATE OUTPUT SET PREV_7 = (SELECT SUM(Reports) FROM REPORTS WHERE Date BETWEEN OUTPUT.Date -7 AND OUTPUT.Date);
UPDATE OUTPUT SET AFTER_7 = (SELECT SUM(Reports) FROM REPORTS WHERE Date BETWEEN OUTPUT.Date AND OUTPUT.Date + 7);
user_109923420
Related