Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / select from table HRMC...
Finance | Project Operations, Human Resources, ...
Answered

select from table HRMCompFixedEmpl between date

(0) ShareShare
ReportReport
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;

 

 

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,158 Moderator on at
    RE: select from table HRMCompFixedEmpl between date

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

  • mohammed.mqi Profile Picture
    mohammed.mqi 2,050 on at
    RE: select from table HRMCompFixedEmpl between date

    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

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,158 Moderator on at
    RE: select from table HRMCompFixedEmpl between date

    Yes, I understand.

  • mohammed.mqi Profile Picture
    mohammed.mqi 2,050 on at
    RE: select from table HRMCompFixedEmpl between date

    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

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,158 Moderator on at
    RE: select from table HRMCompFixedEmpl between date

    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.

  • mohammed.mqi Profile Picture
    mohammed.mqi 2,050 on at
    RE: select from table HRMCompFixedEmpl between date

    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

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,158 Moderator on at
    RE: select from table HRMCompFixedEmpl between date

    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?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Congratulations to all our community participants!

Adis Hodzic – Community Spotlight

We are honored to recognize Adis Hodzic as our May 2025 Community…

Kudos to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

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

#1
Martin Dráb Profile Picture

Martin Dráb 372 Most Valuable Professional

#2
Abhilash Warrier Profile Picture

Abhilash Warrier 291

#3
Saalim Ansari Profile Picture

Saalim Ansari 209

Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans