I have two sources A and В.
Source A contains events in form of:
Id1 StartTime1 EndTime1
Id2 StartTime2 EndTime2
Source B contains events like:
TxId1 TxStartTime11 TxEndTime11
TxId1 TxStartTime12 TxEndTime12
TxId1 TxStartTime13 TxEndTime13
TxId2 TxStartTime21 TxEndTime21
TxId2 TxStartTime22 TxEndTime22
TxId2 TxStartTime23 TxEndTime23
Id(i) and TxId(i) are the same, i.e these are the ids of related events.
I want to find the maximum of TxEndTime in each group of source B (grouped by TxId) and then calculate the difference between max(TxEndTime) and StartTime of the corresponding event in source A (Id<n> = TxId<n>).
The search for the start time is simple:
The search for finding maximum is quite easy too (omitting time conversions):
and it returns me a table [TxId][MaxTxEndTime].
The way to join two searches is also not so complicated (to align names of Id and TxId):
But how can I put all the things together?
Is it possible to join the results of the search A with the search B to calculate the difference? The problem is the first search returns the events while the second one returns the results of stats and I can't combine them for further calculations.
asked 10 Nov '11, 08:09
I usually tie my conditional evals directly to the source, so as long as the source is consistent the search is a little easier for others to understand. The whole search would be something like:
using your eval instead, and doing some conversion at the end:
Assuming we don't need to care about aligning different ID names (Id && TxId, they are both "Id" now), I managed to change my query like this:
That gives me the desired difference, but the search takes a certain time to complete on a larger amount of data. I guess, the JOIN statement is the cause, so is there any way to modify the search with OR to improve the performance?
answered 11 Nov '11, 14:28