Re: need urgent help

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Fri, 23 Nov 2007 11:14:53 -0800 (PST)
Message-ID: <860dda6c-37c6-42d5-841b-545f5de9b14f@x69g2000hsx.googlegroups.com>

On Nov 23, 10:30 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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;
> (SNIP)
> 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.-

For the sake of completeness, let's take another look at the problem. What if there is a need for the time intervals to cross midnight. We need to make a couple adjustments. First, let's add another row for variety:

INSERT INTO
  T1
VALUES(
  'c',
  TO_DATE('2007-11-10 13:00','YYYY-MM-DD HH24:MI'),   TO_DATE('2007-11-10 19:00','YYYY-MM-DD HH24:MI')); The select from earlier:
SELECT
  LOGIN_TIME,
  TO_NUMBER(TO_CHAR(LOGIN_TIME,'HH24')) LOGIN_HOUR,   TO_NUMBER(TO_CHAR(LOGOUT_TIME,'HH24')) LOGOUT_HOUR FROM
  T1;

LOGIN_TIM LOGIN_HOUR LOGOUT_HOUR

--------- ---------- -----------
10-NOV-07         12          16
10-NOV-07         15          18
10-NOV-07         13          19

We will modified the SQL statement above to produce the same output, with a little bit more efficiency:
SELECT
  LOGIN_TIME,
  (LOGIN_TIME-TRUNC(LOGIN_TIME))*24 LOGIN_HOUR,   (LOGOUT_TIME-TRUNC(LOGOUT_TIME))*24 LOGOUT_HOUR FROM
  T1;

LOGIN_TIM LOGIN_HOUR LOGOUT_HOUR

--------- ---------- -----------
10-NOV-07         12          16
10-NOV-07         15          18
10-NOV-07         13          19

Now, we need to round the clock in and clock out times to two hour intervals - note that the LOGOUT_HOUR_A of the last row was rounded up:
SELECT
  LOGIN_TIME,
  (LOGIN_TIME-TRUNC(LOGIN_TIME))*24 LOGIN_HOUR,   (LOGOUT_TIME-TRUNC(LOGOUT_TIME))*24 LOGOUT_HOUR,   FLOOR((LOGIN_TIME-TRUNC(LOGIN_TIME))*24/2)*2 LOGIN_HOUR_A,   CEIL((LOGOUT_TIME-TRUNC(LOGOUT_TIME))*24/2)*2 LOGOUT_HOUR_A FROM
  T1;

LOGIN_TIM LOGIN_HOUR LOGOUT_HOUR LOGIN_HOUR_A LOGOUT_HOUR_A

--------- ---------- ----------- ------------ -------------
10-NOV-07         12          16           12            16
10-NOV-07         15          18           14            18
10-NOV-07         13          19           12            20

Let's take the above hours and translate them back into date/time values and determine the number of intervals between the adjusted LOGIN_HOUR_A and LOGOUT_HOUR_A:
SELECT
  LOGIN_TIME,
  LOGOUT_TIME,
  TRUNC(LOGIN_TIME)+(FLOOR((LOGIN_TIME-TRUNC(LOGIN_TIME))*24/2)*2)/24 LOGIN_HOUR_A,
  TRUNC(LOGOUT_TIME)+(CEIL((LOGOUT_TIME-TRUNC(LOGOUT_TIME))*24/2)*2)/ 24 LOGOUT_HOUR_A,
  ((TRUNC(LOGOUT_TIME)+(CEIL((LOGOUT_TIME-TRUNC(LOGOUT_TIME))*24/2)*2)/ 24)

(TRUNC(LOGIN_TIME)+(FLOOR((LOGIN_TIME-TRUNC(LOGIN_TIME))*24/2)*2)/ 24))*12 H FROM T1;

LOGIN_TIME           LOGOUT_TIME          LOGIN_HOUR_A
LOGOUT_HOUR_A        H
==================== ==================== ====================
==================== =
10-NOV-2007 12:00:00 10-NOV-2007 16:00:00 10-NOV-2007 12:00:00 10-
NOV-2007 16:00:00 2
10-NOV-2007 15:00:00 10-NOV-2007 18:00:00 10-NOV-2007 14:00:00 10-
NOV-2007 18:00:00 2

10-NOV-2007 13:00:00 10-NOV-2007 19:00:00 10-NOV-2007 12:00:00 10- NOV-2007 20:00:00 4 We then combine the above with a simple counter that counts from 1 up to 12, only joining those rows from the counter that are less than or equal to the calculated number of intervals. By adding the number of hours determined by the counter to the adjusted LOGIN_HOUR_A, we obtain the time intervals:
SELECT

  T.LOGIN_TIME,
  T.LOGOUT_TIME,
  T.LOGIN_HOUR_A+(C.COUNTER*2-2)/24 TIME_START,
  T.LOGIN_HOUR_A+(C.COUNTER*2)/24 TIME_END

