Medium: How to sum 3 month after.

[#1]

Dear all,
I have no idea how to do this.
Let say at present time we had 32 employees.
I had new employee join at 21-01-2010 at that time he been employed, after 3 month later (xx-04-2009) his number become active as donation blood as in the company total will be 33 employees.

I only need the month sum 3 after employment date, not care about date.
Let say If other person join at 29-04-2010 his will contribute after xx-07-2010 and total number will be 34.

How is correct formula for this.

LOAD
No_ as EE,
Employment Date as EmployDate,
Resignation Date as ResignDate,
Termination Date as TerminDate,
Inactive Date as InactvDate

SQL SELECT *
FROM ENSURE.dbo."SQLDATABASE$Employee";

Someone can help on this?

[spsrk_84 · Accepted solution]

Hi Frank,

As per my understanding of your u need to count the no of employees after three months of there joining i.e they will become active at that time . I got one solution for your query i.e

count the no of employess like this

count(if(AddMonths(EmpJoiningDate,3)<=Date(Today()),EMPID) this is one way another one is implement the same logic in the script such for each employee before three months w.r.t to currenth month will be indicated with 1 and rest as 0 and we will take the count of 1's

i.e

LOAD
No_ as EE,
Employment Date as EmployDate,

if(No_= Previous(No_) and addmonths(Employment Date ,3)<=Date(Today()),1,0) as ACTIVE_EMP_COUNT,
Resignation Date as ResignDate,
Termination Date as TerminDate,
Inactive Date as InactvDate

SQL SELECT *
FROM ENSURE.dbo."SQLDATABASE$Employee";

The above logic should work hope i have given the right solution for what your required

[#3]

You can use Count Function in the chart

=COUNT(DISTINCT IF(date(today(),'DD-MMM-YYYY')>=AddMonths([Employment Date],3),No_))

or use the if condition in load statement

LOAD
No_ as EE,
Employment Date as EmployDate,
Resignation Date as ResignDate,
Termination Date as TerminDate,
Inactive Date as InactvDate,

IF(date(today(),'DD-MMM-YYYY')>=AddMonths([Employment Date],3),1,0) as donate_flag;

SQL SELECT *
FROM ENSURE.dbo."SQLDATABASE$Employee";

Regards

Rajesh