Number of customers online each hour of the day

[dirk_konings]

HI,

I have this data to read in QV:

Customer	Date	From	To
A	02/01/2010	6:30	8:30
B	02/01/2010	7:00	8:00
C	02/01/2010	7:00	7:30
D	02/01/2010	8:00	10:30
E	02/01/2010	8:00	8:15
F	02/01/2010	8:00	9:15
G	02/01/2010	10:00	12:00

and i want to have a chart like this :

From - To	# Customers
00:00 - 00:59	0
01:00 - 01:59	0
02:00 - 02:59	0
03:00 - 03:59	0
04:00 - 04:59	0
05:00 - 05:59	0
06:00 - 06:59	0,5
07:00 - 07:59	2,5
08:00 - 08:59	2,75
09:00 - 09:59	1,25
10:00 - 10:59	1,5
11:00 - 11:59	1
12:00 - 12:59	0

where each customer that is online a whole hour = 1

half hour = 0,5

What to do in the script for this ?? Is there a easy way ??

Thanks Dirk.

[#2]

Dirk,

take a look at 'intervalmatch' which should give you some ideas.

Regards,

Gordon

[dirk_konings]

Gordon,

Don't think this will work. Intervalmatch is for 1 value between a min and max value.

In this case, i have to split every time into hour parts.

Dirk

[Oleg_Troyansky · Accepted solution]

Dirk,

if your data volume allows, you can generate hourly rows for each full or partial hour when the customer was active (based on a From-To range), and calculate the portion of the hour when the customer was online. See attached example.

best regards,

[#5]

Oleg

I used your example to split the time into the minutes between each hour segment as such

Stationary_time_split:

load

          RowID,

          hour(time(HourStart, 'hh:mm')) as HourID,

          time(HourStart, 'hh:mm') as HourStart,

          time(HourEnd, 'hh:mm') as HourEnd,

          num#(interval(time(rangemin([Vacated Time], HourEnd), 'hh:mm') - time(rangemax([Arrived Time], HourStart), 'hh:mm') , 'mm') ) as ActiveTime

;

load

          *,

          floor([Arrived Time], 1/24) + (Iterno() - 1)/24 as HourStart,

          floor([Arrived Time], 1/24) + (Iterno())/24  as HourEnd

resident Bay_Sensor_activity

while

          floor([Arrived Time], 1/24) + Iterno()/24 <= ceil([Vacated Time], 1/24) + 0.02 //

;

What i am struggling with though is if the TO date is different to the FROM date, i.e the time period extends past midnight into the following day.  I'm trying to amend the above to take account of the day and get it to generate the time through to the following day - however i'm struggling.  Any help on this would be very much appreciated

Thanks

Lewis