Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: complex SELECT Statement

    TABLE : USER
    USERID
    1
    2

    TABLE : TIME
    TIMEID|USERID|RT|OT|DOT|DATE
    1|1|8|2|1|2004-02-01
    2|1|8|2|0|2004-02-02

    3|2|8|0|0|2004-02-01
    4|2|8|2|2|2004-02-02

    RT : Regular Time
    OT : Over-Time
    DOT : Double Over-Time

    I need to write a query to display the results in this way

    USERID|DATE|TIME
    1|2004-02-01|8
    1|2004-02-01|2
    1|2004-02-01|1

    1|2004-02-02|8
    1|2004-02-02|2

    2|2004-02-01|8
    2|2004-02-02|8
    2|2004-02-02|2
    2|2004-02-02|2

    basically, the time entries for each user each day , seperate rows for RT, OT, DOT if they are not equal to 0

    ive been breaking my head on this for quite a while. any help is appreciated.

    thanks

  2. #2
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: complex SELECT Statement

    can you detail the TIME column in your result

    what do you want in this column ?

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: complex SELECT Statement

    never mind my last post

    just understood...


    I'm checking for the query now

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: complex SELECT Statement

    this should do

    sort the query in the wanted order




    select date, users.userid, time from users
    inner join (
    select date, userid, rt as time from time where rt <>'0'
    union
    select date, userid, ot from time where ot <>'0'
    union
    select date, userid, dot from time where dot <>'0') detail_time
    on detail_time.userid=users.userid
    order by date, users.userid asc

  5. #5
    Join Date
    Feb 2004
    Posts
    5
    thanks karolyn,
    u saved my day !!

    reg
    db_montreal

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    and it's only starting in montreal

Posting Permissions

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