Medium: Ratio for Foreign worker

[#1]

Dear all,
I really need help this condition with your expertise.
My company required me to do ratio for Foreign Worker.
Our company can hire/recruit foreign worker base from local worker ratio.
So the ratio should be like this:

Local Worker= 1
Foreign Worker= 7
Ratio= 1:7


And company have 3 type of industry:

Construction (Local= 5, NTS worker= 35)
Marine (Local= 2, NTS worker= 14)
Maintenance (Local= 10, NTS worker= 70)

Ratio will be taken from excel, reason easy to modify just in case there change ratio.
Each individual local worker will able starting contributing when after 3 month (not day) from employed date e.g. sample local employe below:

Status: Inactive
Name: Micheal Doe
EmploytDate: 15-02-2009
TerminationDate: 30-07-2009
*Contribute since xx-05-2009 until xx-10-2009 (No longer contribute)

Status: Inactive
Name: John Smith
EmployDate: 21-01-2008
ResignDate: 30-02-2010
*Contribute since xx-04-2008 until xx-06-2010 (Still-contribute)

Status: Inactive
Name: Marco Seth
EmployDate: 08-02-2009
ResignDate: 30-04-2010
*Contribute since xx-05-2009 until xx-07-2010 (still-contribute)

Status: Active
Name: Smith White
EmployDate: 12-08-2009
*Contribute since xx-11-2009 until preset

Status: Active
Name: Catherine
EmployDate: 01-04-2010
*Non-active-Contribute
*Contribute will start at xx-07-2010

TodayDate: 18-04-2010

Case like John and Marco still contribute even they still counts 3months after resign date or termination date.

Company did not see base on date but on 3 month.
And this 3 months not probation period, even probation period extended to 4month-6month not effected, still 3 month.

And we need to know each month table, how many total foreign worker company can available can recruit.

Such like this:

Industry |Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec|
-------------------------------------------------------------
Construction |
Marine |
Maintenance |

and my script look like this

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MMM-YYYY hh:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET Num='';
SET MONTH='MMM';

LET CurrentDate = Date(today(0));
LET CurrentMonth = Month(today(0));
LET CurrentYear = Year(today(0));

Employee:
LOAD
 "No_" as EE,
 "First Name",
 Date("Employment Date") as EmployDate,
 Month("Employment Date") as EmpMonth,
 Year("Employment Date") as EmpYear,

 Date("Termination Date") as TerminDate,
 Month("Termination Date") as TerminMonth,
 Year("Termination Date") as TerminYear,

 Date("Resignation Date") as ResignDate,
 Month("Resignation Date") as ResignMonth,
 Year("Resignation Date") as ResignYear;

SQL SELECT *
FROM ENSURE.dbo."DATABASE$Employee"

[#2]

Hi Frank,

If you could attach a sample application with some data that will be great.

Regards

Rajesh

[#3]

Dear Rajesh,
Actually is data is simple.

Loading from MySQL database.
It just like 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"
where ("No_" Between '2' and '5999');

Someone can help on this?

[#4]

Hi Frank,

Herewith i have attached the application for your reference. Pls post me if you find any difficulties.

Regards

Rajesh

[#5]

Hi Rajesh,
I still find difficulties...

Anyone can help about this? 😞

Frenky