Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2003
    Posts
    25

    Unanswered: two rows combine into one

    hello,

    how can u combine two rows into one....

    e.g. date in out
    27-aug-03 17:27
    28-aug-03 17:30 06:00
    29-aug-03 17:00 06:10

    i want to combine 27-aug-03 (in) and 28-aug-03(out) into one
    record...and so on.....

    date in out
    27-aug-03 17:27 06:00
    28-aug-03 17:30 06:10

    please help me......

    thanks,
    ynoel

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    well, what the heck is the rule?
    how much data are we talking about here?
    Is it all in one table?
    What are the columns on the table?

    are there only 3 rows?

    how is it grouped? and on what columns?

    I fail to see the relationship between Aug 27 with no out timestamp and Aug 28 with an out timestamp ...

    with that in mind you would need something like this
    PHP Code:
    select I.in_date
    (
    select out_date 
    from table 
    where in_date 

    (
    select min(end_dt
    from table 
    where end_dt 
    I.in_date))
    from table
    granted this is with no info.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Posts
    25
    Hi The_Duck,


    Thanks for your reply. For more details here are some explanation.
    From 01-aug-03 thru 05-aug-03 is on day shift, no problem with in and out because they fall into same date, also on 06-aug-03 which is 3:00pm to 11:00 pm schedule, but from 07-aug-03 to 10-aug-03 which is on night shift, in on this date and out is tommorrow. I want to store in and out into one date only (the date when the in falls) . Please see below.....

    The data looks like this .......

    ATTENDANCE_DATE in out
    -------------------- ----- -----
    01-AUG-03 06:33 18:04
    02-AUG-03 06:34 17:00
    04-AUG-03 06:32 16:00
    05-AUG-03 06:37 18:07
    06-AUG-03 14:40 23:02
    07-AUG-03 17:27
    08-AUG-03 17:40 06:30
    09-AUG-03 17:40 06:04
    10-AUG-03 06:01
    11-AUG-03 06:36 16:01
    12-AUG-03 06:35 17:09
    13-AUG-03 06:36 17:00
    14-AUG-03 06:35 17:00
    15-AUG-03 06:26 16:01

    data of aug-7,8 and 9 should look like this.....

    07-AUG-03 17:27 06:30
    08-AUG-03 17:40 06:04
    09-AUG-03 17:40 06:01

    my sql statements ....

    SQLWKS> create or replace view attendance_transactions_view
    2> as
    3> select a.id_number, a.attendance_date, a.actual_time_in,
    4> DECODE(SIGN((to_number(to_char(a.actual_time_in,'H H24MI'))/1500)-1),-1,
    5> a.actual_time_out,1,
    6> b.OUT, a.actual_time_out)"ACTUAL_TIME_OUT",
    7> a.balik_overtime_in, a.balik_overtime_out,
    8> a.with_logbox_validation, a.working_time_code
    9> from ATTENDANCE_TRANSACTIONS_W a,
    10> ( select id_number, attendance_date, actual_time_out "OUT"
    11> from ATTENDANCE_TRANSACTIONS_W)b
    12> where a.id_number = b.id_number
    13> and a.attendance_date = b.attendance_date - 1
    14>
    Statement processed.

    and i got the following results.......(07-aug, 08-aug ang 09-aug , that's what i want)

    ATTENDANCE_DATE in out
    -------------------- ----- -----
    01-AUG-03 06:33 18:04
    04-AUG-03 06:32 16:00
    05-AUG-03 06:37 18:07
    06-AUG-03 14:40 23:02
    07-AUG-03 17:27 06:30
    08-AUG-03 17:40 06:04
    09-AUG-03 17:40 06:01
    10-AUG-03
    11-AUG-03 06:36 16:01
    12-AUG-03 06:35 17:09
    13-AUG-03 06:36 17:00
    14-AUG-03 06:35 17:00
    12 rows selected.

    but have a problem on 02-aug and 15-aug, they're gone...
    what is wrong with my sql????
    can u pls help me......
    Last edited by ynoel; 08-27-03 at 17:38.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    what about this??

    PHP Code:
    16:56:52 kod:schemaSELECT attendance_date AS DAY
    16:56:59   2  actual_time_in t_in
    16:56:59   3  actual_time_out AS t_out 
    16
    :56:59   4  FROM ATTENDANCE_TRANSACTIONS;

    DAY       T_IN       T_OUT
    --------- ---------- ----------
    01-AUG-03 06:33      18:04
    02
    -AUG-03 06:34      17:00
    04
    -AUG-03 06:32      16:00
    05
    -AUG-03 06:37      18:07
    06
    -AUG-03 14:40      23:02
    07
    -AUG-03 17:27
    08
    -AUG-03 17:40      06:30
    09
    -AUG-03 17:40      06:04
    10
    -AUG-03 06:01
    11
    -AUG-03 06:36      16:01
    12
    -AUG-03 06:35      17:09

    DAY       T_IN       T_OUT
    --------- ---------- ----------
    13-AUG-03 06:36      17:00
    14
    -AUG-03 06:35      17:00
    15
    -AUG-03 06:26      16:01

    14 rows selected
    .

    Elapsed00:00:00.00
    16
    :57:00 kod:schemaSELECT  
    16
    :57:05   2  DAY
    16:57:05   3  t_IN
    16:57:05   4  CASE 
    16:57:05   5  WHEN t_out IS NOT NULL THEN t_out
    16
    :57:05   6  WHEN t_OUT IS NULL THEN 
    16
    :57:05   7   (SELECT actual_time_out 
    16
    :57:05   8   FROM ATTENDANCE_TRANSACTIONS 
    16
    :57:05   9   WHERE attendance_date 
    16:57:05  10   (SELECT MIN(attendance_date
    16:57:05  11   FROM ATTENDANCE_TRANSACTIONS 
    16
    :57:05  12   WHERE attendance_dateDAY))
    16:57:05  13  END AS NEW
    16:57:05  14  FROM (
    16:57:05  15  SELECT attendance_date AS DAY
    16:57:05  16  actual_time_in t_in
    16:57:05  17  actual_time_out AS t_out 
    16
    :57:05  18  FROM ATTENDANCE_TRANSACTIONS
    16
    :57:06  19  );

    DAY       T_IN       NEW
    --------- ---------- ----------
    01-AUG-03 06:33      18:04
    02
    -AUG-03 06:34      17:00
    04
    -AUG-03 06:32      16:00
    05
    -AUG-03 06:37      18:07
    06
    -AUG-03 14:40      23:02
    07
    -AUG-03 17:27      06:30
    08
    -AUG-03 17:40      06:30
    09
    -AUG-03 17:40      06:04
    10
    -AUG-03 06:01      16:01
    11
    -AUG-03 06:36      16:01
    12
    -AUG-03 06:35      17:09

    DAY       T_IN       
    NEW
    --------- ---------- ----------
    13-AUG-03 06:36      17:00
    14
    -AUG-03 06:35      17:00
    15
    -AUG-03 06:26      16:01

    14 rows selected
    .

    Elapsed00:00:00.01
    16
    :57:06 kod:schema
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Aug 2003
    Posts
    25
    Hi The_Duck,

    Thank you very much for ur reply. You're given output is exactly what i want... but

    I tried the given sql... and this is the result... CASE is new to me....
    I'm using Oracle 8.0.4 in Sun Sparc Solaris (UNIX).
    Please advise...

    SQLWKS> SELECT attendance_date AS DAY,
    2> to_char(actual_time_in,'hh24:mi') t_in,
    3> to_char(actual_time_out,'hh24:mi') AS t_out
    4> FROM ATTENDANCE_TRANSACTIONS_W
    5> where id_number = '871083';
    DAY T_IN T_OUT
    -------------------- ----- -----
    01-AUG-03 06:33 18:04
    02-AUG-03 06:34 17:00
    04-AUG-03 06:32 16:00
    05-AUG-03 06:37 18:07
    06-AUG-03 14:40 23:02
    07-AUG-03 17:27
    08-AUG-03 17:40 06:30
    09-AUG-03 17:40 06:04
    10-AUG-03 06:01
    11-AUG-03 06:36 16:01
    12-AUG-03 06:35 17:09
    13-AUG-03 06:36 17:00
    14-AUG-03 06:35 17:00
    15-AUG-03 06:26 16:01
    14 rows selected.
    SQLWKS>
    SQLWKS> SELECT
    2> DAY,
    3> t_IN,
    4> CASE
    5> WHEN t_out IS NOT NULL THEN t_out
    6> WHEN t_OUT IS NULL THEN
    7> (SELECT to_char(actual_time_out,'hh24:mi')
    8> FROM ATTENDANCE_TRANSACTIONS_W
    9> WHERE attendance_date =
    10> (SELECT MIN(attendance_date)
    11> FROM ATTENDANCE_TRANSACTIONS_W
    12> WHERE attendance_date> DAY))
    13> END AS NEW
    14> FROM (
    15> SELECT attendance_date AS DAY,
    16> to_char(actual_time_in,'hh24:mi') t_in
    17> to_char(actual_time_out.'hh24:mi') AS t_out
    18> FROM ATTENDANCE_TRANSACTIONS_W );
    WHEN t_out IS NOT NULL THEN t_out
    *
    ORA-00923: FROM keyword not found where expected
    SQLWKS>

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by ynoel
    Hi The_Duck,

    Thank you very much for ur reply. You're given output is exactly what i want... but

    17> to_char(actual_time_out.'hh24:mi') AS t_out
    18> FROM ATTENDANCE_TRANSACTIONS_W );
    WHEN t_out IS NOT NULL THEN t_out
    *
    ORA-00923: FROM keyword not found where expected
    SQLWKS>
    Line 17 has a period instead of a comma.

    So IN and OUT are dates with timestamps huh?
    I'll run a test with that and get back to you.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    this should work
    PHP Code:
    10:18:22 kod:latformSELECT  
    10
    :25:37   2  DAY
    10:25:37   3  TO_CHAR(t_IN'HH24:MI') AS T_IN
    10:25:37   4  CASE 
    10:25:37   5  WHEN t_out IS NOT NULL THEN TO_CHAR(t_out'HH24:MI')
    10:25:37   6  WHEN t_OUT IS NULL THEN 
    10
    :25:38   7   (SELECT TO_CHAR(actual_time_out'HH24:MI')
    10:25:38   8   FROM ATTENDANCE_TRANSACTIONS 
    10
    :25:38   9   WHERE attendance_date 
    10:25:38  10   (SELECT MIN(attendance_date
    10:25:38  11   FROM ATTENDANCE_TRANSACTIONS 
    10
    :25:38  12   WHERE attendance_date DAY))
    10:25:38  13  END AS NEW
    10:25:38  14  FROM (
    10:25:38  15  SELECT attendance_date AS DAY
    10:25:38  16  actual_time_in t_in
    10:25:38  17  actual_time_out AS t_out 
    10
    :25:38  18  FROM ATTENDANCE_TRANSACTIONS
    10
    :25:38  19  );

    DAY       T_IN  NEW
    --------- ----- -----
    01-AUG-03 06:33 18:04
    02
    -AUG-03 06:34 17:00
    04
    -AUG-03 06:32 16:00
    05
    -AUG-03 06:37 18:07
    06
    -AUG-03 14:40 23:02
    07
    -AUG-03 17:27 06:30
    08
    -AUG-03 17:40 06:30
    09
    -AUG-03 17:40 06:04
    10
    -AUG-03 06:01 16:01
    11
    -AUG-03 06:36 16:01
    12
    -AUG-03 06:35 17:09

    DAY       T_IN  
    NEW
    --------- ----- -----
    13-AUG-03 06:36 17:00
    14
    -AUG-03 06:35 17:00
    15
    -AUG-03 06:26 16:01

    14 rows selected

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Aug 2003
    Posts
    25
    Hi The_Duck,


    Is CASE and WHEN exists in Oracle 8.0.4? I've got the same result...


    SQLWKS> SELECT attendance_date AS DAY,
    2> to_char(actual_time_in,'hh24:mi') t_in,
    3> to_char(actual_time_out,'hh24:mi') AS t_out
    4> FROM ATTENDANCE_TRANSACTIONS_W
    5> where id_number = '871083';
    DAY T_IN T_OUT
    -------------------- ----- -----
    01-AUG-03 06:33 18:04
    02-AUG-03 06:34 17:00
    04-AUG-03 06:32 16:00
    05-AUG-03 06:37 18:07
    06-AUG-03 14:40 23:02
    07-AUG-03 17:27
    08-AUG-03 17:40 06:30
    09-AUG-03 17:40 06:04
    10-AUG-03 06:01
    11-AUG-03 06:36 16:01
    12-AUG-03 06:35 17:09
    13-AUG-03 06:36 17:00
    14-AUG-03 06:35 17:00
    15-AUG-03 06:26 16:01
    14 rows selected.
    SQLWKS>
    SQLWKS> SELECT
    2> DAY,
    3> TO_CHAR(t_IN, 'HH24:MI') AS T_IN,
    4> CASE
    5> WHEN t_out IS NOT NULL THEN TO_CHAR(t_out, 'HH24:MI')
    6> WHEN t_OUT IS NULL THEN
    7> (SELECT TO_CHAR(actual_time_out, 'HH24:MI')
    8> FROM ATTENDANCE_TRANSACTIONS_W
    9> WHERE attendance_date =
    10> (SELECT MIN(attendance_date)
    11> FROM ATTENDANCE_TRANSACTIONS_W
    12> WHERE attendance_date > DAY))
    13> END AS NEW
    14> FROM (
    15> SELECT attendance_date AS DAY,
    16> actual_time_in t_in,
    17> actual_time_out AS t_out
    18> FROM ATTENDANCE_TRANSACTIONS _W
    19> );
    WHEN t_out IS NOT NULL THEN TO_CHAR(t_out, 'HH24:MI')
    *
    ORA-00923: FROM keyword not found where expected
    SQLWKS>
    Last edited by ynoel; 08-28-03 at 11:58.

  9. #9
    Join Date
    Jul 2003
    Posts
    2,296
    have you tried this in SQL*plus?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    CASE WHEN and selects within the select clause came with 8i I think. Inline views are OK though : )

    Alan

  11. #11
    Join Date
    Aug 2003
    Posts
    25
    Hi The_Duck,


    yes, i tried it in SQL*Plus... same result i've got... pls help me to solve (badly needed)...

    Connected to:
    Oracle8 Enterprise Edition Release 8.0.4.0.0 - Production
    With the Partitioning option
    PL/SQL Release 8.0.4.0.0 - Production

    SQL> SELECT attendance_date AS DAY,
    2 to_char(actual_time_in,'hh24:mi') t_in,
    3 to_char(actual_time_out,'hh24:mi') AS t_out
    4 FROM ATTENDANCE_TRANSACTIONS_W
    5 where id_number = '871083';

    DAY T_IN T_OUT
    --------- ----- -----
    01-AUG-03 06:33 18:04
    02-AUG-03 06:34 17:00
    04-AUG-03 06:32 16:00
    05-AUG-03 06:37 18:07
    06-AUG-03 14:40 23:02
    07-AUG-03 17:27
    08-AUG-03 17:40 06:30
    09-AUG-03 17:40 06:04
    10-AUG-03 06:01
    11-AUG-03 06:36 16:01
    12-AUG-03 06:35 17:09
    13-AUG-03 06:36 17:00
    14-AUG-03 06:35 17:00
    15-AUG-03 06:26 16:01

    14 rows selected.

    SQL>
    SQL> SELECT
    2 DAY,
    3 TO_CHAR(t_IN, 'HH24:MI') AS T_IN,
    4 CASE
    5 WHEN t_out IS NOT NULL THEN TO_CHAR(t_out, 'HH24:MI')
    6 WHEN t_OUT IS NULL THEN
    7 (SELECT TO_CHAR(actual_time_out, 'HH24:MI')
    8 FROM ATTENDANCE_TRANSACTIONS
    9 WHERE attendance_date =
    10 (SELECT MIN(attendance_date)
    11 FROM ATTENDANCE_TRANSACTIONS
    12 WHERE attendance_date > DAY))
    13 END AS NEW
    14 FROM (
    15 SELECT attendance_date AS DAY,
    16 actual_time_in t_in,
    17 actual_time_out AS t_out
    18 FROM ATTENDANCE_TRANSACTIONS
    19 );
    WHEN t_out IS NOT NULL THEN TO_CHAR(t_out, 'HH24:MI')
    *
    ERROR at line 5:
    ORA-00923: FROM keyword not found where expected

  12. #12
    Join Date
    Jul 2003
    Posts
    2,296
    I am sure my testing on 9i was a tad different than what he is doing on 8.0.4

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  13. #13
    Join Date
    Aug 2003
    Posts
    25
    yup, that's why i'm asking you if CASE and WHEN exists in Oracle 8.0.4.
    because it is new to me...

    how can u convert it ...? i mean your sql so that it will run in 8.0.4

  14. #14
    Join Date
    Jul 2003
    Posts
    2,296
    at this point I would just cut to the chase and write a procedure to do it.
    simple and effective.
    Plus, it is much easier to manipulate each variable however you want.

    create an empty temp table to store your data

    create table new_temp_table as
    select * from ATTENDANCE_TRANSACTIONS
    where 1 = 2;

    PHP Code:
    declare

    v_day date;
    v_new_out date;

    cursor c1 is
    select day
    t_int_out FROM (
    SELECT attendance_date AS DAY,
    actual_time_in as t_in,
    actual_time_out AS t_out
    FROM ATTENDANCE_TRANSACTIONS
    );

    cursor c2 is
    SELECT actual_time_out
        FROM ATTENDANCE_TRANSACTIONS
        WHERE attendance_date 
    =
        (
    SELECT MIN(attendance_date)
        
    FROM ATTENDANCE_TRANSACTIONS
        WHERE attendance_date
    v_DAY);


    begin 

    for v_1 in c1 loop

    v_day 
    := v_1.day;

    if 
    v1.t_OUT is null then
    open c2
    ;
    fetch c2 into v_new_out;

    insert into new_temp_table values (
    v1.day,
    v1.t_in,
    v_new_out);

    close c2;

    else

    insert into new_temp_table values (
    v1.day,
    v1.t_in,
    v1.t_out);

    end if;

    end loop;
    commit;
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  15. #15
    Join Date
    Aug 2003
    Posts
    25
    Hi The_Duck,

    I got it this way.....

    SQLWKS> select a.id_number, a.attendance_date, TO_CHAR(a.actual_time_in,'hh24:mi'),
    2> TO_CHAR(DECODE(SIGN((to_number(to_char(a.actual_ti me_in,'HH24MI'))/1500)-1),-1,
    3> a.actual_time_out,1,
    4> b.OUT, a.actual_time_out),'hh24:mi')"ACTUAL_TIME_OUT",
    5> a.balik_overtime_in, a.balik_overtime_out,
    6> a.with_logbox_validation, a.working_time_code
    7> from ATTENDANCE_TRANSACTIONS_W a,
    8> ( select id_number, attendance_date, actual_time_out "OUT"
    9> from ATTENDANCE_TRANSACTIONS_W)b
    10> where a.id_number = b.id_number
    11> AND a.id_number = '871083'
    12> and (( a.attendance_date = b.attendance_date - 1 and a.actual_time_in > a.actual_time_out)
    13> or ( a.attendance_date = b.attendance_date and a.actual_time_in < a.actual_time_out)
    14> or ( a.attendance_date = b.attendance_date + 1 and a.actual_time_in is not null and a.actual_time_out is null))
    15>
    ID_NUMBER ATTENDANCE_DATE TO_CH ACTUA BALIK_OVERTIME_IN BALIK_OVERTIME_OUT W WORKING_TI
    ---------- -------------------- ----- ----- -------------------- -------------------- - ----------
    871083 01-AUG-03 06:33 18:04 N 0000000000
    871083 02-AUG-03 06:34 17:00 N 0000000000
    871083 04-AUG-03 06:32 16:00 N 0000000000
    871083 05-AUG-03 06:37 18:07 N 0000000000
    871083 06-AUG-03 14:40 23:02 N 0000000000
    871083 07-AUG-03 17:27 23:02 N 0000000000
    871083 08-AUG-03 17:40 06:04 N 0000000000
    871083 09-AUG-03 17:40 06:01 N 0000000000
    871083 11-AUG-03 06:36 16:01 N 0000000000
    871083 12-AUG-03 06:35 17:09 N 0000000000
    871083 13-AUG-03 06:36 17:00 N 0000000000
    871083 14-AUG-03 06:35 17:00 N 0000000000
    871083 15-AUG-03 06:26 16:01 N 0000000000
    13 rows selected.


    Anyways, thank you so much for your replies... I will try your procedure and see which is faster...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •