Ryan Barker Ryan Barker - 14 days ago 5
SQL Question

Oracle SQL - Summing a time series partition based on month

I am working with a data set that looks like this:

MTD | ID | Active
-----------------------
01-APR-16 | A | y
01-MAY-16 | A | y
01-JUN-16 | A | n
01-JUL-16 | A | y
01-AUG-16 | A | n
01-APR-16 | B | n
01-MAY-16 | B | y
01-JUN-16 | B | y
01-JUL-16 | B | y
01-AUG-16 | B | y


I would like to add a count column to the data set that counts the number of times an ID has been active ('y') AFTER the current MTD. The desired output is:

MTD | ID | Active | COUNT
-------------------------------
01-APR-16 | A | y | 2
01-MAY-16 | A | y | 1
01-JUN-16 | A | n | 1
01-JUL-16 | A | y | 0
01-AUG-16 | A | n | 0
01-APR-16 | B | n | 4
01-MAY-16 | B | y | 3
01-JUN-16 | B | y | 2
01-JUL-16 | B | y | 1
01-AUG-16 | B | y | 0


The query I am thinking of is:

SELECT
MTD,

ID,

ACTIVE,

SUM(CASE WHEN MTD > (current records MTD)
AND ACTIVE = 'y' THEN 1 ELSE 0 END)
OVER (PARTITION BY ID)
as COUNT


I'm not sure how to compare each record's MTD to the current record's MTD in the window sum. How can I amend the first line of the case statement?

Thank you,

Ryan Barker

vkp vkp
Answer

Use count() over() with a range specification so you look at the rows following the current row (for each id) for an active flag y and only count them. This assumes mtd is a date column for the ordering to work.

SELECT 
 MTD,
 ID, 
 ACTIVE,
 COUNT(case when active='y' then 1 end) OVER(partition by ID order by mtd range between 1 following and unbounded following)
FROM your_table

Sample Demo

Comments