Click here to Skip to main content
14,392,972 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have tried to calculate cumulative sum column to find out Present Working Employees in each month, but am getting NULL instead of present employee as per previous month.

Table employees:

id    date_started     date_terminated
    1      01-Apr-14       NULL
    2      21-Apr-14       NULL
    3      11-Apr-14       NULL
    4      01-Apr-14       NULL
    5      01-Apr-14       NULL
    6      05-Apr-14       NULL
    7      01-Apr-14       NULL
    8      01-Apr-14       NULL
    9      01-Apr-14       NULL
    10     29-Apr-14       NULL
    11     21-Apr-14       NULL
    12     01-Apr-14       NULL
    13     01-Apr-14       NULL
    14     01-Apr-14       NULL
    15     05-Aug-14       NULL
    16     01-Oct-1        NULL
    17     13-Oct-14       NULL
    18     22-Oct-14       NULL
    19     25-Oct-14       NULL
    10     29-Oct-14       NULL




Table dates: It containing `date` column which having data from `2011-Jan-01` to current date.

Obtained result Table from my query :

+--------------------------------------------------------------+
    | date                  | employee_joined | present_employees  |
    +--------------------------------------------------------------+
    | 2014-01-01 00:00:00-7 |            NULL |              NULL  |
    | 2014-02-01 00:00:00-7 |            NULL |              NULL  |
    | 2014-03-01 00:00:00-7 |            NULL |              NULL  |
    | 2014-04-01 00:00:00-7 |              14 |                14  |
    | 2014-05-01 00:00:00-7 |            NULL |              NULL  |
    | 2014-06-01 00:00:00-7 |            NULL |              NULL  |
    | 2014-07-01 00:00:00-7 |            NULL |              NULL  |
    | 2014-08-01 00:00:00-7 |               1 |                15  |
    | 2014-09-01 00:00:00-7 |            NULL |              NULL  |
    | 2014-10-01 00:00:00-7 |               5 |                20  |
    +--------------------------------------------------------------+


I am looking for resultant table:

+--------------------------------------------------------------+
    | date                  | employee_joined | present_employees  |
    +--------------------------------------------------------------+
    | 2014-01-01 00:00:00-7 |            NULL |              NULL  |
    | 2014-02-01 00:00:00-7 |            NULL |              NULL  |
    | 2014-03-01 00:00:00-7 |            NULL |              NULL  |
    | 2014-04-01 00:00:00-7 |            2264 |              2264  |
    | 2014-05-01 00:00:00-7 |            NULL |              2264  |
    | 2014-06-01 00:00:00-7 |            NULL |              2264  |
    | 2014-07-01 00:00:00-7 |            NULL |              2264  |
    | 2014-08-01 00:00:00-7 |               1 |              2265  |
    | 2014-09-01 00:00:00-7 |            NULL |              2265  |
    | 2014-10-01 00:00:00-7 |               5 |              2270  |
    +--------------------------------------------------------------+


I have tried to get data from below query:


/*-----ONLY FOR PRESENT EMPLOYEES--------*/
    WITH fdates AS 
    	(
            SELECT DATE_TRUNC('month', d.date) AS date
            FROM dates d
            WHERE d.date::DATE <= '10-01-2014' AND
            d.date::DATE >= '01-01-2014'
            group by DATE_TRUNC('month', d.date)
    	),  
    employeeJoin AS
        (
            SELECT COALESCE( COUNT(e.id), 0 ) AS employee_joined, 
                DATE_TRUNC( 'month', e.date_started) AS date_started
            FROM employees e GROUP BY DATE_TRUNC( 'month', e.date_started)
        ),
    employeeJoinRownum AS
        (	
            SELECT employee_joined, date_started, row_number() OVER (order by date_started) rownum
            FROM employeeJoin
        ) 
    SELECT d.*, employee_joined AS employee_joined,
    		(SELECT sum(employee_joined) FROM employeeJoinRownum eJ2 WHERE eJ2.rownum <= eJ1.rownum) AS Total_Joined_Employees
        FROM fdates d
        LEFT OUTER JOIN employeeJoinRownum eJ1 ON( eJ1.date_started = DATE_TRUNC('month', d.date) )
        ORDER BY d.date
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Hi,

You could use the self join on the employees table after grouping up all the employess by date .
something like below

select date_started,COUNT(1) NumberOfEmployeejoined
Into #table2
from employees
Group by date_started

select e1.date_started,SUM(E2.NumberOfEmployeejoined)
from #table2 e1 Join #table2 e2 On E1.date_started>=E2.date_started
Group by e1.date_started
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100