Hi All,
It's about replacement leave, when a person work on holiday entitle either 1 day or 0.5 days.
The validity of work on holiday allow to be claims within 3 months only from the working date.
Let's said a PersonA work on Holiday as below:-
2018-01-27 - 1 day ( Must clear the leave between 2018-01-27 to 2018-04-27 else burnt )
2018-01-28 - 1 day ( Must clear the leave between 2018-01-28 to 2018-04-28 else burnt )
2018-02-03 - 1 day ( Must clear the leave between 2018-02-03 to 2018-05-03 else burnt )
PersonA Taking Substitute Leave on:-
2018-02-21 - 1 day
2018-02-27 - 0.5 day
2018-03-13 - 1 day
Means,
2018-02-21 taken 1 day, will off set from 2018-01-27 => 0 Balance
2018-02-27 taken 0.5 day,will off set from 2018-01-28 => 0.5 Balance
2018-03-13 taken 1 day, will off set from 2018-01-28 0.5 day => 0 Balance;
2018-03-13 0.5 day remaining taken, will off set 2018-02-03 0.5 day => 0.5 Balance
Output should be:
Work Date | TakenDate | Credit | Balance
2018-01-27 | 2018-02-21 | 1 | 0
2018-01-28 | 2018-02-27 | 0.5 | 0
2018-01-28 | 2018-03-13 | 0.5 | 0
2018-02-03 | 2018-03-13 | 0.5 | 0.5
May I know how to wrote a allocation sql to get output as above?
Thanks.
Regards,
Micheale
Output result:
Work Date | TakenDate | Credit | Balance
2018-01-27 | 2018-02-21 | 1 | 0
2018-01-28 | 2018-02-27 | 0.5 | 0
2018-01-28 | 2018-03-13 | 0.5 | 0
2018-02-03 | 2018-03-13 | 0.5 | 0.5
Are you sure you want to display people's email in your post?
Thanks for letting me know
Your code is unusually long and very hard to parse and make head or tails from it. To help us help you, could you describe in simple terms without code what you are attempting to accomplish. No code please
HI,
It's about replacement leave, when a person work on holiday entitle either 1 day or 0.5 days.
The validity of work on holiday allow to be claims within 3 months only from the working date.
Let's said a PersonA work on Holiday as below:-
2018-01-27 - 1 day ( Must clear the leave between 2018-01-27 to 2018-04-27 else burnt )
2018-01-28 - 1 day ( Must clear the leave between 2018-01-28 to 2018-04-28 else burnt )
2018-02-03 - 1 day ( Must clear the leave between 2018-02-03 to 2018-05-03 else burnt )
PersonA Taking Substitute Leave on:-
2018-02-21 - 1 day
2018-02-27 - 0.5 day
2018-03-13 - 1 day
Means,
2018-02-21 taken 1 day, will off set from 2018-01-27 => 0 Balance
2018-02-27 taken 0.5 day,will off set from 2018-01-28 => 0.5 Balance
2018-03-13 taken 1 day, will off set from 2018-01-28 0.5 day => 0 Balance;
2018-03-13 0.5 day remaining taken, will off set 2018-02-03 0.5 day => 0.5 Balance
Output should be:
Work Date | TakenDate | Credit | Balance
2018-01-27 | 2018-02-21 | 1 | 0
2018-01-28 | 2018-02-27 | 0.5 | 0
2018-01-28 | 2018-03-13 | 0.5 | 0
2018-02-03 | 2018-03-13 | 0.5 | 0.5
Hope my explanation make clear to you.
Thanks.
Regards,
Micheale
HI,
It's about replacement leave, when a person work on holiday entitle either 1 day or 0.5 days.
The validity of work on holiday allow to be claims within 3 months only from the working date.
Let's said a PersonA work on Holiday as below:-
2018-01-27 - 1 day ( Must clear the leave between 2018-01-27 to 2018-04-27 else burnt )
2018-01-28 - 1 day ( Must clear the leave between 2018-01-28 to 2018-04-28 else burnt )
2018-02-03 - 1 day ( Must clear the leave between 2018-02-03 to 2018-05-03 else burnt )
PersonA Taking Substitute Leave on:-
2018-02-21 - 1 day
2018-02-27 - 0.5 day
2018-03-13 - 1 day
Means,
2018-02-21 taken 1 day, will off set from 2018-01-27 => 0 Balance
2018-02-27 taken 0.5 day,will off set from 2018-01-28 => 0.5 Balance
2018-03-13 taken 1 day, will off set from 2018-01-28 0.5 day => 0 Balance;
2018-03-13 0.5 day remaining taken, will off set 2018-02-03 0.5 day => 0.5 Balance
Output should be:
Work Date | TakenDate | Credit | Balance
2018-01-27 | 2018-02-21 | 1 | 0
2018-01-28 | 2018-02-27 | 0.5 | 0
2018-01-28 | 2018-03-13 | 0.5 | 0
2018-02-03 | 2018-03-13 | 0.5 | 0.5
Hope my explanation make clear to you.
Thanks.
Regards,
Micheale
1 Like