Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone, I need your help. I’m working on a development about absenteeism, and I stuck with the actual data that I have.
At this day, my data is like this :
id_abs | B_date_abs | T_date_abs |
---|---|---|
12354 | 01/01/2014 | 20/04/2014 |
1454 | 23/02/2014 | 25/03/2014 |
8454 | 30/03/2014 | 01/04/2014 |
The goal is to analyse the % of absenteeism regarding the month selected or the year. So, for the first line, we've got 31 days of absenteeism in January, and 20 in february.
If I want to see the amount of days in february, it'll be 20 days (1st row) + 6 days (2nd row). What will be the best way to allocate those days for each months ?
Thanks a lot in advance.
Like this?
For simplicity, I have changed Field Names, You can change as per your database.
Can you please explain little more? Don't get idea of your requirements from this description.
Ramet,
What i will suggest you is that please explain in simple words and in synchronised steps the complete issue.
Explain what is the role of these two date field in calculating the Absent days.
I want to know how many days of absence we had in the company by months. But I only have the date of the beginning of the sickness leave and the ending date.
For the 1st row, we've got 110 days of absence (01/01/2014 --> 20/04/2014). But it has to be divided like this :
id_abs | B_date_abs | T_date_abs | count_days |
---|---|---|---|
12354 | 01/01/2014 | 31/01/2014 | 31 |
12354 | 01/02/2014 | 28/02/2014 | 28 |
12354 | 01/03/2014 | 31/03/2014 | 31 |
12354 | 01/04/2014 | 20/04/2014 | 20 |
Then you have to create those records in script, I would suggest.
Sure, but I don't know how to do it. That was the motivation for my post (bad-explained, of course ).
Does someone have an idea about it ?
Thx
Like this?
For simplicity, I have changed Field Names, You can change as per your database.
The best and fastest solution is a While LOAD (with a little bit date gymnastics here):
Data:
LOAD * INLINE [
id_abs, B_date_abs, T_date_abs
12345, 01.01.2014, 20.04.2014
1454, 23.02.2014, 25.03.2014
8454, 30.03.2014, 01.04.2014
];
Result:
NOCONCATENATE LOAD
id_abs,
MonthName(B_date_abs, IterNo()-1) as month,
If(AddMonths(B_date_abs, IterNo()) > T_date_abs,
T_date_abs - AddMonths(B_date_abs, IterNo()-1) +1,
AddMonths(B_date_abs, IterNo()) - AddMonths(B_date_abs, IterNo()-1)) as count_days
Resident Data
While AddMonths(B_date_abs, IterNo()-1) < T_date_abs;
And have fun coding things like that.. 😉
- Ralf
My first shot was wrong. 😉
Data:
LOAD * INLINE [
id_abs, B_date_abs, T_date_abs
12345, 01.01.2014, 20.04.2014
1454, 23.02.2014, 25.03.2014
8454, 30.03.2014, 01.04.2014
];
Result:
NOCONCATENATE LOAD
id_abs,
MonthName(B_date_abs, IterNo()-1) as month,
If(IterNo()=1,
Floor(MonthEnd(B_date_abs)) - B_date_abs,
If(MonthStart(AddMonths(B_date_abs, IterNo()-1)) = MonthStart(T_date_abs),
T_date_abs - MonthStart(T_date_abs),
Floor(MonthEnd(AddMonths(B_date_abs, IterNo()-1))) - MonthStart(AddMonths(B_date_abs, IterNo()-1)))) +1 as count_days
Resident Data
While MonthStart(AddMonths(B_date_abs, IterNo()-1)) <= MonthStart(T_date_abs);
- Ralf
It's perfect ! Thanks a lot !