FROM
  (SELECT
    LOGIN_TIME,
    LOGOUT_TIME,
    TRUNC(LOGIN_TIME)+(FLOOR((LOGIN_TIME-TRUNC(LOGIN_TIME))*24/2)*2)/ 24 LOGIN_HOUR_A,
    TRUNC(LOGOUT_TIME)+(CEIL((LOGOUT_TIME-TRUNC(LOGOUT_TIME))*24/2)*2)/ 24 LOGOUT_HOUR_A,
    ((TRUNC(LOGOUT_TIME)+(CEIL((LOGOUT_TIME- TRUNC(LOGOUT_TIME))*24/2)*2)/24)

(TRUNC(LOGIN_TIME)+(FLOOR((LOGIN_TIMETRUNC (LOGIN_TIME))*24/2)*2)/24))*12 H FROM T1) T, (SELECT LEVEL COUNTER FROM DUAL CONNECT BY LEVEL<=12) C WHERE T.H>=C.COUNTER;

LOGIN_TIME LOGOUT_TIME TIME_START TIME_END

==================== ==================== ====================
====================
10-NOV-2007 12:00:00 10-NOV-2007 16:00:00 10-NOV-2007 12:00:00 10-
NOV-2007 14:00:00
10-NOV-2007 15:00:00 10-NOV-2007 18:00:00 10-NOV-2007 14:00:00 10-
NOV-2007 16:00:00
10-NOV-2007 13:00:00 10-NOV-2007 19:00:00 10-NOV-2007 12:00:00 10-
NOV-2007 14:00:00
10-NOV-2007 12:00:00 10-NOV-2007 16:00:00 10-NOV-2007 14:00:00 10-
NOV-2007 16:00:00
10-NOV-2007 15:00:00 10-NOV-2007 18:00:00 10-NOV-2007 16:00:00 10-
NOV-2007 18:00:00
10-NOV-2007 13:00:00 10-NOV-2007 19:00:00 10-NOV-2007 14:00:00 10-
NOV-2007 16:00:00
10-NOV-2007 13:00:00 10-NOV-2007 19:00:00 10-NOV-2007 16:00:00 10-
NOV-2007 18:00:00

10-NOV-2007 13:00:00 10-NOV-2007 19:00:00 10-NOV-2007 18:00:00 10- NOV-2007 20:00:00 The final step is to perform a group by: SELECT
  CHECK_DATE,
  TO_CHAR(TIME_START,'HH24')||'-'||TO_CHAR(TIME_END,'HH24') TIME_RANGE,
  COUNT(*) TOTAL_PEOPLE
FROM
(SELECT
  TRUNC(T.LOGIN_HOUR_A+(C.COUNTER*2-2)/24) CHECK_DATE,   T.LOGIN_HOUR_A+(C.COUNTER*2-2)/24 TIME_START,   T.LOGIN_HOUR_A+(C.COUNTER*2)/24 TIME_END FROM
  (SELECT
    LOGIN_TIME,
    LOGOUT_TIME,
    TRUNC(LOGIN_TIME)+(FLOOR((LOGIN_TIME-TRUNC(LOGIN_TIME))*24/2)*2)/ 24 LOGIN_HOUR_A,
    TRUNC(LOGOUT_TIME)+(CEIL((LOGOUT_TIME-TRUNC(LOGOUT_TIME))*24/2)*2)/ 24 LOGOUT_HOUR_A,
    ((TRUNC(LOGOUT_TIME)+(CEIL((LOGOUT_TIME- TRUNC(LOGOUT_TIME))*24/2)*2)/24)

(TRUNC(LOGIN_TIME)+(FLOOR((LOGIN_TIMETRUNC (LOGIN_TIME))*24/2)*2)/24))*12 H FROM T1) T, (SELECT LEVEL COUNTER FROM DUAL CONNECT BY LEVEL<=12) C WHERE T.H>=C.COUNTER) GROUP BY CHECK_DATE, TO_CHAR(TIME_START,'HH24')||'-'||TO_CHAR(TIME_END,'HH24') ORDER BY 1, 2;

CHECK_DAT TIME_ TOTAL_PEOPLE

--------- ----- ------------
10-NOV-07 12-14            2
10-NOV-07 14-16            3
10-NOV-07 16-18            2
10-NOV-07 18-20            1

Let's try an example that crosses midnight: INSERT INTO
  T1
VALUES(
  'c',
  TO_DATE('2007-11-10 19:00','YYYY-MM-DD HH24:MI'),   TO_DATE('2007-11-11 04:00','YYYY-MM-DD HH24:MI')); CHECK_DAT TIME_ TOTAL_PEOPLE

--------- ----- ------------
10-NOV-07 12-14            2
10-NOV-07 14-16            3
10-NOV-07 16-18            2
10-NOV-07 18-20            2
10-NOV-07 20-22            1
10-NOV-07 22-00            1
11-NOV-07 00-02            1
11-NOV-07 02-04            1

The original output would look like this using the above SQL statement:
CHECK_DAT TIME_ TOTAL_PEOPLE

--------- ----- ------------
10-NOV-07 12-14            1
10-NOV-07 14-16            2
10-NOV-07 16-18            1

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Nov 23 2007 - 13:14:53 CST