How to get this

[alvinford]

Hi All,

Trying to get the following logic. I have the following details in table

i.e. Teacher ID, Teaching Code, Status , Start Date and End Date.

I want to exclude the Teacher ID's who are in Training (teaching Code A16,A17,A18)  when the Year or

Month of Teaching period  falls in between the Start Date and End Date is selected.

For Example : If Year 2013 and Month 4 is selected, distinct count(Teacher ID) should be 5. i.e. Teacher ID 50440 and 54240 to be excluded from the distinct count of Teacher ID as they are in the training.

Please find the attachment for your reference.

Regards,

Alvin.

[alvinford]

Hi Jan Gunnars,

Thank you for your reply.

Would like to achieve the logic from backend i.e. from the script.

Any help is greatly appreciated..

Regards,

Alvin.

[jan_gunnars]

Some parts of the code

First add all dates

Teaching_Dates:

Teaching:

//Add intervall when teacher have been teaching
if(match( [Teaching Code], 'A16','A17','A18') > 0,Date([Start Date],'YYYY-MM-DD')) as t_Start_Date,
if(match( [Teaching Code], 'A16','A17','A18') > 0,Date([End Date],'YYYY-MM-DD')) as t_End_Date

Then intervallMatch to add all teaching dates in intervalls

//Create table with teachers and their training dates. If a teacher hasn't been traing he/she is not in table.
Tmp_T:

//Recreate base table without training dates
Tmp_T2:

//Tmp_T and Tmp_T2 is connected with key [Teacher ID]