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:
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