select from table HRMCompFixedEmpl between date

(0) Share

Report

Posted on by mohammed.mqi

2,050

Greeting everyone

I want to know the total monthly salary of the employee who receives it during a specific period, regardless of the positions in which he worked

so i faced problem when i used this code if employee has been already expired

it's give me 0 even if he was have position between that date (SelectedDate)

select sum (PayRate) from hRMCompFixedEmpl
where hRMCompFixedEmpl.Worker==hcmWorker.RecId
&& (hRMCompFixedEmpl.ValidFrom <= SelectedDate  && hRMCompFixedEmpl.ValidTo >= SelectedDate )

and alsoe i try this code

it's give me total (PayRate) even if date out of range of my (SelectedDate)

but i need only total (PayRate) when (SelectedDate) still in range of (hRMCompFixedEmpl.ValidFrom & hRMCompFixedEmpl.ValidTo)

dateStart=SelectedDate;
dateEND=SelectedDate;

select validTimeState(dateStart, dateEND) sum(PayRate) from hRMCompFixedEmpl
where hRMCompFixedEmpl.Worker==hcmWorker.RecId;

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

You need to use the validTimeState(dateStart, dateEnd) syntax when querying date effective tables, otherwise it will give you only results that are valid today.

Your second code will return sum of PayRate for all records that were valid on "selectedDate". By the way, if your start date and end date are the same, you can simply provide one data for the validTimeState() function.

Do you still have some problem with your code? Can you explain a bit more?

Was this reply helpful? Yes No
mohammed.mqi 2,050 on at

Like (0)

Report

ok thanks for replay

and thanks for let me explain

in report i'm trying to print from pay statements so there's many pay statements for each employee

for example if i have employee already Termination in January 2020 so from January to August in 2019 he was have one position with PayRate 3k$

but from September to November he also have another position with PayRate 4k$

so for 3 months he was receive PayRate as total 7k $

in my code i need to calculated his PayRate for each month separately

for example if i need for August it must PayRate 3k$

and from September to October will be PayRate 7k $

and December will be his PayRate 3k$

but my code in December return me PayRate 7k $ and that's the problem

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

If you look at the data, you notice that you can't calculate such thing with only one statement.

Instead you need one statement for calculating the salary in August. Then another for September. And so on. Finally you sum all these calculations together.

Was this reply helpful? Yes No
mohammed.mqi 2,050 on at

Like (0)

Report

but i want PayRate depending on (SelectedDate) and check on range between (hRMCompFixedEmpl.ValidFrom & hRMCompFixedEmpl.ValidTo) and if he has multi position inside that range it will be give me total of PayRate

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

Yes, I understand.

Was this reply helpful? Yes No
mohammed.mqi 2,050 on at

Like (0)

Report
ok thanks for replay

in this case so i need only to show the first (PayRate) for that employee

and when i typed this code it's give me error expression

select validTimeState(SelectedDate, SelectedDate) firstOnly(PayRate) from hRMCompFixedEmpl where hRMCompFixedEmpl.Worker==hcmWorker.RecId;

how can i fix this code

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report
select firstonly validTimeState(SelectedDate) PayRate from hRMCompFixedEmpl where hRMCompFixedEmpl.Worker==hcmWorker.RecId;

Was this reply helpful? Yes No

Join the conversation

Community site session details

select from table HRMCompFixedEmpl between date

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

select from table HRMCompFixedEmpl between date

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates