Joined: 18 Mar 2008
Location: Malaysia
Online Status: Offline
Posts: 4
Topic: time calculation Posted: 23 Mar 2008 at 8:24pm
Hi everyone,
ID Condition Time Floor
1 1 10:54:48 AM 1 2 1 10:54:48 AM 2 9 0 10:55:02 AM 2 10 0 10:55:16 AM 1
I'm trying to create a graph chart based on Count of Time by Floor and Condition. But the problem is, i dont know how to create a formula to calculate "time" from the above database. Can everyone please tell me how to create a formula to calculate time from above database. The basic idea to calculate the time is to subtract the "time" that has condition "0" by "time" that has condition "1".
ID Condition Start_End_Time Start_End_Date Floors
--- ------------- -------------------- ------------------- --------
1 1 10:54AM Feb 21 2008 1
6 0 6:55PM Feb 21 2008 1
2 1 10:54AM Feb 21 2008 2
4 0 7:54PM Feb 21 2008 2
5 1 10:54AM Feb 21 2008 3
7 0 8:55PM Feb 21 2008 3
3 1 10:54AM Feb 21 2008 4
8 0 9:55PM Feb 21 2008 4
If the table looks like the above one, then type the following
Query in the “Add Command “of the Database Expert.
select
t1.Floors
,t1.Start_End_Date
,t1.Start_End_Time as Start_Time
,t2.Start_End_Time as End_Time
,datediff(hh, t1.Start_End_Time , t2.Start_End_Time ) as Duration
From timetable t1
join timetable t2
on t1.Floors = t2.Floors
where t1.Condition = 1
and t2.Condition = 0
output:
Floors Start_End_Date Start_Time End_Time Duration
-------- ------------------- ------------- ------------- -----------
1 Feb 21 2008 10:54AM 6:55PM 8
2 Feb 21 2008 10:54AM 7:54PM 9
3 Feb 21 2008 10:54AM 8:55PM 10
4 Feb 21 2008 10:54AM 9:55PM 11
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
