How to get the count of records by status by Month historically for reporting. Ex: Count of active

Anuswadh · Jun 16, 2022 12:53PM

I have a users table and a status change log table.

I want to get the count of active users by month.

If the user was active during the month, then the user should be included in the count.

Ex: if the user status was changed from active to inactive on January 2nd and the status was changed from inactive to active on March 23rd, then the user should be included in the count of active users for January and march but not in February.

I appreciate any help.

Thanks in advance.

Anuswadh

CREATE table "TBL_USERS" (

"ID" NUMBER,

"CLIENT_ID" NUMBER,

"NAME" VARCHAR2(4000),

"STATUS" VARCHAR2(4000),

constraint "TBL_USERS_PK" primary key ("ID")

)

/

insert into tbl_users(ID, CLIENT_ID, name, status) values (1,1,'test1', 'active');

insert into tbl_users(ID, CLIENT_ID, name, status) values (2,1,'test2', 'active');

insert into tbl_users(ID, CLIENT_ID, name, status) values (3,2,'test3', 'inactive');

CREATE table "TBL_USER_STATUS_CHANGES" (

"ID" NUMBER,

"CLIENT_ID" NUMBER,

"USER_ID" NUMBER,

"OLD_STATUS" VARCHAR2(4000),

"NEW_STATUS" VARCHAR2(4000),

"CHANGED_ON" DATE,

constraint "TBL_USER_STATUS_CHANGES_PK" primary key ("ID")

)

/

CREATE sequence "TBL_USER_STATUS_CHANGES_SEQ"

/

CREATE trigger "BI_TBL_USER_STATUS_CHANGES"

before insert on "TBL_USER_STATUS_CHANGES"

for each row

begin

if :NEW."ID" is null then

select "TBL_USER_STATUS_CHANGES_SEQ".nextval into :NEW."ID" from sys.dual;

end if;

end;

