Thread: PL SQL help

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    2
    Rep Power
    0

    PL SQL help


    Anyone that could confirm if this is possible would be great,

    I have a table with the following columns

    EMPLOYEEID NUMBER(12,0)
    PUNCHDTM DATE
    TIMEZONEID NUMBER(12,0)

    I want to return any results where any employee id that has 2 different timezoneid's on the same date. I would actually like, if its possibe, to select these etnries to display on one row per employee per day. So for example

    EMPLOYEEID - PUNCHDTM - TIMEZONEID - PUNCHDTM - TIMEZONEID
    12345 - 6/20/2012 5:00 am - 123 - 6/20/2012 10:00am - 456

    To me who is newer with SQL this sounds like i would be 'joining' the table to itself so i've searched for that but not found what i need.

    Thank you in advance for any help
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    This should get you started:
    Code:
    select e1.employeeid, 
           e1.punchdtm,
           e1.timezoneid,
           e2.punchdtm,
           e2.timezoneid
    from a_table e1
      join a_table e2 
           on e1.employeeid = e2.employeeid 
          and e1.timezoneid <> e2.timezoneid
    Note that this won't work correctly if an employee has more than 2 timezones.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    2
    Rep Power
    0
    well that worked with a slight tweak so i thank you but idiot here found a view that already stores the punches linked together with appropriate timezone.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Originally Posted by sandsawks
    well that worked with a slight tweak so i thank you but idiot here found a view that already stores the punches linked together with appropriate timezone.
    But you learned something!

IMN logo majestic logo threadwatch logo seochat tools logo