Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Given is a table with the following values:
Name | Starttime | Endtime |
---|---|---|
A | 8:00 | 10:00 |
B | 7:00 | 9:00 |
... |
I want to show a barchart with 48 bars where every bar is a time period of an half hour (7:00, 7:30, 8:00, 8:30)
Each Bar should count the count of entries of the given table where the starttime is before the period and the endtime is after the period. The resultset should be:
Period | Number of entries |
---|---|
7:00 | 1 |
7:30 | 1 |
8:00 | 2 |
8:30 | 2 |
9:00 | 2 |
9:30 | 1 |
10:00 | 1 |
My thoughts were to create a virtual table for the The Period's and connect them to the Data, but I have no idea how to connect the both sets, cause there is no unique foreign key for that it's kind of a "between-join"
Do you have any idea's to solve that?
Temp:
Load
RowNo() as Row,
mod(RowNo(),60) as Minutes,
mod(div(RowNo(),60),(24)) as Hours
AutoGenerate (24*60);
Temp2:
Load
Hours,
Minutes,
Hours&':'&Minutes as Time,
Hours&':'&if(Minutes<30,00,30)&' to '&Hours&':'&if(Minutes<30,29,59) as Bucket
// Hours&':'&Class(Minutes,30) as Bucket2
Resident Temp;
DROP Table Temp;
Try to Map this
Hi ,
Yes you can create a virtual period table and connect it to the data using INTERVAL MATCH. Check the forum for examples.
When I have an entry C with Starttime = 8:10 and Endime=8:20 it will not be connected with 8:00 but in my case it has to be counted, because it is between 8:00 and 8:30
Instead of creating a virtual table with period of 30 minustes, you can create a virtual table for every minute using autogenerate and the do the interval match.
And how do I ensure that the chart later shows a bar for each 30-minutes step?
Temp:
Load
RowNo() as Row,
mod(RowNo(),60) as Minutes,
mod(div(RowNo(),60),(24)) as Hours
AutoGenerate (24*60);
Temp2:
Load
Hours,
Minutes,
Hours&':'&Minutes as Time,
Hours&':'&if(Minutes<30,00,30)&' to '&Hours&':'&if(Minutes<30,29,59) as Bucket
// Hours&':'&Class(Minutes,30) as Bucket2
Resident Temp;
DROP Table Temp;
Try to Map this