Forum

Start and End Dates...
 
Notifications
Clear all

Start and End Dates Columns

4 Posts
2 Users
0 Reactions
134 Views
(@ruthib4)
Posts: 3
Active Member
Topic starter
 

Hello. Hope you all are ok.

I am trying to transform my data but not able to progress. Here is the problem.

Have a table with the following columns

Can you please help me?

Thanks 

DESCRIPTION DATE SALARY
FINISH 1 10/30/2017 100
START 1 10/18/2016 100
FINISH 2 6/11/2003 1091.25
MODIFICATION 1 1/1/2003 1091.25
MODIFICATION 2 12/1/2002 1091.25
MODIFICATION 3 11/1/2002 1634.14
MODIFICATION 4 10/1/2002 1636.09
MODIFICATION 5 9/1/2002 1634.14
MODIFICATION 6 6/1/2002 1634.49
MODIFICATION 7 5/1/2002 1728.61
MODIFICATION 8 4/1/2002 1367.06
MODIFICATION 9 3/1/2002 1361.9
MODIFICATION 10 2/1/2002 1366.79
MODIFICATION 11 1/1/2002 1053.75
MODIFICATION 12 6/16/2001 1008.75
MODIFICATION 13 1/1/2001 1008.75
MODIFICATION 14 8/1/2000 947
MODIFICATION 15 1/1/2000 947.5
MODIFICATION 16 12/3/1998 861.25
MODIFICATION 17 1/1/1998 755
MODIFICATION 18 4/1/1997 661.25
MODIFICATION 19 4/1/1996 557.41
MODIFICATION 20 12/4/1995 484.88
MODIFICATION 21 6/1/1995 457.5
MODIFICATION 22 4/1/1995 448.98
MODIFICATION 23 1/1/1995 408.5
MODIFICATION 24 1/1/1994 381.75
MODIFICATION 25 7/21/1993 256.86
MODIFICATION 26 1/1/1993 142.7
START 3 9/28/1992 133.3
FINISH 4 11/11/1991 119
MODIFICATION 27 11/16/1990 119
FINISH 5 10/19/1990 100.8

and I need the following:

DESCRIPTION START DATE FINISH DATE SALARY
FINISH 1 10/18/2016 10/30/2017 100
MODIFICATION 1 1/1/2003 6/11/2003 1091.3
MODIFICATION 2 12/1/2002 1/1/2003 1091.3
MODIFICATION 3 11/1/2002 12/1/2002 1634.1
MODIFICATION 4 10/1/2002 11/1/2002 1636.1
MODIFICATION 5 9/1/2002 10/1/2002 1634.1
MODIFICATION 6 6/1/2002 9/1/2002 1634.5
MODIFICATION 7 5/1/2002 6/1/2002 1728.6
MODIFICATION 8 4/1/2002 5/1/2002 1367.1
MODIFICATION 9 3/1/2002 4/1/2002 1361.9
MODIFICATION 10 2/1/2002 3/1/2002 1366.8
MODIFICATION 11 1/1/2002 2/1/2002 1053.8
MODIFICATION 12 6/16/2001 1/1/2002 1008.8
MODIFICATION 13 1/1/2001 6/16/2001 1008.8
MODIFICATION 14 8/1/2000 1/1/2001 947
MODIFICATION 15 1/1/2000 8/1/2000 947.5
MODIFICATION 16 12/3/1998 1/1/2000 861.25
MODIFICATION 17 1/1/1998 12/3/1998 755
MODIFICATION 18 4/1/1997 1/1/1998 661.25
MODIFICATION 19 4/1/1996 4/1/1997 557.41
MODIFICATION 20 12/4/1995 4/1/1996 484.88
MODIFICATION 21 6/1/1995 12/4/1995 457.5
MODIFICATION 22 4/1/1995 6/1/1995 448.98
MODIFICATION 23 1/1/1995 4/1/1995 408.5
MODIFICATION 24 1/1/1994 1/1/1995 381.75
 
Posted : 06/09/2021 8:07 am
(@catalinb)
Posts: 1937
Member Admin
 

Hi Victor,

You have:

MODIFICATION 1- Start Date: 1/1/2003 , Salary: 1091.25

In your desired output, from where is Finish Date coming from, not seeing it in the initial data?

DESCRIPTION START DATE FINISH DATE SALARY
       
MODIFICATION 1 1/1/2003 6/11/2003 1091.3
 
Posted : 13/09/2021 11:42 pm
(@ruthib4)
Posts: 3
Active Member
Topic starter
 

Hi Catalin, the start date is formed by the previous date:

Example:

from below table,

Start 1 = 10/18/2016

Modification 1 = Start date 1/1/2003 and finish date 6/11/2003

Modification 2 = start date 12/1/2002 and finish date 1/1/2003

Hope this helps

Thanks in advance

DESCRIPTION DATE SALARY
FINISH 1 10/30/2017 100
START 1 10/18/2016 100
FINISH 2 6/11/2003 1091.25
MODIFICATION 1 1/1/2003 1091.25
MODIFICATION 2 12/1/2002 1091.25
 
Posted : 14/09/2021 7:26 am
(@catalinb)
Posts: 1937
Member Admin
 

Ok, so Modification 1 has the finish date of FINISH 2 ?! What's the order of the operations, as it's not as obvious as you may think. The date column looks like it is a start date or an end date, depending on description, correct?

But, why do you have in your sample data (first post) START 1 and 3, FINISH 1, 2, 4 and 5 and MODIFICATION 1 to 27. Why there are gaps and which ones are related and must be analyzed as a group?

Please provide the entire logical chain, we can't imagine our own process, guessing always leads to wasted time.

Thanks for understanding, we'll try to help you.

 
Posted : 14/09/2021 11:51 pm
Share: