mysqli - SQL get number of hours on previous rows -

i working on query extracts information store opening , close time. resultset:

rtl_loc_id trans_typcode                  begin_datetime             ---------- ------------------------------ ---------------------------       2390 workstation_open               14.10.01 09:53:43,121000000        2390 workstation_close              14.10.01 23:51:49,729000000        2390 workstation_open               14.10.02 09:57:47,768000000        2390 workstation_close              14.10.02 23:47:00,120000000        2390 workstation_open               14.10.03 09:47:38,949000000        2390 workstation_close              14.10.03 23:45:42,602000000  

6 rows selected

this query:

select rtl_loc_id,trans_typcode, begin_datetime trn_trans where(trans_typcode = 'workstation_open' or trans_typcode='workstation_close') , business_date between '14.10.01 00:00:00' , '14.10.03 00:00:00' order business_date, begin_datetime asc; 

so need calculate number of hours between opening , closing of store , place value new column. put result day in same row instead of 2 separate lines each day.

thanks

this answer assumes mysql since question not tagged oracle begin with. i'm leaving answer here, since might inspire oracle skills toward solution...

assuming location opens before closes, quick , dirty solution this:

select rtl_loc_id, date(business_date),      min(business_date) [opentime],     max(business_date) [closetime]      trn_trans where(trans_typcode = 'workstation_open' or trans_typcode='workstation_close') , business_date between '14.10.01 00:00:00' , '14.10.03 00:00:00' group rtl_loc_id, date(business_date) order date(business_date) 

or if want pedantic:

select rtl_loc_id, date(business_date),      max(case trans_typcode when 'workstation_open' business_date else null end) [opentime],     max(case trans_typcode when 'workstation_close' business_date else null end) [closetime],     -- rest of query same above --