Can I sum a conditional value by years????

[AsMok]

hi all

lets assume that we have 2 employees :::
according to job conditions
-------employees have 30 days as an annual leave if the work for the company less than 3 years
-------------and have 45 days as an annual leave if the work for the company more than 3 years

calculating this year leave on my report can be done by.... i wrote it in a query ::

IIf(DateDiff("yyyy";[WorkDate];Date();7;1)>=3;45;30)

how can i have a field in my report to calculate all his working years since his working date that means some years the same employee have 30 days others have 45 ones:

hope my question is clear>>>>>>

 

[Ranman256]

years, leave
1 , 30
2 , 30
3 , 45

Q1 get the base data:
select emp, HireDate, YrsWorked: dateadd("yyyy",HireDate,Date()) from tData

Q2 join Q1 with the tLeave table on YrsWrkd

 

[CJ_London]

not sure if your statement is complete

employees have 30 days as an annual leave if the work for the company less than 3 years

Normally you would have a 'leave year' in which to take your annual leave, perhaps starting 1st Jan, maybe the start of the financial year - or perhaps the anniversary of their start date.

So an employee starts - what is the leave entitlement they can actually take? 30 days? even if they only started yesterday?

 

[AsMok]

not sure if your statement is complete
Normally you would have a 'leave year' in which to take your annual leave, perhaps starting 1st Jan, maybe the start of the financial year - or perhaps the anniversary of their start date.

So an employee starts - what is the leave entitlement they can actually take? 30 days? even if they only started yesterday?

i just wrote it in general to know how can i sum it....
job conditions are more accurate and count it by days and hours
it is just a mock case

 

[CJ_London]

there are many ways of summing it - which way depends on your business rules. Suggest go with Ranmans suggestion to get you going

 

[AsMok]

Hi all
Thanks all

I placed Iif function on my report and finally I got it as I want ......for sure I will take other work conditions in mind....

if you expect any future problems please notify me​

---
i placed this function to unbound text

=IIf([yearsworked]>=3;(([yearsworked]-2)*45)+(2*30);[yearsworked]*30)

 

[CJ_London]

glad you have a solution