interval data with only one date field to create a tenure field

[danaleota1]

I am trying to create a second date field in my table to ultimately create a tenure field in months.  I have a HireDate field but the second date would need to be arbitrary the dates wouldn't be set.  I also will change the date format of hire date to be "MMM-YYYY" to measure my tenure in months. 

Would I somehow be using the iterno() function or setting a variable for this? 

Once I have my secondary date then I would an interval measure for tenure. 

The original table fields of interest would be:

Load

ID,

HireDate

From myTable;

[Kushal_Chawda]

Would you be able to provide sample data with expected output

[danaleota1]

It would be something like below.  I want to be able to take a respective date and then use that date to come up with Tenure based on the hire date.  If I'm looking at data from say 1/2020 my tenure for each person will be different than if I'm looking at data from last month.  I want to make a calculated field- Tenure based on hire date and this second date. 

Date	Hire Date	Tenure in Days
4/1/2020	5/3/2018	699
5/1/2020	5/3/2018	729
6/1/2020	5/3/2018	760
7/1/2020	5/3/2018	790

[danaleota1]

Actually this is what I have so far its starting to work:

LIB CONNECT TO 'myconnection';
[TOTAL_VIEW_AGENT]:
LOAD
TV_AGENT_ID,
TV_LAST_NAME,
TV_FIRST_NAME,
TV_LAST_NAME & ', ' & TV_FIRST_NAME as Agent,
Date(Date#(TV_HIRE_DATE, 'MM-DD-YYYY'), 'M/D/YYYY') as HIRE_DATE,
today() as MaxDate,
Date('1/1/2020') as MinDATE,
TV_AVAYA_LOGIN_ID;

[TOTAL_VIEW_AGENT]:
SELECT
"TV_AGENT_ID",
"TV_LAST_NAME",
"TV_FIRST_NAME",
"TV_HIRE_DATE",
"TV_AVAYA_LOGIN_ID"
FROM "OPS_DASHBOARD_OWNER"."TOTAL_VIEW_AGENT"
where "TV_DEPARTMENT" In ('750','751','795','798','799','993','994','995','9996','9997','9998','9999','9994','9995')
and "TV_MU_NBR" NOT IN('70','9999','8888');

[Temp]:
Load
TV_AGENT_ID,
Agent,
HIRE_DATE,
MaxDate,
MinDATE + IterNo() as ROWDATE,
TV_AVAYA_LOGIN_ID
Resident [TOTAL_VIEW_AGENT]
while MinDATE +IterNo() <= MaxDate;

Drop Table [TOTAL_VIEW_AGENT];

[TenureTable]:
Load
TV_AGENT_ID,
TV_AVAYA_LOGIN_ID,
Agent,
HIRE_DATE,
Date(ROWDATE),
ROWDATE - HIRE_DATE as TENURE
Resident [Temp];


DROP Table Temp;

[Kushal_Chawda]

Do you see any issues in this code?

[danaleota1 · Accepted solution]

No I don't think so. I made a few minor adjustments and it is working.  I'm not sure if there was a more efficient way to accomplish this but it's working. 

LIB CONNECT TO 'myconnection';
[TOTAL_VIEW_AGENT]:
LOAD
TV_AGENT_ID,
TV_LAST_NAME,
TV_FIRST_NAME,
TV_LAST_NAME & ', ' & TV_FIRST_NAME as Agent,
Date(Date#(TV_HIRE_DATE, 'MM-DD-YYYY'), 'M/D/YYYY') as HIRE_DATE,
today() as MaxDate,
Date('1/1/2020') as MinDATE,
TV_AVAYA_LOGIN_ID;

[TOTAL_VIEW_AGENT]:
SELECT
"TV_AGENT_ID",
"TV_LAST_NAME",
"TV_FIRST_NAME",
"TV_HIRE_DATE",
"TV_AVAYA_LOGIN_ID"
FROM "OPS_DASHBOARD_OWNER"."TOTAL_VIEW_AGENT"
where "TV_DEPARTMENT" In ('750','751','795','798','799','993','994','995','9996','9997','9998','9999','9994','9995')
and "TV_MU_NBR" NOT IN('70','9999','8888');

[Temp]:
Load
TV_AGENT_ID,
Agent,
HIRE_DATE,
MaxDate,
MinDATE + IterNo() as ROWDATE,
TV_AVAYA_LOGIN_ID
Resident [TOTAL_VIEW_AGENT]
while MinDATE +IterNo() <= MaxDate;

Drop Table [TOTAL_VIEW_AGENT];

[TenureTable]:
Load
TV_AVAYA_LOGIN_ID & '|' & date(ROWDATE) as KEY_IdDate,
TV_AGENT_ID,
TV_AVAYA_LOGIN_ID as AVAYA_LOGIN,
Agent,
HIRE_DATE,
DATE(ROWDATE,'M/D/YYYY') as ROWDATE,
ROWDATE - HIRE_DATE as TENUREDays,
if(ROWDATE - HIRE_DATE > 180, 'Tenured', 'NewHire') as TENURE,
IF(ROWDATE- HIRE_DATE < 30, '29 days ', IF(ROWDATE - HIRE_DATE < 60,'30-59 days',
IF(ROWDATE -HIRE_DATE < 90, '60 - 89 days' , IF(ROWDATE- HIRE_DATE < 180 , '90 - 179 days',
IF(ROWDATE -HIRE_DATE < 365, '179 days - 1 year', 'Over 1 Year'))))) as TENURE2
Resident [Temp]
where ROWDATE - HIRE_DATE > 0 ;

DROP Table Temp;

Output: