Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Handling on-going events [Beginner's Question]

Hi,   I am trying to do analysis on how weather affects demand for city bikes in Oslo. So far, I have three tables:   Weather observations are done on hourly basis, started_at and ended_at ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

lbendlin 

Hi, thank you for your answer and clarification! I have created this table with hourly granularity because initially I was thinking about linking everything based on hour and I've had star schema in mind.  As for the observation table, I have one set of observations per hour:

 

But then, to connect my tables, I still need some bridge between them. Otherwise I won't be able to visualise for example average duration of the tour vs average temperature.

 

edit: Actually, I think I could use YYYYmmDDhh as a key, I just need to add a row to my tours table everytime a tour exceeds one hour(?) and then I have many to one relationship. Gonna try that!

 

So the only solution would be to create two relationships(one active and one inactive) to both date fields and then USERELATIONSHIP() to achieve expected output?  

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

that's one option, but (by far) not the oly one.  You could also create a reference of your dates table so you can link the trip start to the dates table and the end time to the reference.  Or you could use LookupValue etc.