How do I get a rolling view of counts?

Question

Goal is to count anyone who fits a criteria on three months back from specified date. The (BetweenDate -3 months) is the tricky part. I am operating within a yearly window not 3 months back from getDate() I need it to be three months back from within -3 months of Y. Any ideas?

CREATE TABLE MONTH3LOOK AS Select 
to_CHAR(DATE_OF_SERVICE_3013,'YYYY-MM') "Date"

,COUNT(DISTINCT case when (regexp_instr(IS_CONCAT,'(2957|29570|29571|29572|29573|29574|29575|29576|29577|29578|29579)')>0)
 and 
 (DATE_OF_SERVICE_3013 between trunc(DATE_OF_SERVICE_3013,'MM') and add_months(trunc(DATE_OF_SERVICE_3013,'MM'),-3))
then USER end) AS Recip


FROM .NET_SERVICE
WHERE DATE_OF_SERVICE_3013 BETWEEN 
TO_DATE('2013-10','YYYY-MM') AND 
TO_DATE('2014-03','YYYY-MM')

group by to_CHAR(DATE_OF_SERVICE_3013,'YYYY-MM')

Provide some sample data and expected output and we will try to help. — FutbolFan, Sep 09 '15 at 17:36
The sample data would be rolled up in the rolling month. So USER COLUMN would be X( where X = users who have had IS_CODE in the last three months) — Tinkinc, Sep 09 '15 at 17:41
It appears that you almost never accept any answers based on your previous questions. Also, that doesn't make any sense, please provide sample data (explaining your situation) that would help other understand your issue. Good luck! — FutbolFan, Sep 09 '15 at 17:48

score 0 · Answer 1 · answered Sep 09 '15 at 21:44

You will likely need to use analytic functions to get your counts and the distinct operator to simulate the group by since including the group by operator interferes with the operation of the analytic functions:

select distinct trunc(date_of_service_3013,'MM') "Date"
     , count(case when regexp_like(IS_CONCAT, '(1234|5678|etc)') then user end)
       over (order by trunc(date_of_service_3013, 'mm') 
             range between interval '3' month preceding 
                       and current row) recip
  from your_table
 where DATE_OF_SERVICE_3013 BETWEEN TO_DATE('2013-10','YYYY-MM')
                                AND TO_DATE('2014-03','YYYY-MM');

Another way to take the effect of the group by operation into account is to change use both analytic and aggregate functions:

select trunc(date_of_service_3013,'MM') "Date"
     , sum(count(case when regexp_like(IS_CONCAT, '1234|5678|etc') then user end))
       over (order by trunc(date_of_service_3013, 'mm') 
             range between interval '3' month preceding 
                       and current row) recip
  from your_table
  group by trunc(date_of_service_3013,'MM')
 where DATE_OF_SERVICE_3013 BETWEEN TO_DATE('2013-10','YYYY-MM')
                                AND TO_DATE('2014-03','YYYY-MM');

Here the aggregate count works on a month by month basis as per the group by clause, then it uses the analytic sum to add up those counts.

One thing about these two solutions, the where clause will prevent any records prior to 2013-10 from being counted. If you want to include records prior to 2013-10 in the counts but only output 2013-10 to 2014-03 then you'll need to do it in two stages using either of the two queries above inside the with t1 as (...) subfactored query block with the starting date adjusted appropriately:

with t1 as (
select distinct trunc(date_of_service_3013,'MM') "Date"
     , count(case when regexp_like(IS_CONCAT, '1234|5678|etc') then user end)
       over (order by trunc(date_of_service_3013, 'mm') 
             range between interval '3' month preceding 
                       and current row) recip
  from your_table
 where DATE_OF_SERVICE_3013 BETWEEN TO_DATE('2013-07','YYYY-MM')
                                AND TO_DATE('2014-03','YYYY-MM')
)
select * from t1 where "Date" >= TO_DATE('2013-10','YYYY-MM');

How do I get a rolling view of counts?

1 Answers1