Calculate a number of operations between two timestamps

[#1]

Hello. There are two tables:

1. The first one is a list of start time and finish time of customers  sessions  (customerID, operation type(start/finish), timestamp
2. The second one is a records of customers operations  during sessions (customerID, operation type, timestamp)

I want to calculate a number of customers who did any operation during the session, and who is not. And if a customer did some operations then to calculate how many operation he did and a time of the first one.

[MarcoWedel]

Hi,

you could transform table A using generic load in order to get start and end time in one row (one row per Session/customer id). Using intervalmatch you could then create a link table.

please provide sample data to demonstrate.

hope this helps

regards

Marco

[Peter_Cammaert · Accepted solution]

This is an example of how it can be done in the load script, using INTERVALMATCH as suggested by Marco.

See the script for an explanation.

Best,

Peter

[#4]

Thanks a lot! It is exactly what I need.

But I faced with one problem.

In my case i have two more fields in SessionData table.

So SD2 table looks like:  CustomerID, SessionStart, SessionStop, setting1,setting2

And I need this two fields in my Operations table.

The only way i found is to use left join again:

LEFT JOIN(Operations)

LOAD SessionStart, SessionStop, CustomerID, setting1,setting2

RESIDENT SD2;

DROP Table SD2;

I have sinking feeling that it is not a best practice.

[Peter_Cammaert]

Don't worry. If it works, it works. Your solution is allright.

You can also select the not-so-clean datamodel with a synthetic key.

Add your settings columns to the SessionData table. Drop the LEFT JOIN prefix from the INTERVALMATCH LOAD. And finally, delete the DROP Table SD2 statement.

After reloading, you'll get a datamodel with a third table (the intervalmatch table) and a synthetic key that keeps Sessions and Operations separate. Although it doesn't look nice, this model is perfectly ok and is an example of making good use of synthetic keys.

See Henric's explanation of the synthetic key advantages in IntervalMatch