calculation of difference between time

[jayati_shrivast]

Hi All,

I have a table with field DATE(in the format DD-MM-YYYY, hh:mm:ss) and STATUS field(with values AVAILABLE, OFFLINE)

I have created a straight table, under that i added field as date(DATE), STATUS, and two more columns as measure i.e AVAILABLE AND UNAVAILABLE

FOR AVAILABLE measure i applied expression in the expression editor as :- = if(STATUS = 'AVAILABLE', time(DATE)) 

FOR OFFLINE measure I applied expression in the expression editor as:- if(STATUS = 'AVAILABLE', time(DATE)) 

Now I need to add one more measure in the table as TOTAL AVAILABLE DURATION( which means difference between offline time - available time.

for this I used the following expression in the data load editor script

Interval(if(STATUS = 'OFFLINE',time(DATE)) - if(STATUS = 'AVAILABLE',time(DATE))) as total_duration.

But in the front end, in table I am not getting the values for this total_duration column.

Hereby I am attaching the table layout structure

how can i calculate diff between offline and available for a particular date, and show it in separate column as total available duration??

kindly help.

THANKS

[Channa]

try to have them in sigle row if you using datebase

select a.date,a.status,availabe,b.status,b.ofline from

(select date,Status,Available from table1 where status='Available ') a

(select date,Status,offline from table1 where status='Ofline') b

where a.date=b.date

after this your date will be in one row then you can use Available-offline

[jayati_shrivast]

Thanks but from the database I have only 2 columns i.e date and status, the other 2 columns I have created by hardcoding it as Explained above. How can I implement this in the script??

[Channa]

ok

try to  make status in single row

like OnlineStatus Offlinestatus

later add your expressions you need to have single row

select a.date,a.status,b.status from

(select date,Status from table1 where status='Available ') a

(select date,Status from table1 where status='Ofline') b

where a.date=b.date

may be