/

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (1,1,null,'active', to_date('01/01/2022','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (1,1,'active','inactive', to_date('02/02/2022','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (1,1,'inactive','active', to_date('04/02/2022','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (1,1,'active','inactive', to_date('04/22/2022','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (1,1,'inactive','active', to_date('06/04/2022','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (2,1,null,'active', to_date('01/01/2021','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (2,1,'active','inactive', to_date('02/02/2021','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (2,1,'inactive','active', to_date('03/02/2021','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (2,1,'active','inactive', to_date('05/22/2021','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (2,1,'inactive','active', to_date('09/24/2021','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (3,2,null,'active', to_date('09/01/2021','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (3,2,'active','inactive', to_date('01/02/2022','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (3,2,'inactive','active', to_date('05/02/2022','MM/DD/YYYY') );

insert into tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) values (3,2,'active','inactive', to_date('05/22/2022','MM/DD/YYYY') );

Solomon Yakobson · Jun 16, 2022 8:08PM

Well, if I got your requirements right:

with date_range as (
                    select  min(changed_on) min_dt,
                            max(changed_on) max_dt
                      from  tbl_user_status_changes
                   ),
          dates as (
                    select  min_dt + level - 1 dt
                      from  date_range
                      connect by min_dt + level - 1 <= max_dt
                   ),
   daily_status as (
                    select  d.dt,
                            t.client_id,
                            t.user_id,
                            nvl(
                                last_value(t.new_status) ignore nulls
                                  over(partition by t.client_id,t.user_id order by d.dt),
                                'inactive'
                               ) status
                      from      dates d
                            left join
                                tbl_user_status_changes t
                              partition by(t.client_id,t.user_id)
                              on d.dt = t.changed_on
                   ),
 monthly_status as (
                    select  to_char(dt,'yyyy') year,
                            to_char(dt,'MON') mon,
                            client_id,
                            user_id,
                            max(
                                case status
                                  when 'active' then 1
                                  else 0
                                end
                               ) status
                      from  daily_status
                      group by to_char(dt,'yyyy'),
                               to_char(dt,'MON'),
                               client_id,
                               user_id
              )
select  client_id,
        year,
        sum(
            case
              when mon = 'JAN' then status
              else 0
            end
           ) jan,
        sum(
            case
              when mon = 'FEB' then status
              else 0
            end
           ) feb,
        sum(
            case
              when mon = 'MAR' then status
              else 0
            end
           ) mar,
        sum(
            case
              when mon = 'APR' then status
              else 0
            end
           ) apr,
        sum(
            case
              when mon = 'MAY' then status
              else 0
            end
           ) may,
        sum(
            case
              when mon = 'JUN' then status
              else 0
            end
           ) jun,
        sum(
            case
              when mon = 'JUL' then status
              else 0
            end
           ) jul,
        sum(
            case
              when mon = 'AUG' then status
              else 0
            end
           ) aug,
        sum(
            case
              when mon = 'SEP' then status
              else 0
            end
           ) sep,
        sum(
            case
              when mon = 'OCT' then status
              else 0
            end
           ) oct,
        sum(
            case
              when mon = 'NOV' then status
              else 0
            end
           ) nov,
        sum(
            case
              when mon = 'DEC' then status
              else 0
            end
           ) dec
  from  monthly_status
  group by client_id,
           year
  order by client_id,
           year
/

 CLIENT_ID YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
---------- ---- --- --- --- --- --- --- --- --- --- --- --- ---
         1 2021   1   1   1   1   1   0   0   0   1   1   1   1
         1 2022   2   2   1   2   1   2   0   0   0   0   0   0
         2 2021   0   0   0   0   0   0   0   0   1   1   1   1
         2 2022   1   0   0   0   1   0   0   0   0   0   0   0

SQL>

SY.

Frank Kulash · Jun 16, 2022 2:19PM

Hi, @Anuswadh

Thanks for posting the sample data and results. Are you sure the results you posted are correct for that sample data? for example, wasn't client 1 active in January, 2021?

I want to get the count of active users by month.

Do you want active users or active clients? If you really want active users, don't you want to see user_id somewhere in the results?

Are the tbl_users table and the old_status column in the tbl_user_status_changes table needed in this problem? Point out where the results don't depend entirely on the other columns of the tbl_user_status_changes table.

Anuswadh · Jun 16, 2022 2:59PM

Hi @Frank Kulash ,

You are right.

It is my mistake. I updated the image of the results.

Thanks for catching it.

Thanks,

Anuswadh

Solomon Yakobson · Jun 16, 2022 4:02PM

Still not clear. What you posted doesn't match how I understood the requirements. Client 1, for example:

SQL> select * from tbl_user_status_changes where client_id = 1 order by changed_on;

        ID  CLIENT_ID    USER_ID OLD_STATUS NEW_STATUS CHANGED_O
---------- ---------- ---------- ---------- ---------- ---------
         6          1          2            active     01-JAN-21
         7          1          2 active     inactive   02-FEB-21
         8          1          2 inactive   active     02-MAR-21
         9          1          2 active     inactive   22-MAY-21
        10          1          2 inactive   active     24-SEP-21
         1          1          1            active     01-JAN-22
         2          1          1 active     inactive   02-FEB-22
         3          1          1 inactive   active     02-APR-22
         4          1          1 active     inactive   22-APR-22
         5          1          1 inactive   active     04-JUN-22

10 rows selected.

SQL>

So 2021 should have jan - 1 (active), feb - 0 (inactive), mar - 1 (active), apr - 1 (active - derived from mar), may - 0 (incative), jun, jul, aug - 0 (inactive - derived from may), sep - 1 (active), oct, nov, dec - 1 (active - derived from sep). Please explain.

SY.

Frank Kulash · Jun 16, 2022 4:05PM

Hi, @Anuswadh

I updated the image of the results.

Please don't change your messages after you post them: it makes the thread hard to read and you changes easy to miss. Post all corrections and additions in a new message at the end of the thread.

Anuswadh · Jun 16, 2022 5:03PM

Hi @Franck N , @Solomon Yakobson

Sorry for the confusion

I want to get total active users per client per month

I posted the wrong image and I corrected it

Solomon Yakobson · Jun 16, 2022 8:08PM

Well, if I got your requirements right:

with date_range as (
                    select  min(changed_on) min_dt,
                            max(changed_on) max_dt
                      from  tbl_user_status_changes
                   ),
          dates as (
                    select  min_dt + level - 1 dt
                      from  date_range
                      connect by min_dt + level - 1 <= max_dt
                   ),
   daily_status as (
                    select  d.dt,
                            t.client_id,
                            t.user_id,
                            nvl(
                                last_value(t.new_status) ignore nulls
                                  over(partition by t.client_id,t.user_id order by d.dt),
                                'inactive'
                               ) status
                      from      dates d
                            left join
                                tbl_user_status_changes t
                              partition by(t.client_id,t.user_id)
                              on d.dt = t.changed_on
                   ),
 monthly_status as (
                    select  to_char(dt,'yyyy') year,
                            to_char(dt,'MON') mon,
                            client_id,
                            user_id,
                            max(
                                case status
                                  when 'active' then 1
                                  else 0
                                end
                               ) status
                      from  daily_status
                      group by to_char(dt,'yyyy'),
                               to_char(dt,'MON'),
                               client_id,
                               user_id
              )
select  client_id,
        year,
        sum(
            case
              when mon = 'JAN' then status
              else 0
            end
           ) jan,
        sum(
            case
              when mon = 'FEB' then status
              else 0
            end
           ) feb,
        sum(
            case
              when mon = 'MAR' then status
              else 0
            end
           ) mar,
        sum(
            case
              when mon = 'APR' then status
              else 0
            end
           ) apr,
        sum(
            case
              when mon = 'MAY' then status
              else 0
            end
           ) may,
        sum(
            case
              when mon = 'JUN' then status
              else 0
            end
           ) jun,
        sum(
            case
              when mon = 'JUL' then status
              else 0
            end
           ) jul,
        sum(
            case
              when mon = 'AUG' then status
              else 0
            end
           ) aug,
        sum(
            case
              when mon = 'SEP' then status
              else 0
            end
           ) sep,
        sum(
            case
              when mon = 'OCT' then status
              else 0
            end
           ) oct,
        sum(
            case
              when mon = 'NOV' then status
              else 0
            end
           ) nov,
        sum(
            case
              when mon = 'DEC' then status
              else 0
            end
           ) dec
  from  monthly_status
  group by client_id,
           year
  order by client_id,
           year
/

 CLIENT_ID YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
---------- ---- --- --- --- --- --- --- --- --- --- --- --- ---
         1 2021   1   1   1   1   1   0   0   0   1   1   1   1
         1 2022   2   2   1   2   1   2   0   0   0   0   0   0
         2 2021   0   0   0   0   0   0   0   0   1   1   1   1
         2 2022   1   0   0   0   1   0   0   0   0   0   0   0

SQL>

SY.

Stax · Jun 17, 2022 7:30AM

Hi, SY

with tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) as (

select 1,1,null,'active', to_date('01/01/2022','MM/DD/YYYY') from dual union all

select 1,1,'active','inactive', to_date('02/02/2022','MM/DD/YYYY') from dual union all

select 1,1,'inactive','active', to_date('04/02/2022','MM/DD/YYYY') from dual union all

select 1,1,'active','inactive', to_date('04/22/2022','MM/DD/YYYY') from dual union all

select 1,1,'inactive','active', to_date('06/04/2022','MM/DD/YYYY') from dual union all

--

select 2,1,null,'active', to_date('01/01/2021','MM/DD/YYYY') from dual union all

select 2,1,'active','inactive', to_date('02/02/2021','MM/DD/YYYY') from dual union all

select 2,1,'inactive','active', to_date('03/02/2021','MM/DD/YYYY') from dual union all

select 2,1,'active','inactive', to_date('05/22/2021','MM/DD/YYYY') from dual union all

select 2,1,'inactive','active', to_date('09/24/2021','MM/DD/YYYY') from dual

)

,date_range as (

....

130 from monthly_status

131 group by client_id,

132 year

133 order by client_id,

134* year

SQL> /

CLIENT_ID YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC

---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

1 2021 0 0 0 0 0 0 0 0 0 0 0 0

1 2022 0 0 0 0 0 0 0 0 0 0 0 0

SQL>

Stax · Jun 17, 2022 7:35AM

Modify SY script

(status not change more one year)

with

tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) as (

/*

select 1,1,null,'active', to_date('01/01/2022','MM/DD/YYYY') from dual union all

select 1,1,'active','inactive', to_date('02/02/2022','MM/DD/YYYY') from dual union all

select 1,1,'inactive','active', to_date('04/02/2022','MM/DD/YYYY') from dual union all

select 1,1,'active','inactive', to_date('04/22/2022','MM/DD/YYYY') from dual union all

select 1,1,'inactive','active', to_date('06/04/2022','MM/DD/YYYY') from dual union all

--

select 2,1,null,'active', to_date('01/01/2021','MM/DD/YYYY') from dual union all

select 2,1,'active','inactive', to_date('02/02/2021','MM/DD/YYYY') from dual union all

select 2,1,'inactive','active', to_date('03/02/2021','MM/DD/YYYY') from dual union all

select 2,1,'active','inactive', to_date('05/22/2021','MM/DD/YYYY') from dual union all

select 2,1,'inactive','active', to_date('09/24/2021','MM/DD/YYYY') from dual

*/

select 1,1,null,'active', to_date('03/03/2020','MM/DD/YYYY') from dual union all

select 1,1,'active','inactive', to_date('02/02/2022','MM/DD/YYYY') from dual union all

select 1,1,'inactive','active', to_date('04/02/2022','MM/DD/YYYY') from dual union all

--

select 2,1,null,'active', to_date('03/03/2021','MM/DD/YYYY') from dual union all

select 2,1,'active','inactive', to_date('06/02/2021','MM/DD/YYYY') from dual union all

--

select 2,2,null,'active', to_date('04/01/2021','MM/DD/YYYY') from dual union all

select 2,2,'active','inactive', to_date('02/02/2022','MM/DD/YYYY') from dual

)

,date_range as (

select min(changed_on) min_dt,

max(changed_on) max_dt

from tbl_user_status_changes

)

,dates as (

select min_dt + level - 1 dt

from date_range

connect by min_dt + level - 1 <= max_dt

)

,u_ch as (

select t.*

,lead(changed_on,1,date '2099-12-31') over (partition by USER_ID, CLIENT_ID order by changed_on) ch_off

from tbl_user_status_changes t

)

,daily_status as (

select --d.dt,

t.client_id,

t.user_id,

to_char(dt,'yyyy') year,

to_char(dt,'mm') mon,

max(decode(new_status,'active',1,0)) status

from dates d

,u_ch t

where d.dt >= t.changed_on and d.dt<t.ch_off

group by to_char(dt,'yyyy'),

to_char(d.dt,'mm'),

client_id,

user_id

)

select client_id,

year,

--pilot

sum(case when mon = '01' then status else 0 end) m01,

sum(case when mon = '02' then status else 0 end) m02,

sum(case when mon = '03' then status else 0 end) m03,

sum(case when mon = '04' then status else 0 end) m04,

sum(case when mon = '05' then status else 0 end) m05,

sum(case when mon = '06' then status else 0 end) m06,

sum(case when mon = '07' then status else 0 end) m07,

sum(case when mon = '08' then status else 0 end) m08,

sum(case when mon = '09' then status else 0 end) m09,

sum(case when mon = '10' then status else 0 end) m10,

sum(case when mon = '11' then status else 0 end) m11,

sum(case when mon = '11' then status else 0 end) m12

from daily_status

group by client_id,

year

order by client_id,

year

/

SQL> /

CLIENT_ID YEAR M01 M02 M03 M04 M05 M06 M07 M08 M09 M10 M11 M12

---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

1 2020 0 0 1 1 1 1 1 1 1 1 1 1

1 2021 1 1 2 2 2 2 1 1 1 1 1 1

1 2022 1 1 0 1 0 0 0 0 0 0 0 0

2 2021 0 0 0 1 1 1 1 1 1 1 1 1

2 2022 1 1 0 0 0 0 0 0 0 0 0 0

SQL>

Solomon Yakobson · Jun 17, 2022 1:07PM

@Stax:

CLIENT_ID YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC

---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------

1 2021 0 0 0 0 0 0 0 0 0 0 0 0

1 2022 0 0 0 0 0 0 0 0 0 0 0 0

What version are you using? I can't reproduce it in 12C and in 19C:

with tbl_user_status_changes ( USER_ID, CLIENT_ID, old_status, new_status, changed_on) as (
select 1,1,null,'active', to_date('01/01/2022','MM/DD/YYYY') from dual union all
select 1,1,'active','inactive', to_date('02/02/2022','MM/DD/YYYY') from dual union all
select 1,1,'inactive','active', to_date('04/02/2022','MM/DD/YYYY') from dual union all
select 1,1,'active','inactive', to_date('04/22/2022','MM/DD/YYYY') from dual union all
select 1,1,'inactive','active', to_date('06/04/2022','MM/DD/YYYY') from dual union all
--
select 2,1,null,'active', to_date('01/01/2021','MM/DD/YYYY') from dual union all
select 2,1,'active','inactive', to_date('02/02/2021','MM/DD/YYYY') from dual union all
select 2,1,'inactive','active', to_date('03/02/2021','MM/DD/YYYY') from dual union all
select 2,1,'active','inactive', to_date('05/22/2021','MM/DD/YYYY') from dual union all
select 2,1,'inactive','active', to_date('09/24/2021','MM/DD/YYYY') from dual 
),
date_range as (
                    select  min(changed_on) min_dt,
                            max(changed_on) max_dt
                      from  tbl_user_status_changes
                   ),
          dates as (
                    select  min_dt + level - 1 dt
                      from  date_range
                      connect by min_dt + level - 1 <= max_dt
                   ),
   daily_status as (
                    select  d.dt,
                            t.client_id,
                            t.user_id,
                            nvl(
                                last_value(t.new_status) ignore nulls
                                  over(partition by t.client_id,t.user_id order by d.dt),
                                'inactive'
                               ) status
                      from      dates d
                            left join
                                tbl_user_status_changes t
                              partition by(t.client_id,t.user_id)
                              on d.dt = t.changed_on
                   ),
 monthly_status as (
                    select  to_char(dt,'yyyy') year,
                            to_char(dt,'MON') mon,
                            client_id,
                            user_id,
                            max(
                                case status
                                  when 'active' then 1
                                  else 0
                                end
                               ) status
                      from  daily_status
                      group by to_char(dt,'yyyy'),
                               to_char(dt,'MON'),
                               client_id,
                               user_id
              )
select  client_id,
        year,
        sum(
            case
              when mon = 'JAN' then status
              else 0
            end
           ) jan,
        sum(
            case
              when mon = 'FEB' then status
              else 0
            end
           ) feb,
        sum(
            case
              when mon = 'MAR' then status
              else 0
            end
           ) mar,
        sum(
            case
              when mon = 'APR' then status
              else 0
            end
           ) apr,
        sum(
            case
              when mon = 'MAY' then status
              else 0
            end
           ) may,
        sum(
            case
              when mon = 'JUN' then status
              else 0
            end
           ) jun,
        sum(
            case
              when mon = 'JUL' then status
              else 0
            end
           ) jul,
        sum(
            case
              when mon = 'AUG' then status
              else 0
            end
           ) aug,
        sum(
            case
              when mon = 'SEP' then status
              else 0
            end
           ) sep,
        sum(
            case
              when mon = 'OCT' then status
              else 0
            end
           ) oct,
        sum(
            case
              when mon = 'NOV' then status
              else 0
            end
           ) nov,
        sum(
            case
              when mon = 'DEC' then status
              else 0
            end
           ) dec
  from  monthly_status
  group by client_id,
           year
  order by client_id,
           year
/

 CLIENT_ID YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
---------- ---- --- --- --- --- --- --- --- --- --- --- --- ---
         1 2021   1   1   1   1   1   0   0   0   1   1   1   1
         1 2022   2   2   1   2   1   2   0   0   0   0   0   0

SQL> select version from v$instance
  2  /

VERSION
-----------------
12.2.0.1.0

SQL>...

 CLIENT_ID YEAR JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
---------- ---- --- --- --- --- --- --- --- --- --- --- --- ---
         1 2021   1   1   1   1   1   0   0   0   1   1   1   1
         1 2022   2   2   1   2   1   2   0   0   0   0   0   0


SQL> select version_full from v$instance
  2  /

VERSION_FULL
-----------------
19.13.0.0.0

SQL>

SY.

Stax · Jun 17, 2022 1:52PM

SY,

SORRY 😚, NLS

SQL> select to_char(sysdate,'MON') mm from dual

2 /

MM

---

ЧЕР

Forum Stats

Quick Links

Categories

How to get the count of records by status by Month historically for reporting. Ex: Count of active

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

How to get the count of records by status by Month historically for reporting. Ex: Count of active

Best Answer

Answers