SQLTeam.com | Weblogs | Forums

Replacement Leave vs Subsistute Leave FIFO Date Range Allocation Better Query Solution

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

No code

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

Hi All Expert,

Please help me please.

Regards,
Micheale