11 Replies Latest reply: Jan 13, 2014 5:06 AM by Rahul_India RSS

    Need a rolling count of rows from table using just SQL

    tx103108

      Oracle 11gR2

      RHEL 6.4

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

       

      Given the following table data:

       

      EMPLOYEE     HIRE_DATE     TERM_DATE     DEPT

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

      John Doe          01/01/13                                   101

      Jane Smith       01/05/13                                   102

      Bob Jones        02/04/13          04/22/13             102

      Jenny Boo        03/12/13          03/31/13             103

      Joe Schmoe     03/24/13                                    102

      Bill Max            04/23/13                                   103

      Jill Clay            04/24/13                                   103

      Joe Boom         05/11/13                                   102

       

      I want to return the number of employees that are still employed for each month so long as they were hired anytime in that month and irregardless if they were terminated later that month (i.e only the month concerns me).  So I would be expecting

       

      MONTH  EMPLOYEES

      Jan         2

      Feb        3

      Mar        5

      Apr        6   (Jenny Boo has been terminated)

      May       6   (Jenny Boo and Bob Jones have been terminated)  

       

      I know there are some bright people out there that are SQL experts but I am not one of them.  If there is a way to do this in just SQL I would like to see it.

        • 1. Re: Need a rolling count of rows from table using just SQL
          Frank Kulash

          Hi,

           

          Construct a result set that contains 1 row for every month that you want in the output.  (I'm not sure what months those are; you can either specify them in the query, or derive them from the table).  Let's call that result set all_months.  Give it 2 DATE columns: this_month (the first day of the month) and next_month (the first day of the following month).

          Once you have that result set, join it to your table, something like this

           

          FROM             all_months  m

          LEFT OUTER JOIN  table_x     t   ON   m.this_month   <= NVL ( t.term_date

                                                                      , DATE '9999-12-31'

                                                                      )

                                           AND  m.next_month   > t.hire_date

          and GROUP BY m.this_month.

           

          Depending on you data and your requirements, a plain old inner join may do as well as an outer join.

           

          I assume that a NULL term_date means the employee is counted as working in any month on or after hire_date.

           

          I hope this answers your question.

          If not, post  a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.

          Explain, using specific examples, how you get those results from that data.  In particular, explain what months should be covered by the output.

          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

          See the forum FAQ: https://forums.oracle.com/message/9362002

          • 2. Re: Need a rolling count of rows from table using just SQL
            Martin Preiss

            there are certainly simpler solutions but it's late and I am tired:

            create table emp1 (

                EMPLOYEE varchar2(20)

              , HIRE_DATE date

              , TERM_DATE date

              , DEPT number

            );

             

            insert into emp1 values ('John Doe',   to_date('01/01/13', 'mm/dd/yy'), NULL, 101);

            insert into emp1 values ('Jane Smith', to_date('01/05/13', 'mm/dd/yy'), NULL, 102);

            insert into emp1 values ('Bob Jones',  to_date('02/04/13', 'mm/dd/yy'), to_date('04/22/13', 'mm/dd/yy'), 102);

            insert into emp1 values ('Jenny Boo',  to_date('03/12/13', 'mm/dd/yy'), to_date('03/31/13', 'mm/dd/yy'), 103);

            insert into emp1 values ('Joe Schmoe', to_date('03/24/13', 'mm/dd/yy'), NULL, 102);

            insert into emp1 values ('Bill Max',   to_date('04/23/13', 'mm/dd/yy'), NULL, 103);

            insert into emp1 values ('Jill Clay',  to_date('04/24/13', 'mm/dd/yy'), NULL, 103);

            insert into emp1 values ('Joe Boom',   to_date('05/11/13', 'mm/dd/yy'), NULL, 102);

             

            with

            date_range as (

            select trunc(min(hire_date), 'mm') ref_min

                 , max(hire_date) ref_max

              from emp1

            )

            ,

            ref_dates_generator as (

            select add_months((select ref_min from date_range), -1 + rownum) ref_month

              from dual

            connect by level <= 1000

            )

            ,

            ref_dates as (

            select ref_month

              from ref_dates_generator

            where ref_month <= (select ref_max from date_range)

            )

            ,

            emp_hired as (

            select trunc(hire_date, 'mm') hire_date_trunc

                 , count(*) hired

              from emp1

            group by trunc(hire_date, 'mm')

            )

            ,

            emp_terminated as (

            select add_months(trunc(term_date, 'mm'), 1) term_date_trunc

                 , count(*) terminated

              from emp1

            group by add_months(trunc(term_date, 'mm'), 1)

            )

            select to_char(ref_dates.ref_month, 'MON') month

                 , sum(emp_hired.hired - nvl(emp_terminated.terminated, 0)) over (order by ref_dates.ref_month) employees

              from ref_dates

              left outer join

                   emp_hired

               on (ref_dates.ref_month = emp_hired.hire_date_trunc)

              left outer join

                   emp_terminated

               on (ref_dates.ref_month = emp_terminated.term_date_trunc)

            order by ref_dates.ref_month;

             

            MON  EMPLOYEES

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

            JAN          2

            FEB          3

            MRZ          5

            APR          6

            MAI          6

            • 3. Re: Need a rolling count of rows from table using just SQL
              Solomon Yakobson

              Assuming you want count just for months where someone was hired or terminated:


              with t as (

                          select  trunc(hire_date,'mm') dt,

                                  1 weight

                            from  emp1

                         union all

                          select  last_day(term_date) + 1 dt,

                                  -1 weight

                            from  emp1

                            where term_date is not null

                        )

              select  to_char(dt,'FMMonth, YYYY') month,

                      sum(sum(weight)) over(order by dt) employees

                from  t

                group by dt

                order by dt

              /


              MONTH            EMPLOYEES
              --------------- ----------
              January, 2013            2
              February, 2013           3
              March, 2013              5
              April, 2013              6
              May, 2013                6

              SQL>


              If you want all months within given range:

               

              with r as (

                         select  date '2013-03-01' from_dt,

                                 date '2013-12-01' to_dt

                           from  dual

                        ),

                   t as (

                          select  trunc(hire_date,'mm') dt,

                                  1 weight,

                                  from_dt

                            from  emp1,

                                  r

                            where hire_date <= last_day(to_dt)

                         union all

                          select  last_day(term_date) + 1 dt,

                                  -1 weight,

                                  from_dt

                            from  emp1,

                                  r

                             where last_day(term_date) + 1 <= to_dt

                         union all

                          select  add_months(from_dt,level - 1) dt,

                                  0 weight,

                                  from_dt

                            from  r

                            connect by add_months(from_dt,level - 1) <= to_dt

                        ),

                   s as (

                         select  dt,

                                 sum(sum(weight)) over(order by dt) employees,

                                 from_dt

                           from  t

                           group by dt,

                                 from_dt

                        )

              select  to_char(dt,'FMMonth, YYYY') month,

                      employees

                from  s

                where dt >= from_dt

                order by dt

              /


              MONTH            EMPLOYEES
              --------------- ----------
              March, 2013              5
              April, 2013              6
              May, 2013                6
              June, 2013               6
              July, 2013               6
              August, 2013             6
              September, 2013          6
              October, 2013            6
              November, 2013           6
              December, 2013           6

              10 rows selected.

              SQL>

               


              SY.

              • 4. Re: Need a rolling count of rows from table using just SQL
                Stew Ashton

                Here is a compact solution that only shows months when someone was hired or fired. The data is "sparse" with respect to the months (although your sample data doesn't show that). Oracle does the UNPIVOT, then the SUM with GROUP BY, and lastly the analytical SUM, so it is SUMming a sum. Solomon did basically the same thing, but used UNION ALL instead of UNPIVOT.

                 

                select to_char(mon,'FMMonth, YYYY') month,
                sum(sum(cnt)) over(order by mon) employees
                from (
                  select trunc(hire_date, 'MM') hire_month,
                  trunc(add_months(term_date,1), 'MM') gone_month
                  from emp1
                ) a
                unpivot(mon for cnt in (hire_month as 1, gone_month as -1))
                group by mon
                order by mon;
                

                 

                 

                MONTHEMPLOYEES
                January, 20132
                February, 20133
                March, 20135
                April, 20136
                May, 20136

                 

                If you want to show every month, that is called "dense" data.

                 

                with dates as (
                  select trunc(min(hire_date), 'MM') min_date,
                  trunc(add_months(max(coalesce(term_date, hire_date)), 1), 'MM') max_date
                  from emp1
                ),
                date_range as (
                  select add_months(min_date, level-1) mon
                  from dates
                  connect by add_months(min_date, level-1) <= max_date
                )
                select to_char(mon,'FMMonth, YYYY') month,
                sum(sum(cnt)) over(order by mon) employees
                from (
                  select trunc(hire_date, 'MM') hire_month,
                  trunc(add_months(term_date,1), 'MM') gone_month
                  from emp1
                )
                unpivot(mon for cnt in (hire_month as 1, gone_month as -1))
                right join date_range using(mon)
                group by mon
                order by mon;
                

                 

                 

                MONTHEMPLOYEES
                January, 20132
                February, 20133
                March, 20135
                April, 20136
                May, 20136
                June, 20136

                 

                Here Oracle is doing the UNPIVOT, then the JOIN, then the GROUP BY and lastly the analytical function.

                 

                Message was edited by: Stew Ashton -- I changed "1" and "-1" to 1 and -1 in the UNPIVOT clause, following an example by Frank Kulash in another thread. Thanks Frank!

                • 5. Re: Need a rolling count of rows from table using just SQL
                  tx103108

                  These are all such good answers.

                   

                  Solomon, if I wanted all dates between the min date in the table and the current date (sysdate), would just change your first temp table from:

                   

                  with r as (

                             select  date '2013-03-01' from_dt,

                                     date '2013-12-01' to_dt

                               from  dual

                            ),



                  to

                  with r as (

                             select  date min(date_in_table) from_dt,

                                     date sysdate to_dt

                               from  dual

                            ),


                  Would this also capture those months were no activity occurs, i.e., no one was hired or terminated so the count would be unchanged from the previous month and also still exist in the result set?


                  • 6. Re: Need a rolling count of rows from table using just SQL
                    Frank Kulash

                    Hi,

                     

                    Here's the outer join solution described in the 1st reply:

                    WITH    limits    AS

                    (

                        SELECT  TRUNC (MIN (hire_date), 'MONTH')    AS first_month

                        ,       TRUNC (SYSDATE,         'MONTH')    AS last_month

                        FROM    emp1

                    )

                    ,       all_months    AS

                    (

                        SELECT  ADD_MONTHS (first_month, LEVEL - 1) AS this_month

                        ,       ADD_MONTHS (first_month, LEVEL)     AS next_month

                        FROM    limits

                        CONNECT BY  LEVEL <= 1 + MONTHS_BETWEEN (last_month, first_month)

                    )

                    SELECT    TO_CHAR (m.this_month, 'fmMonth, YYYY')    AS month

                    ,         COUNT (*)                                  AS num_employees

                    FROM             all_months  m

                    LEFT OUTER JOIN  emp1        e   ON   m.this_month   <= NVL ( e.term_date

                                                                                , DATE '9999-12-31'

                                                                                )

                                                     AND  m.next_month   > e.hire_date

                    GROUP BY  m.this_month

                    ORDER BY  m.this_month

                    ;

                    As you can see, it includes all months from the first hire_date in the table up to the current month, whether or not there were any hires or terminations in that month:

                    MONTH           NUM_EMPLOYEES

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

                    January, 2013               2

                    February, 2013              3

                    March, 2013                 5

                    April, 2013                 6

                    May, 2013                   6

                    June, 2013                  6

                    July, 2013                  6

                    August, 2013                6

                    September, 2013             6

                    October, 2013               6

                    November, 2013              6

                    December, 2013              6

                    January, 2014               6

                    • 7. Re: Need a rolling count of rows from table using just SQL
                      tx103108

                      Hello Stew,

                      Thanks for your response.  In looking at your SQL, where did you get the column 'cnt'?  I don't see that in any table, physical or virtual.

                       

                      NOTE: disregard, I see my error...  :-(

                      • 8. Re: Need a rolling count of rows from table using just SQL
                        rp0428
                        Hello Stew,

                         

                        Thanks for your response.  In looking at your SQL, where did you get the column 'cnt'?  I don't see that in any table, physical or virtual.

                        It is in the 'unpivot' clause of the query that he posted.

                         

                        Create the 'emp1' table and data that Martin provided and then execute the query that Stew provided.

                         

                        Works for me:

                        select to_char(mon,'FMMonth, YYYY') month,

                        sum(sum(to_number(cnt))) over(order by mon) employees

                        from (

                          select trunc(hire_date, 'MM') "1",

                          trunc(add_months(term_date,1), 'MM') "-1"

                          from emp1

                          ) a

                          unpivot(mon for cnt in ("1", "-1"))

                        group by mon

                        order by mon;


                        Header 1Header 2

                        MONTH

                        EMPLOYEES
                        January, 2013 2
                        February, 2013 3
                        March, 2013 5
                        April, 2013 6
                        May, 2013 6
                        • 9. Re: Need a rolling count of rows from table using just SQL
                          Rahul_India

                          Hi Frank,

                          Al though i understood your solution ,Can you tell how to proceed on these type of questions.

                          • 10. Re: Need a rolling count of rows from table using just SQL
                            Stew Ashton

                            Rahul_India wrote:

                             

                            Hi Frank,

                            Al though i understood your solution ,Can you tell how to proceed on these type of questions.

                            I'm not Frank, but I'll tell you how I proceeded this time:

                            1. Decide exactly what is wanted. In this case, I didn't know if you wanted "dense" data (show all months) or "sparse" data (skip months where nothing happened). In what follows I will treat the "dense" option.
                            2. Decide on an algorithm. Here I thought: add 1 if an employee is hired this month, and subtract 1 if an employee left last month (if he left this month, he still counts). Then get a sum by month, then a running total of that sum.
                            3. Implement the algorithm in SQL, one step at a time.
                              • First I TRUNCATEd all the dates to the first day of the month, and assigned column names of "1" for hire_date and "-1" for term_date. I made term_date the following month since the employee is still considered present the month he leaves.
                              • put all the dates in the same column with UNPIVOT. The column names "1" and "-1" now become values in column CNT, so I can convert them to numbers and sum them.
                              • SUM the CNT column, grouping by date
                              • Use the analytic SUM function to get a running total of my previous SUM.

                             

                            P.S. Thanks to RP for answering the question you asked me.

                            • 11. Re: Need a rolling count of rows from table using just SQL
                              Rahul_India

                              Hi Skew,

                              Thanks.That helps.I find issues with the implementation of the 3rd step.Maybe i will improve with experience.

                               

                              Message was edited by: Rahul_India