Inserting total count of passed conditional expression to a column

[Anonymous]

Dear All,

I am having a Source table containing Current_Month,Date of Joining(DOJ) and Date of Separation(DOS).

And in Destination table I have head count, where i need to insert head count of employee based on below condition,

COUNT(((TalendDate.formatDate("MMM-yy", row1.DOJ)<=Current_Month) &&
(TalendDate.formatDate("MMM-yy", row1.DOS)>=Current_Month))?1:0)

I have used above expression on head count column in destination table. But it is not working. Could you please assist me, how to get solved.

Thank you in advance.

[Anonymous]

Where are you trying to use this expression? Is it being used in SQL or Java?

[TRF]

You cannot compare dates based on MMM as "OCT" will be considered as less than "SEP" but in fact it's greater (10 > 09).
Instead you should use TalendDate.compareDate().

[Anonymous]

@rhall @ I am trying to use it in expression builder function. If so its possible in java or sql. Could please let me know hownit can be approached

[Anonymous]

@TRF  yea understood. I can use TalendDate.compareDate() but later part of which i need to count number of passed condition. And insert that value in all rows of that column.

[Anonymous]

"Count" is not a Java method I am aware of, but it is a SQL function. If you are trying to use this in a database component, then you need to do this in a different way to if you are doing this in a tMap or tJava, for example. By the way, given the logic you have here with the 0 : 1 I assume that you want to use something like "Sum" rather than "Count" if this is meant to be run by your database.

[TRF · Accepted solution]

Based on the following data:

you can use the following tMap to determine for each row if it should be set to 0 or 1:

Then, you have to aggregate the result:

Here is the result:

|=-----------+----=|
|CurrentMonth|Count|
|=-----------+----=|
|2019-09     |1    |
|2019-10     |1    |
'------------+-----'

Is that what you expect?

Else, give more details (where sample data and expected result).

Here is the whole job:

[TRF]

@KathikVenky, does this help?

If so, thank's to mark your case as solved.