Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
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,016

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,156 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,016 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,156 on at
    RE: select from table HRMCompFixedEmpl between date

    Yes, I understand.

  • mohammed.mqi Profile Picture
    mohammed.mqi 2,016 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,156 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,016 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,156 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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans