Diffrence in hours between dates field in the same colum

Hi
I want to calculate the difrences in Hours between dates in the same column. it’s mean that I have table with column name “start timestems” for any notification and I want to measure the time between the notifications so I will know what is the avg time that notification creates.
someone can help me pls with this function?

Hi @Noys ,

Can you provide more information about the different fields in the dataset? Specifically, how are the notifications captured in the data? Is it in a separate row? Or another field in the same row?

In general, you can calculate differences in data values with the datediff() function, here.

But in this case, you might have to enhance your datasource to include the timestamps with each notification.

Thanks!

ws

1 Like

Sure, I’m sorry.

Let me explain:

This is example for my table:

order_number order_status Start_Time_Stemp
1 completed 1/1/23 10:20
1 waiting for supplier 1/1/23 10:20
1 waiting for materials 1/1/23 10:20
2 created 1/1/23 10:30
2 cancaled 1/1/23 10:30
3 created 1/1/23 10:50
4 Technically completed 1/1/23 16:00
4 work completed 1/1/23 16:00

I have a dataset structured in a way that it describes orders with their statuses and the order creation timestamp. For any order number there are many statuses but one and unique StartTimestamp that describes the date and time when the order was created.

My customer asked for a KPI that calculates the average time between order creations.

He wants to know how much time estimate there is between creation of orders there is.

I know the dateiff function but I want to apply it to the same field, with the value right after.

So at the beginning anyway I should manipulate the table so that I get one row for any order with its number and its StartTimestemp:

order_number Start_Time_Stemp
1 1/1/23 10:20
2 1/1/23 10:30
3 1/1/23 10:50
4 1/1/23 16:00
5 1/1/23 19:00
6 2/1/23 9:30
7 2/1/23 11:20
8 2/1/23 13:00

Then, I should calculate the dateiff between the order and the order after.

Something like this:

running_avg(Datediff(StartTimestemp(order_index), StartTimestemp(order_index+1))

I don’t know how to calculate it.

The only way I thought about is to insert a column with SQL custom of index and then join where index=index+1 and add only the column of the StartTimeStemp of the next row, so I will get something like that:

order_number Start_Time_Stemp Start_Time_Stemp_of_the_next_order
1 1/1/23 10:20 1/1/23 10:30
2 1/1/23 10:30 1/1/23 10:50
3 1/1/23 10:50 1/1/23 16:00
4 1/1/23 16:00 1/1/23 19:00
5 1/1/23 19:00 2/1/23 9:30
6 2/1/23 9:30 2/1/23 11:20
7 2/1/23 11:20 2/1/23 13:00
8 2/1/23 13:00

And then it is easy to add the calculated field of date_diff and apply runnung_avg for the columns of the date_diff.

But, it’s absolutely really expensive and not an effective solution.

I would appreciate any ideas.

Thank you.

Have you looked at lag(). There is an example that is almost exactly what you are trying to do with that expensive join.

2 Likes

Hi @Andy_Brand

Thank you for your reply!

Actually, I wasn’t aware of this function before and I’m glad I know now. Unfortunately, it receives “measure” input, which doesn’t include datediff() function, instead it receives aggregate functions such as sum.

If there is only one row,you could use max() , or avg() for the aggregate. So create a calculated field such as NextStartTime lag(max(StartTime), DESC, 1, [your partition attributes]).

Then you could pass StartTime, and NextStartTime to DateDiff.