So I would use #Timestamp() for your Date-Time field to get that interpreted and then wrap that with Hour() to get an hour column and then you can count that?
Somethign like this:
LOAD * Inline [
Number, ArriveDate, LeaveDate
1, 12/07/2018 07:00, 12/07/2018 08:00
2, 12/07/2018 08:00, 12/07/2018 09:00
TimeStamp#(ArriveDate,'MM/DD/YYYY HH:mm') As ArriveDate,
Hour(TimeStamp#(ArriveDate,'MM/DD/YYYY HH:mm')) As ArriveHour,
TimeStamp#(LeaveDate,'MM/DD/YYYY HH:mm') As LeaveDate,
Hour(TimeStamp#(LeaveDate,'MM/DD/YYYY HH:mm')) As LeaveHour
Drop Table Temp_Data;
Thanks but I'm not interested in the interval, I'm interested in counting how many dates fall in each hour of the day.so of I have 3 dates around 9:00 that would be a count of 3. If I have 2 dates around 1400, that is a count of 2 and so on...
I need a count like this for each column, shown in a graph. Base on one column is easy, because I can key on the dates from that column, but when it comes to including the other column, graphed into another bar on the same graph, I'm finding very difficult.
sorry, I was inattentive, I read it again, and understand the problem
I think your data should arrange in other way:
DateTime | Type
2018-01-01 12:00 | Arrival
2018-01-01 21:00 | Leave
and the hour(DateTime) will be the common dimension, the measure can be count(*) and add dimension Type, to get two line/bar.