Sum of all matching elements inside a time frame that changes according to the element in SQL-CodePudding

I searched a lot before asking this question here but couldn't find anything related to my problem.

I am currently trying to get a specific value (let's call it "sum in reference period") to be retrieved from my Oracle SQL database.

I have different elements that are linked to a group (e.g. element A is from Group 1, element B is from Group 2, element C is from Group 3, etc.) and each element has the date when it was created in the following format: YYYYMM.

How do I create an additional column with the sum of all elements that are from the same group as the element in the row and that were created in the last 12 months from the element creation?

Let's say my current table looks like this:

| Element_Id | Creation_Date | Group |
| A          | 202101        | 1     |
| B          | 202101        | 2     |
| C          | 202101        | 3     |
| D          | 202102        | 1     |
| E          | 202001        | 1     |
| F          | 202002        | 1     |
| G          | 202003        | 1     |

and what I want the result to be is:

| Element_Id | Creation_Date | Group | Sum_In_Reference_Period |
| A          | 202101        | 1     | 3                       |
| B          | 202101        | 2     | 1                       | 
| C          | 202101        | 3     | 1                       |
| D          | 202102        | 1     | 3                       |
| E          | 202001        | 1     | 1                       |
| F          | 202002        | 1     | 2                       |
| G          | 202003        | 1     | 3                       |

Thank you in advance.

CodePudding user response：

Use a windowed analytic function:

SELECT t.*,
       COUNT(*) OVER (
         PARTITION BY grp
         ORDER BY TO_DATE(Creation_Date || '01', 'YYYYMMDD')
         RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND INTERVAL '0' MONTH FOLLOWING
       ) AS sum_in_reference_period
FROM   table_name t
ORDER BY element_id

Which, for your sample data:

CREATE TABLE table_name (Element_Id, Creation_Date, Grp) AS
SELECT 'A', 202101, 1 FROM DUAL UNION ALL
SELECT 'B', 202101, 2 FROM DUAL UNION ALL
SELECT 'C', 202101, 3 FROM DUAL UNION ALL
SELECT 'D', 202102, 1 FROM DUAL UNION ALL
SELECT 'E', 202001, 1 FROM DUAL UNION ALL
SELECT 'F', 202002, 1 FROM DUAL UNION ALL
SELECT 'G', 202003, 1 FROM DUAL;

Outputs:

ELEMENT_ID CREATION_DATE GRP SUM_IN_REFERENCE_PERIOD

A 202101 1 3

B 202101 2 1

C 202101 3 1

D 202102 1 3

E 202001 1 1

F 202002 1 2

G 202003 1 3

ELEMENT_ID	CREATION_DATE	GRP	SUM_IN_REFERENCE_PERIOD
A	202101	1	3
B	202101	2	1
C	202101	3	1
D	202102	1	3
E	202001	1	1
F	202002	1	2
G	202003	1	3

db<>fiddle here