Click here to Skip to main content
15,917,177 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:


SQL
/*-----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

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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900