Days allocation with 2 dates

[#1]

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.

[MK_QSL]

Can you please explain little more? Don't get idea of your requirements from this description.

[sujeetsingh]

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.

[#4]

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

[rbecher]

Then you have to create those records in script, I would  suggest.

[#6]

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

[MK_QSL · Accepted solution]

Like this?

For simplicity, I have changed Field Names, You can change as per your database.

[rbecher]

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

[rbecher]

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

[#10]

It's perfect ! Thanks a lot !