Call Utilization/Number of calls per hour - SQL Query

It normally works the other way around...
You tell us what the existing tablename and fieldnames are.
Sample data and expected results will also provide you with a better answer.

Is this a study exercise?

Point taken. Please see attached.
Call-Details-April13-EE.xlsx

Just noticed you have not specified the database vendor - this is quite essential as date arithmetic differ dramatically between them. I'll start assuming SQL Server (as there is a time column).

I'd like to clarify "simultaneous" if I may, does this exclude calls that have no overlap with any other call, "O" is part of the call duration that "overlaps"e.g.

zero 'simultaneous'
__________
          __________
                    __________
                              __________
                                        __________

2 simultaneous
__________OOO
          __________
                    __________
                              __________OOO
                                        __________

5 simultaneous                                        
__________OOO
          __________OOO
                    __________OOO
                              __________OOO
                                        __________OOO

Select allOpen in new window

here is a starting point, it is based on the assumption of SQL Server 2012. That version support a "lead()" function so the version is important. Oracle also supports this feature.

The further assumption is that "simultaneous" is derived from overlapping calls (as depicted above). If none of these assumptions regarding database, version or overlapping are true please advise.

a small sample of your data (part of 2013-04-04) has been used and is available here to inspect: http://sqlfiddle.com/#!6/8485c/7 where the query may be run for testing.

CALLDATE	HR	SIMULTANEOUS	NUM_CALLS
2013-04-04	6		1		1
2013-04-04	7		11		14
2013-04-04	8		8		10
2013-04-04	9		26		52
2013-04-04	10		15		24
2013-04-04	16		0		1

-- produced by this code:
SELECT
  convert(varchar(10),calldate,121) as calldate
, hr
, sum(overlap) AS simultaneous
, count(8) AS num_calls
FROM (
      SELECT
              calldate
            , callstart
            , datepart(hour,callstart) AS hr
            , dateadd(SECOND,duration_sec,callstart) AS callEnd
            , CASE WHEN lead(callstart) over (ORDER BY calldate, callstart)
                        > dateadd(SECOND,duration_sec,callstart)
                        THEN 1
                   ELSE 0
              END AS Overlap
      FROM calldata
      WHERE calldate >= '2013-04-04' -- for A date

      --where calldate >= '2013-04-01' -- for a MONTH, from this date
      --and   calldate  < '2013-05-01' -- and before this date

     ) AS derived
GROUP BY
  calldate
, hr

Select allOpen in new window

some added notes.

I chose some field names that differ to the spreadsheet headings as 'Date' and 'Time' are sql reserved words: CallDate, CallStart
similarly the headings with () within them were also renamed.

& below is a variant of your spreadsheet where you can see an analysis of the overlaps found in the call data.

nb: sqlfiddle only permits very small data samples - hence I could not replicate all your data there.

I would sincerely like to propose that you share the real tables and fieldnames in your dbms - those will make it easier for all concerned. Please do advise the dbms vendor and version (very important that version).

Cheers, Paul
Q-28165626-Call-Details-Inspect.xlsx

Say, This is excellent feedback.
Its actually mysql (Microsoft SQL Server is just too complicated for me right now to work with). Please see attached data and table structure.
This data also includes the time of call with seconds.
There are really two queries, that are related.
1. The number of calls per hour calculated per minute. 1 call is also valid ie "simultaneous" is not strictly correct.
2. The number of calls per hour over a 24 hour period calculated over each hour.
1. is therefore a subset of 2.

The logic is that one is able to ultimately mine down from 2. into 1. for more detail.
Hope this is enough detail.
tTelkomDetailsMaster-Freq.sql

excellent! thanks. now I know dbms type MySQL (and version, which is in a file, 5.0.95 ) and the real table structure. This makes quite a difference.

I'm a little worried about the validity of your data though, look at the dates stored:
The year is 2603 or 0000 - this is not so great

INSERT INTO `tTelkomDetailsMaster_Freq` (`InvoiceNo`, `Invoice number`, `Row description`, `Number called from`, `Final number called`, `InvNoOrig`, `Client`, `ClientGroup`,
  `Date of call`, `Time`, `Time of call`, `Duration of call`, `Description`, `Tariff`, `Cost of call`, `Call type`, `Source data`, `Surcharge/SCall`, `Number called to`, `auto`, `ID`, `uniqueID`) VALUES
