Change date of field if field wasn't changed

[eugeniyaromanov]

Hello again everyone!

I have a question, it terrifies me for month already

I have a Table:

Name	Status	Date
1	Working	01.10.2016
1	Ill	20.10.2016
1	Working	25.10.2016
1	Fired	28.03.2017
2	Working	01.12.2016
3	Working	30.12.2015
3	Vacation	01.08.2016
3	Working	20.08.2016

I need to calculate number or workers, for example on 01.02.2017.

And I need it to be 3.

How do i get field up to a specific date if it's not in chosen period but it haven't changed to a chosen period.

Am I succeed in explanation?

[eugeniyaromanov]

Can anyone help?

[Anil_Babu_Samineni]

May be confusing with the date field. Would you let me know what was the expecting output from given data

[Anonymous]

Do you want number of workers on a given date (like status = "Working")?? If the record is not present on that date, should we consider the status of the nearest date in the past?? Can you add some clear example?

[eugeniyaromanov]

Hi Anil!

Thank you for your concern!

I thought about it like another column or maybe it should be with intervalmatch... but notning...

I need to get number of workers on a date. The date could be any date.

The idea of table is workers and their status (work, ill, fired, on vacation etc.) but the date we have in table is only the date of moving to status.

For example worker1 was hired on 01.10.2016, than from 20.10.2016 to 24.10.2016 he was ill (24.10 we get from next date, when he was at work again), after that he was back at work (from 25.10.2016) and was fired yesterday (28.03.2017).

I need to count him as a working person (or ill, or fired) at any possible date (like on 01.02.2017 he must be counted as working; even if he doesn't have this status at the date, we know he worked, because status wasn't changed).

Please feel free to ask any question, should it help you to help me

BR

[Anil_Babu_Samineni]

Always you've mentioned 01.02.2017 but here we don't have that date in our tabular. From given data if you select any date then what was the expected to see in UI? better if you share few columns which you need

[eugeniyaromanov]

Yes!!! Exactly what i need! If record isn't here, we should get the record from the nearest in the past.

The clearest example I can write is down below.

Please ask if anything isn't clear!

[eugeniyaromanov]

But that is the point, to get data on date we don't have in our table.

Depending on previous status.