query help

[prodadmin]

guys..

I need some help on a query.
I need to query how many hours one student logged on perday.for exapmle the login time for 202050 is 15:53:48 and logout time is 15:58:19

Studentno logintime
----------- ----------
202050 21-MAY-2002 15:53:48
202050 21-MAY-2002 15:53:46
202050 21-MAY-2002 15:54:50
202050 21-MAY-2002 15:55:52
202050 21-MAY-2002 15:56:54
202050 21-MAY-2002 15:57:56
202050 21-MAY-2002 15:58:19

202051 22-MAY-2002 11:53:48
202051 22-MAY-2002 12:53:46
202051 22-MAY-2002 12:54:50
202051 22-MAY-2002 15:55:52

202051 23-MAY-2002 15:56:54
202051 23-MAY-2002 15:57:56
202051 23-MAY-2002 15:58:19

any help is much appreciated!!!

[lesstjm]

How do you know what time they logged out. It seems that you have a lot of log in times but what about log out times?

[coderodeo]

SELECT TO_DATE(MIN(logintime),'dd-mon-yyyy')-TO_DATE(MAX(logintime),''dd-mon-yyyy'')*24*60 FROM my TABLE WHERE studentno=202050 AND TRUNC(logintime)='21-MAY-2002'

[Sameer]

Originally posted by lesstjm
How do you know what time they logged out. It seems that you have a lot of log in times but what about log out times?

Hope you assume min time as Login time and max time as logged out time

SELECT
studentno,
MIN (logintime) login_time,
MAX(logintime) logout_time,
(MAX(logintime) - MIN(logintime)) * 24 Time_in_Hours
FROM
students
GROUP BY studentno

[prodadmin]

thank you guys for the help.

Sameer..the query you provided works fine for a day but if i give a date range more than a day it's not working.

Let's say the student logged in twice for today( 2 hours) and he logged in next day for 3 times( 2 hours) .which is 4 hours total for the 2 days but the query results a big number.

i believe the query is calculating the non-logged time between these two days.

guys is there any way we can tell the query to calculate the logged in time only and don't calculate the non -logged time when we give date range.

thanks..

[slimdave]

are you looking for something like ...

SELECT
studentno,
TRUNC(logintime,'DD') login_day,
MIN (logintime) login_time,
MAX(logintime) logout_time,
(MAX(logintime) - MIN(logintime)) * 24 Time_in_Hours
FROM
students
GROUP BY studentno,TRUNC(logintime,'DD')

[prodadmin]

The query was pretty close what i was looking for.

Thanks a ton....

[Sameer]

Originally posted by prodadmin
thank you guys for the help.

Sameer..the query you provided works fine for a day but if i give a date range more than a day it's not working.

Let's say the student logged in twice for today( 2 hours) and he logged in next day for 3 times( 2 hours) .which is 4 hours total for the 2 days but the query results a big number.


For this requirement I guess you need one more column which stores the counter for the number of loggings per student per day. In the following example, on 23rd student 202051 has logged in twice, first at 15:56 Hrs and second at 17:56


SQL> SELECT * FROM students;

STUDENTNO LOGINTIME ID
---------- -------------------- ----------
202050 21-MAY-2002 15:53:48 1
202050 21-MAY-2002 15:53:46 1
202050 21-MAY-2002 15:54:50 1
202050 21-MAY-2002 15:55:52 1
202050 21-MAY-2002 15:56:54 1
202050 21-MAY-2002 15:57:56 1
202050 21-MAY-2002 15:58:19 1
202051 22-MAY-2002 11:53:48 1
202051 22-MAY-2002 12:53:46 1
202051 22-MAY-2002 12:54:50 1
202051 22-MAY-2002 15:55:52 1
202051 23-MAY-2002 15:56:54 1
202051 23-MAY-2002 15:57:56 1
202051 23-MAY-2002 15:58:19 1
202051 23-MAY-2002 17:56:54 2
202051 23-MAY-2002 17:57:56 2
202051 23-MAY-2002 18:58:19 2

17 rows selected.

SQL> SELECT
2 studentno,
3 MIN (logintime) login_time,
4 MAX(logintime) logout_time,
5 (MAX(logintime) - MIN(logintime)) * 24 Time_in_Hours
6 FROM
7 students GROUP BY studentno, TRUNC(logintime), id;

STUDENTNO LOGIN_TIME LOGOUT_TIME TIME_IN_HOURS
---------- -------------------- -------------------- -------------
202050 21-MAY-2002 15:53:46 21-MAY-2002 15:58:19 .075833333
202051 22-MAY-2002 11:53:48 22-MAY-2002 15:55:52 4.03444444
202051 23-MAY-2002 15:56:54 23-MAY-2002 15:58:19 .023611111
202051 23-MAY-2002 17:56:54 23-MAY-2002 18:58:19 1.02361111

SQL> SELECT studentno, SUM(time_in_hours) time_in_hours
2 FROM (
3 SELECT
4 studentno,
5 MIN (logintime) login_time,
6 MAX(logintime) logout_time,
7 (MAX(logintime) - MIN(logintime)) * 24 Time_in_Hours
8 FROM
9 students
10 GROUP BY studentno, TRUNC(logintime), id
11 )
12 GROUP BY studentno ;

STUDENTNO TIME_IN_HOURS
---------- ------------------
202050 .075833333
202051 5.08166667



HTH
Sameer