Calculate time difference between multivalue fields

[ayush1234]

Hi , I have a data from search in below format
Name       provider1IN                 provider1OUT               provider2IN             provider2OUT
ABC               13:00                             14:00                            15:00                                 16:00
                         17:00                             18:00                            19:00                                 20:00
BCD                                                                                                  21:00                                22:00
                                                                                                           23:00                                 23:30

here, for ABC, Intime at provider 1 is at 13:00 and 17:00 hours and  out time is 14:00 and 18:00 hours. Similary at provider 2, intime is 15:00 and 19:00 hours and out time is 16:00 and 20:00 hours
For BCD, provider1 intime and outtime are null values and only provider2 intime and outtime has value as shown in above table.

Requirement : I need to calculate total time spent by ABC and BCD in provider1 and provider 2
which means what I want to achieve is

ABC                       provider1time                                            provider2time
                                (14:00-13:00)+(18:00-17:00)             (16:00-15:00)+(20:00-19:00)
BCD                                      0                                                       (22:00-21:00)+(23:30-23:00)

Kindly help and suggest how can I achieve the above result 
I am using stats list function to retrieve the multivalue intime and outtime.

Thanks for the help in advance.

[richgalloway]

I think stats list has painted you into a corner.  Can you remove that and use eval to compute the difference between IN and OUT for each provider?  Then use stats to sum the differences.

... | eval provider1diff = strptime(provider1OUT, "%H:%M") - strptime(provider1IN, "%H:%M"), provider2diff = strptime(provider1OUT, "%H:%M") - strptime(provider2IN, "%H:%M")
| stats sum(provider1diff) as provider1time, sum(provider2diff) as provider2time by Name
| fillnull value=0

[ayush1234]

Thanks @richgalloway But since for each Name , provider1IN,provider1OUT,provider2IN,provider2OUT are multivalue fields, a direct subtraction is not working. I have to use list to get all the times when request was sent to provider and when response was received.

[richgalloway]

Don't use stats list. Then you won't have multi-value fields. That's the crux of my reply.

[ayush1234]

Hi @richgalloway 
I have to either use
stats list(eval(if(Source="Provider1",_time,null()))) as Provider1IN list(eval(if(Destination="Provider1",_time,null()))) as Provider1OUT

OR 

stats values(eval(if(Source="Provider1",_time,null()))) as Provider1IN values(eval(if(Destination="Provider1",_time,null()))) as Provider1OUT

In either case, Provider1IN and Provider1OUT are multivalue field and similar for Provider2IN and Provider2OUT

Apologies if I am confusing here, I am new to this and trying to find my way out.

[richgalloway]

Where did the mandate to use list or values come from?
Can you share the full search in case there's another way to reach the same goal?