query help

Printable View

03-06-2003, 04:23 PM
prodadmin

query help

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!!!
03-06-2003, 04:46 PM
lesstjm

Log Out Time

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?
03-07-2003, 09:25 AM
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'
03-09-2003, 12:12 PM
Sameer

Re: Log Out Time

Quote:

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

Code:

SELECT studentno, MIN (logintime) login_time, MAX(logintime) logout_time, (MAX(logintime) - MIN(logintime)) * 24 Time_in_Hours FROM students GROUP BY studentno
03-10-2003, 11:45 AM
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..
03-10-2003, 01:53 PM
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')
03-10-2003, 02:14 PM
prodadmin

The query was pretty close what i was looking for.

Thanks a ton....
03-10-2003, 02:15 PM
Sameer

Quote:

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

Code:

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