Difference between 2 dates based on another field in years and days

[khojas02]

I have a sample data as below

Assigned Analyst Assigned Date
John 2018-03-09 00:00:00.0
2018-03-23 00:00:00.0
2018-03-30 00:00:00.0
2018-04-16 00:00:00.0
2018-04-24 00:00:00.0
2018-04-26 00:00:00.0
2018-05-03 00:00:00.0

Joe 2017-03-22 00:00:00.0
2017-03-23 00:00:00.0
2017-05-01 00:00:00.0
2017-05-02 00:00:00.0
2017-05-18 00:00:00.0
2017-05-23 00:00:00.0

Now, I would like to find the time span for each Analyst based on the earliest and latest values of Assigned Date in Years and Days.
Assigned Date is simply the date on which the ticket was assigned. Ticket Number is the unique identifier which I didn't add in the sample data.
Thanks in advance

[woodcock]

Like this:

| makeresults 
| eval _raw="Assigned Analyst,Assigned Date
 John,2018-03-09 00:00:00.0 2018-03-23 00:00:00.0 2018-03-30 00:00:00.0 2018-04-16 00:00:00.0 2018-04-24 00:00:00.0 2018-04-26 00:00:00.0 2018-05-03 00:00:00.0
 Joe,2017-03-22 00:00:00.0 2017-03-23 00:00:00.0 2017-05-01 00:00:00.0 2017-05-02 00:00:00.0 2017-05-18 00:00:00.0 2017-05-23 00:00:00.0" 
| multikv forceheader=1 
| table A*

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| rex field=Assigned_Date mode=sed "s/(-\d+)\s+/\1T/g"
| makemv Assigned_Date
| eval Assigned_Date = strptime(Assigned_Date, "%Y-%m-%dT%H:%M:%S.%1n")
| stats range(Assigned_Date) AS duration BY Assigned_Analyst
| fieldformat duration = tostring(duration, "duration")

[khojas02]

Hi,

For some reason, this solution is not producing any output for me.

I just added required index and source on top of it.

[to4kawa · Accepted solution]

| makeresults 
| eval _raw="Assigned Analyst,Assigned Date
John,2018-03-09 00:00:00.0
,2018-03-23 00:00:00.0
,2018-03-30 00:00:00.0
,2018-04-16 00:00:00.0
,2018-04-24 00:00:00.0
,2018-04-26 00:00:00.0
,2018-05-03 00:00:00.0

Joe,2017-03-22 00:00:00.0
,2017-03-23 00:00:00.0
,2017-05-01 00:00:00.0
,2017-05-02 00:00:00.0
,2017-05-18 00:00:00.0
,2017-05-23 00:00:00.0"
|multikv forceheader=1
| table A*
| rename COMMENT as "this sample, from here, the logic"
| eval assigned_time=strptime(Assigned_Date,"%F %T.%1Q")
| eval assigned_year=strftime(assigned_time,"%Y")
| filldown Assigned_Analyst
| stats min(assigned_time) as FirstAssigned max(assigned_time) as LastAssigned range(assigned_time) as span by Assigned_Analyst assigned_year
| eval FirstAssigned=strftime(FirstAssigned,"%F %T"), LastAssigned=strftime(LastAssigned,"%F %T")
| eval span=tostring(span,"duration")
| rex field=span mode=sed "s/(\d+)?\+?(\d\d):(\d\d):\d\d\.\d+/\1d \2h \3m/g"

[khojas02]

This worked for me, thanks!!

How can we show the the days in years?

[to4kawa]

assigned_year displays already.

the days in years? what's this?

....
|rex field=span "(?<days>\d+)"
| eventstats sum(days) by Assigned_Analyst

this?

[khojas02]

Your search has created the output shown below. Now, if I want to add the column that shows the span in number of years.

AssignedAnalyst FirstAssigned LastAssigned span
B_Davis 2018-03-09 00:00:00 2020-02-28 00:00:00 721d 00h 00m
C_Ramos 2017-03-22 00:00:00 2019-06-24 00:00:00 824d 00h 00m
L_Allen 2018-09-19 00:00:00 2019-01-14 00:00:00 17d 01h 00m

Is is possible to do that? Thanks!!

[to4kawa]

 ....
| rex field=span "(?<days>\d+)"
| eval years=floor(days/365)."years ".(days % 365)."days"

If you want only years, amend this.

[woodcock]

See my answer. It does this and is more efficient, too.