... '2603-05-07', '14:06:54', 1, 2, NULL, 'SC', 0, 5, 2252, 0.038, '094471795', '598772', 8942041, 831),
... '2603-05-07', '14:42:29', 1, 117, NULL, 'SC', 0, 5, 2252, 2.233, '094471795', '598773', 8942042, 832),
... '0000-00-00', '09:11:19', 0, 59, NULL, 'SC', 0, 5, 2252, 1.126, '094471795', '598774', 8942043, 833),
... '0000-00-00', '09:12:50', 0, 1, NULL, 'SC', 0, 5, 2252, 0.609, '094471795', '598775', 8942044, 834),

Select allOpen in new window

can you confirm this is correct?
and. maybe you would also want to look at why those date are so weird

there is more weird stuff in that table, e.g. field `Time` contains values like '60:47:20'

time cannot be greater than 23:59:59 (ignoring smaller units)

are you able to run this without error (I can't):

select
       `Date of call`
    ,  `Time`
    ,  `Time of call`
    ,  `Duration of call`
from tTelkomDetailsMaster_Freq

http://sqlfiddle.com/#!8/94caa/2
java.sql.SQLException: Illegal hour value '60' for java.sql.Time type in value '60:47:20.: select `Date of call` , `Time` , `Time of call` , `Duration of call` from tTelkomDetailsMaster_Freq

note this is being executed through Java - that may be the issue

what does a time like '60:47:20' represent?

I can run this:

select
       `Date of call`
    ,  concat(`Time`,'') as x
    ,  `Time of call`
    ,  `Duration of call`
from tTelkomDetailsMaster_Freq
where left(concat(`Time`,''),1) in ('3','4','5','6','7','8','9')

more...

what is `Time of call` (int)?
it can be zero but, in the same record,`Duration of call` >0

so is `Time of call` ignored?

so, here is a simple query that will produce a count of calls per hour, but the data is a bot weird so results at your end may be surprising

SELECT
       date_format(`Date of call`,'%Y-%m-%d') AS `Date of call`
    ,  left(`Time`,2)                         AS HourString /* weird times ! */
    ,  count(*)                               AS NumOfCalls
    ,  avg(`Duration of call`)                AS avCallSecs
FROM tTelkomDetailsMaster_Freq

WHERE `Date of call` >= '2603-04-17' /* weird dates ! */
AND   `Date of call`  < '2603-04-26'

GROUP BY
       `Date of call`
    ,  left(`Time`,2) /* weird times ! */
ORDER BY
      `Date of call`
    , HourString

Select allOpen in new window

see this at: http://sqlfiddle.com/#!8/94caa/18

Thank you for the detailed feedback. I must extend my apologies as I had used find and replace on the data to mask the tel no's and overlooked that the time and date would be affected.
Attached is corrected data
tTelkomDetailsMaster-Freq.sql

I have created a summary of that data in sqlfiddle
http://sqlfiddle.com/#!8/3506c/1

the previous query still works - but the work is incomplete - & I have to delay doing more until (my) tomorrow

Thanks, The result looks meaningful.  look forward to the morrow...

Ok, if I have interpreted you intention, here are 2 queries that summarise your call data By Day/By Hour (range of days, with columns per hour) showing count of calls
In an Hour, drilling down from a particular cell  the next query shows no. of calls made in 5 minute blocks (as an example)

Is this sufficient for you?

DATE OF CALL	HR_0	HR_1	HR_2	HR_3	HR_4	HR_5	HR_6	HR_7	HR_8	HR_9	HR_10	HR_11	HR_12	HR_13	HR_14	HR_15	HR_16	HR_17	HR_18	HR_19	HR_20	HR_21	HR_22	HR_23	CALLS_PER_DAY
2013-05-02	0	0	0	0	0	0	0	0	2	5	2	4	3	3	2	2	4	0	0	0	0	0	0	0	27
2013-05-03	0	0	0	0	0	0	0	0	1	3	3	2	2	5	3	1	2	0	0	0	0	0	0	0	22
2013-05-06	0	0	0	0	0	0	0	0	2	3	4	3	0	4	5	2	0	0	0	0	0	0	0	0	23
2013-05-07	0	0	0	0	0	0	0	0	2	0	2	2	1	3	2	5	3	0	0	0	0	0	0	0	20
2013-05-08	0	0	0	0	0	0	1	1	2	1	5	0	2	2	3	3	5	2	0	0	0	0	0	0	27
2013-05-09	0	0	0	0	0	0	0	0	1	0	4	4	3	5	2	3	3	1	0	0	0	0	0	0	26

Select allOpen in new window

drill down from a cell above, to detail below (e.g. 2013-05-02, hour 9)

DATE OF CALL	HOUR_OF_CALL	MI_0	MI_5	MI_10	MI_15	MI_20	MI_25	MI_30	MI_35	MI_40	MI_45	MI_50	MI_55	CALLS_IN_HOUR
2013-05-02	9		0	0	0	0	0	2	0	1	1	0	0	1	5

Select allOpen in new window

the SQL code for these:

SELECT
      date_format(`Date of call`,'%Y-%m-%d') AS `Date of call`
    , count( case when hour(`Time`) = 0  then `Duration of call` end ) as hr_0
    , count( case when hour(`Time`) = 1  then `Duration of call` end ) as hr_1
    , count( case when hour(`Time`) = 2  then `Duration of call` end ) as hr_2
    , count( case when hour(`Time`) = 3  then `Duration of call` end ) as hr_3
    , count( case when hour(`Time`) = 4  then `Duration of call` end ) as hr_4
    , count( case when hour(`Time`) = 5  then `Duration of call` end ) as hr_5
    , count( case when hour(`Time`) = 6  then `Duration of call` end ) as hr_6
    , count( case when hour(`Time`) = 7  then `Duration of call` end ) as hr_7
    , count( case when hour(`Time`) = 8  then `Duration of call` end ) as hr_8
    , count( case when hour(`Time`) = 9  then `Duration of call` end ) as hr_9
    , count( case when hour(`Time`) = 10 then `Duration of call` end ) as hr_10
    , count( case when hour(`Time`) = 11 then `Duration of call` end ) as hr_11
    , count( case when hour(`Time`) = 12 then `Duration of call` end ) as hr_12
    , count( case when hour(`Time`) = 13 then `Duration of call` end ) as hr_13
    , count( case when hour(`Time`) = 14 then `Duration of call` end ) as hr_14
    , count( case when hour(`Time`) = 15 then `Duration of call` end ) as hr_15
    , count( case when hour(`Time`) = 16 then `Duration of call` end ) as hr_16
    , count( case when hour(`Time`) = 17 then `Duration of call` end ) as hr_17
    , count( case when hour(`Time`) = 18 then `Duration of call` end ) as hr_18
    , count( case when hour(`Time`) = 19 then `Duration of call` end ) as hr_19
    , count( case when hour(`Time`) = 20 then `Duration of call` end ) as hr_20
    , count( case when hour(`Time`) = 21 then `Duration of call` end ) as hr_21
    , count( case when hour(`Time`) = 22 then `Duration of call` end ) as hr_22
    , count( case when hour(`Time`) = 23 then `Duration of call` end ) as hr_23
    , count(*) as calls_per_day
FROM tTelkomDetailsMaster_Freq

WHERE `Date of call` >= '2013-01-01'
AND   `Date of call`  < '2014-01-01'

GROUP BY
       `Date of call`
ORDER BY
      `Date of call`
;

Select allOpen in new window

set @day = '2013-05-02';
set @hour = 9;

SELECT
      date_format(`Date of call`,'%Y-%m-%d') AS `Date of call`
    , @hour as hour_of_call
    , count( case when minute(`Time`) >= 0  and minute(`Time`) < 5  then `Duration of call` end ) as mi_0
    , count( case when minute(`Time`) >= 5  and minute(`Time`) < 10 then `Duration of call` end ) as mi_5
    , count( case when minute(`Time`) >= 10 and minute(`Time`) < 15 then `Duration of call` end ) as mi_10
    , count( case when minute(`Time`) >= 15 and minute(`Time`) < 20 then `Duration of call` end ) as mi_15
    , count( case when minute(`Time`) >= 20 and minute(`Time`) < 25 then `Duration of call` end ) as mi_20
    , count( case when minute(`Time`) >= 25 and minute(`Time`) < 30 then `Duration of call` end ) as mi_25
    , count( case when minute(`Time`) >= 30 and minute(`Time`) < 35 then `Duration of call` end ) as mi_30
    , count( case when minute(`Time`) >= 35 and minute(`Time`) < 40 then `Duration of call` end ) as mi_35
    , count( case when minute(`Time`) >= 40 and minute(`Time`) < 45 then `Duration of call` end ) as mi_40
    , count( case when minute(`Time`) >= 45 and minute(`Time`) < 50 then `Duration of call` end ) as mi_45
    , count( case when minute(`Time`) >= 50 and minute(`Time`) < 55 then `Duration of call` end ) as mi_50
    , count( case when minute(`Time`) >= 55 and minute(`Time`) < 60 then `Duration of call` end ) as mi_55
    , count(*) as calls_in_hour
FROM tTelkomDetailsMaster_Freq

WHERE `Date of call` = @day
AND hour(`Time`) = @hour
GROUP BY
       `Date of call`
;

Select allOpen in new window

demonstration: http://sqlfiddle.com/#!2/3506c/14

This is looking very on track.
I have a question as to the logic behind this case statement:
count( case when minute(`Time`) >= 0  and minute(`Time`) < 5  then `Duration of call` end ) as mi_0

What does " then `Duration of call`" mean and result in?

To explain further, a all made within the same hour may end up without an overlapping duration if the duration of the two is not compared.

We are after knowing if there are too few lines i.e. if all the lines are busy at a certain time....

If you have taken this into account but I can't understand how.
Tx Shaun

the count() function requires a non-null value, any non-null value, so the choice of field was quite arbitrary.

i.e. count() will add 1 if there is a value, but ignore any nulls.

I could have used:

count( case when minute(`Time`) >= 0  and minute(`Time`) < 5  then 1 end ) as mi_0
or
count( case when minute(`Time`) >= 0  and minute(`Time`) < 5  then 'x' end ) as mi_0

by the way, these queries are really based on the 'start of a call', the duration does not matter. If you look at the average durations any influence of duration is (sorry) trivial in the overall scheme and certainly not very relevant at hourly increments.

In any case, you did rule out special consideration of "simultaneous" quite some time ago.

Thanks for the explanation.
There's been a misunderstanding as I said:
"1. The number of calls per hour calculated per minute. 1 call is also valid ie "simultaneous" is not strictly correct."

The point is "also". You query is good if there is only one call, but we need to know how many simultaneous calls as well I'm afraid.
Any ideas?

PS thank you for the excellent way you have displayed the results.

"also" makes no difference I'm afraid, note the "5 calls" below

zero 'simultaneous', 5 calls
__________
          __________
                    __________
                              __________
                                        __________

2 simultaneous, 5 calls
__________OOO
          __________
                    __________
                              __________OOO
                                        __________

5 simultaneous , 5 calls
__________OOO
          __________OOO
                    __________OOO
                              __________OOO
                                        __________OOO

Select allOpen in new window

there is a possibility that a calls spans one of the boundaries, and one could argue then that one call be counted in the starting "bucket" as well as the finishing "bucket" (but 1 call now becomes 2 calls, or you start splitting calls to 0.5) - which my queries do not do. One call is one call only, and it is counted at the point of commencement.

a possible refinement could be to count a call at the midpoint of the call duration
but please do look closely at the call durations, such a refinement is not likely to add to the value of the data IMHO.

If you really need count of all concurrent calls in any time period, then this is way more complex

- and I don't know how to achieve this in MySQL (without research at least)
- in Oracle or SQL Server I do know how

in the spreadsheet you provided (1946 call records) the modal duration is 70 seconds
(average 156.7), but there are some calls that would span an hour.

basically to achieve a much more refined method requires building a table of ate/time boundaries and then counting any call durations starting/finishing or spanning each.

Thank you for the feedback and honesty.

I have found possible methods in MySQL for the missing parts.

In Oracle/SQL Server I would use "recursive queries" to build a table of date boundaries, then join the data to that, and then evaluate the data against the boundaries. Recursive queries don't exist in MySQL (as of now anyway) - so to build the missing parts requires other techniques.

This page identifies some approaches to overcome this, and the last listed stored procedure appears (with a tweak or 2) to be relevant.
http://www.artfulsoftware.com/infotree/qrytip.php?id=95

there's quite a  bit here I'm not that familiar with and I may not have time for it.

How strong are you with MySQL? is that stored proc something you can handle?

what is missing from it (for your purpose is a "second date", i.e. each record should have a start and an end, that proc only gives the start)

here are the tweaks needed on that sp... I have changed some names also:

CREATE PROCEDURE dt_buckets_sp( pstart datetime, pstop datetime, pminutes int ) 
DETERMINISTIC 
BEGIN 
  DECLARE thisdate datetime; 
  DECLARE thatdate datetime; 
  DROP TABLE IF EXISTS dt_buckets; 
  CREATE TABLE dt_buckets( start_at datetime , stop_at datetime);  
  SET thisdate=pstart; 
  SET thatdate = adddate( thisdate, INTERVAL pminutes MINUTE ); 
  INSERT INTO dt_buckets VALUES(pstart, thatdate); 
  WHILE thatdate < pstop DO 
    SET thisdate = adddate( thisdate, INTERVAL pminutes MINUTE ); 
    SET thatdate = adddate( thisdate, INTERVAL pminutes MINUTE ); 
    INSERT INTO dt_buckets VALUES( thisdate, thatdate ); 
  END WHILE; 
END 
/

Select allOpen in new window

usage:

CALL dt_buckets_sp('2013-05-02 00:00:00', '2013-05-10 00:00:00', 60);

select * from dt_buckets
;

see: http://sqlfiddle.com/#!2/c2927a/1

and here is another step in the adventure (something of a bargain it seems)...

http://sqlfiddle.com/#!2/1d7442/1

CALL dt_buckets_sp('2013-05-02 00:00:00', '2013-05-10 00:00:00', 60); 

select
  start_at
, start
, stop
, stop_at
, `Duration of call`
, auto
from dt_buckets as B
left join (
          select
            `auto`
          , `Duration of call`
          , addtime(`Date of call`, `Time`) as start
          , addtime( addtime(`Date of call`, `Time`), SEC_TO_TIME(`Duration of call`)) as stop
          from tTelkomDetailsMaster_Freq
          ) as c on (    c.start >= B.start_at and c.start < B.stop_at
                      or c.stop  >= B.start_at and c.stop  < B.stop_at
                      or B.start_at >= c.start and B.stop_at < c.stop
                    )
where `Duration of call` > 500
order by auto, start_at
;

Select allOpen in new window

what this is doing is spreading the call duration across a number of datetime buckets (e.g. if a call lasts 3 hours you will get a total count of 3, 1 per hour)

from here, we proceed back to the 'pivot' over 24 hours as in the earlier display...

when will this end?

can this question be closed off yet?

Thank you very much. The comparison is appreciated between your 1st and now this implementation. The more significant result is 6 concurrent calls appear at some points with this as opposed to only 5 with estimate. This data was made from a system with a maximum of 8 channels ie. 4xISDN lines and either they never reach peak or perhaps one ISDN ( 2channels per ISDN) is faulty.

I'm going to close question although I've a favor ask...

Thank you for your fine answer and excellent presentation!

If you can perhaps assist me with the coding direction I should take to achieve the 5 minute drill down I would appreciate as I'm not familiar with the "advanced" MySql you have mastered. tx again.

Please note a couple of things...


manually changed:
('2013-05-02 00:00:00', '10:00:00', 0, 10799, 089),
('2013-05-02 00:00:00', '10:00:00', 0, 10801, 090),

for the record, here is the 'drill down' ( using 2013-05-02 at 09:00 )

set @day = '2013-05-02';
set @hour = 9;

CALL dt_buckets_sp( adddate(@day, INTERVAL @hour HOUR) , adddate(@day, INTERVAL (@hour+1) HOUR), 5);
                     
SELECT
      date_format(start_at,'%Y-%m-%d') AS `Date of call`
    , @hour as hour_of_call
    , count( case when minute(start_at) >= 0  and minute(start_at) < 5  then `auto` end ) as mi_0
    , count( case when minute(start_at) >= 5  and minute(start_at) < 10 then `auto` end ) as mi_5
    , count( case when minute(start_at) >= 10 and minute(start_at) < 15 then `auto` end ) as mi_10
    , count( case when minute(start_at) >= 15 and minute(start_at) < 20 then `auto` end ) as mi_15
    , count( case when minute(start_at) >= 20 and minute(start_at) < 25 then `auto` end ) as mi_20
    , count( case when minute(start_at) >= 25 and minute(start_at) < 30 then `auto` end ) as mi_25
    , count( case when minute(start_at) >= 30 and minute(start_at) < 35 then `auto` end ) as mi_30
    , count( case when minute(start_at) >= 35 and minute(start_at) < 40 then `auto` end ) as mi_35
    , count( case when minute(start_at) >= 40 and minute(start_at) < 45 then `auto` end ) as mi_40
    , count( case when minute(start_at) >= 45 and minute(start_at) < 50 then `auto` end ) as mi_45
    , count( case when minute(start_at) >= 50 and minute(start_at) < 55 then `auto` end ) as mi_50
    , count( case when minute(start_at) >= 55 and minute(start_at) < 60 then `auto` end ) as mi_55
    , count(`auto`)          AS concurrent_cpd
    , count(DISTINCT `auto`) AS actual_cpd
FROM (
        SELECT
          start_at
        , START
        , stop
        , stop_at
        , `Duration of call`
        , auto
        FROM dt_buckets AS B
        LEFT JOIN (
                  SELECT
                    `auto`
                  , `Duration of call`
                  , addtime(`Date of call`, `Time`) AS START
                  , addtime( addtime(`Date of call`, `Time`), SEC_TO_TIME(`Duration of call`)) AS stop
                  FROM tTelkomDetailsMaster_Freq
                  ) AS c ON (    c.START >= B.start_at AND c.START < B.stop_at
                              OR c.stop  >= B.start_at AND c.stop  < B.stop_at
                              OR B.start_at >= c.START AND B.stop_at < c.stop
                            )
     ) AS derived
GROUP BY
     date_format(start_at,'%Y-%m-%d')
;

Select allOpen in new window

this can be seen at: http://sqlfiddle.com/#!2/a6396/19 together with the multi-day query

Thank you very much PortletPaul!
Please can you help me understand what these are and why the difference:
CONCURRENT_CPD (8)      ACTUAL_CPD (5)

Does it pertain to this comment of yours?
ID: 39280897

sort of
    , count(`auto`)          AS concurrent_cpd
    , count(DISTINCT `auto`) AS actual_cpd -- nb the "DISTINCT"

cpd = calls per day

actual_cpd
concurrent_cpd

may I make this small point? of course an upgrade of MySQL would allow you to use stored procedures.

http://sqlfiddle.com/#!2/dc220/2

create table ints(i tinyint); 
insert into ints values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 



set @S := '2010-10-01';
set @E := '2010-10-10';

SELECT
    @S + INTERVAL t.i*100 + u.i*10 + v.i HOUR                     AS Start_at 
, ( @S + INTERVAL t.i*100 + u.i*10 + v.i HOUR ) + INTERVAL 1 HOUR AS Stop_at
FROM ints AS t  
JOIN ints AS u 
JOIN ints AS v 
WHERE ( @S + INTERVAL t.i*100 + u.i*10 + v.i HOUR )  < @E
ORDER BY start_at DESC
; 



set @S := '2010-10-09';
set @E := '2010-10-10';

SELECT
    @S + INTERVAL (t.i*100 + u.i*10 + v.i)*5 MINUTE                       AS Start_at 
, ( @S + INTERVAL (t.i*100 + u.i*10 + v.i)*5 MINUTE ) + INTERVAL 5 MINUTE AS Stop_at
FROM ints AS t  
JOIN ints AS u 
JOIN ints AS v 
WHERE ( @S + INTERVAL (t.i*100 + u.i*10 + v.i)*5 MINUTE )  < @E
ORDER BY start_at DESC
; 

Select allOpen in new window

reading:
http://sqlfiddle.com/#!2/dc220/2

Hello Paul Maxwell
this is awesome stuff!!! thank YOU!
I just would like some help with one of your comments

i see that you have a query ( http://sqlfiddle.com/#!2/a6396/19) to report on the number of calls every 5 minutes,  i believe you are hardcoding the hour that you are looking for, the value is 9.

is there a way instead of hardcoding the hour, to display for every hour based on date?

so if i chose, give the number of calls for every 5 minutes, i will get 12 rows, each row represent an hour of the day? and every column in that row will represent a 5 minute interval?


Date                H    0       5     10    15   20    25    30   35    40    45   50   55
2013-05-02      0     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      1     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      2     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      3     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      4    0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      5     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      6     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      7     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      8     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      9     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      10   0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      11   0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      12   0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      1     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      2     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      3     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      4    0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      5     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      6     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      7     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      8     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      9     0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      10   0      0      0      0      0      2      2      1      1      1      0      1
2013-05-02      11   0      0      0      0      0      2      2      1      1      1      0      1


Thank you,

Paul,

I'm sorry about that. that was my first port on this site and will open a new question.

Rami