| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: need urgent help
On Nov 20, 2:11 am, Bhishm <bhis..._at_gmail.com> wrote:
> Hi,
>
> I am creating a attendance sheet software for inhouse use.
>
> my data is like this:-
>
> -------------------------------------------------------------------------------------------------
> | name | login time | logout
> time |
> -------------------------------------------------------------------------------------------------
> | a | 2007-11-10 12:00:00 | 2007-11-10
> 16:00:00 |
> -------------------------------------------------------------------------------------------------
> | b | 2007-11-10 15:00:00 | 2007-11-10
> 18:00:00 |
> -------------------------------------------------------------------------------------------------
>
> My requirement:-
>
> I want to generate an hourly report like this:-
> ---------------------------------------------------------------------------------------------
> date time range total people logged
> in
> ----------------------------------------------------------------------------------------------
> 2007-11-10 0 -2 0
> ---------------------------------------------------------------------------------------------
> 2007-12-10 2-4 0
> ---------------------------------------------------------------------------------------------
> .
> .
> --------------------------------------------------------------------------------------------
> 2007-11-10 12-14 1
> --------------------------------------------------------------------------------------------
> 2007-11-10 14-16 2
> ---------------------------------------------------------------------------------------------
> 2007-11-10 16-18 1
> -------------------------------------------------------------------------------------------------
> .
> .
> ----------------------------------------------------------------------------------------------
> 2007-11-10 22-24 0
> ---------------------------------------------------------------------------------------------
>
> This is what I want to creat , but I don't know how can I generate
> such kind of report.
>
> Can you please guide me for the same. Please reply urgently.
>
> Thanks & Regards,
> Bhishm
First, the setup:
CREATE TABLE T1 (
USERNAME VARCHAR2(15),
LOGIN_TIME DATE,
LOGOUT_TIME DATE);
INSERT INTO
T1
VALUES(
'a',
TO_DATE('2007-11-10 12:00','YYYY-MM-DD HH24:MI'),
TO_DATE('2007-11-10 16:00','YYYY-MM-DD HH24:MI'));
INSERT INTO
T1
VALUES(
'b',
TO_DATE('2007-11-10 15:00','YYYY-MM-DD HH24:MI'),
TO_DATE('2007-11-10 18:00','YYYY-MM-DD HH24:MI'));
COMMIT;
One of the challenges that you will face is working around the need to
generate up to 12 rows (1 for each of the possible time periods) for
each row in your table. A second problem is how to handle logins that
occur before midnight, with a corresponding logout that occurs after
midnight. If I knew that there would be no time periods that cross
midnight, I might try to build a solution like this:
SELECT
TRUNC(LOGIN_TIME) CHECK_DATE,
TO_NUMBER(TO_CHAR(LOGIN_TIME,'HH24')) LOGIN_HOUR,
TO_NUMBER(TO_CHAR(LOGOUT_TIME,'HH24')) LOGOUT_HOUR
FROM
T1;
CHECK_DAT LOGIN_HOUR LOGOUT_HOUR
--------- ---------- ----------- 10-NOV-07 12 16 10-NOV-07 15 18
The above just simplifies the input table into dates, login hour and logout hour.
Next, we need a way to generate 12 rows. You could just use an
existing table, and specify that you want to return all rows where
ROWNUM<=12, but we will use CONNECT BY LEVEL, which will likely result
in greater CPU consumption, but would likely be more portable:
SELECT
(LEVEL-1)*2 LOGIN_COUNTER,
(LEVEL-1)*2+2 LOGOUT_COUNTER
FROM
DUAL
CONNECT BY
LEVEL<=12;
LOGIN_COUNTER LOGOUT_COUNTER
------------- --------------
0 2
2 4
4 6
6 8
8 10
10 12
12 14
14 16
16 18
18 20
20 22
22 24
Now that we have the two simplified data sets, we just need to find
where the two data sets intersect. First, let's find those records
where the numbers from the counter fall between the LOGIN_HOUR and the
LOGOUT_HOUR:
SELECT
T.CHECK_DATE, T.LOGIN_HOUR, T.LOGOUT_HOUR,
CHECK_DAT LOGIN_HOUR LOGOUT_HOUR TIME_RA
--------- ---------- ----------- ------- 10-NOV-07 12 16 12- 14 10-NOV-07 12 16 14- 16 10-NOV-07 15 18 16- 18
You may notice that we are missing one row. Let's see if we can find
a way to include the missing row:
SELECT
T.CHECK_DATE, T.LOGIN_HOUR, T.LOGOUT_HOUR,
CHECK_DAT LOGIN_HOUR LOGOUT_HOUR TIME_RA
--------- ---------- ----------- ------- 10-NOV-07 12 16 12- 14 10-NOV-07 12 16 14- 16 10-NOV-07 15 18 14- 16 10-NOV-07 15 18 16- 18
By also allowing the LOGIN_HOUR to fall between the LOGIN_COUNTER and
LOGOUT_COUNTER, or the LOGOUT_HOUR to fall between the LOGIN_COUNTER
and LOGOUT_COUNTER (with a slight adjustment), we pick up the missing
row. Now, it is a simple matter to find the total number in each time
period:
SELECT
T.CHECK_DATE,
TO_CHAR(LOGIN_COUNTER,'99')||'-'||TO_CHAR(LOGOUT_COUNTER,'99')
TIME_RANGE,
COUNT(*) TOTAL_PEOPLE
FROM
(SELECT
TRUNC(LOGIN_TIME) CHECK_DATE,
TO_NUMBER(TO_CHAR(LOGIN_TIME,'HH24')) LOGIN_HOUR,
TO_NUMBER(TO_CHAR(LOGOUT_TIME,'HH24')) LOGOUT_HOUR
FROM
T1) T,
(SELECT
(LEVEL-1)*2 LOGIN_COUNTER,
(LEVEL-1)*2+2 LOGOUT_COUNTER
FROM
DUAL
CONNECT BY
LEVEL<=12) C
WHERE
(C.LOGIN_COUNTER BETWEEN T.LOGIN_HOUR AND T.LOGOUT_HOUR
AND C.LOGOUT_COUNTER BETWEEN T.LOGIN_HOUR AND T.LOGOUT_HOUR)
OR T.LOGIN_HOUR BETWEEN C.LOGIN_COUNTER AND C.LOGOUT_COUNTER-1
OR T.LOGOUT_HOUR BETWEEN C.LOGIN_COUNTER+1 AND C.LOGOUT_COUNTER
GROUP BY
T.CHECK_DATE,
TO_CHAR(LOGIN_COUNTER,'99')||'-'||TO_CHAR(LOGOUT_COUNTER,'99')
ORDER BY
1,
2;
CHECK_DAT TIME_RA TOTAL_PEOPLE
--------- ------- ------------ 10-NOV-07 12- 14 1 10-NOV-07 14- 16 2 10-NOV-07 16- 18 1
The above likely is not the only solution to the problem.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Nov 23 2007 - 09:30:10 CST
 |
 |