Results 1 to 3 of 3

Thread: Query Logic

  1. #1
    Join Date
    Mar 2004
    Location
    chicago
    Posts
    26

    Cool Unanswered: Query Logic

    I have a table which tracks only changes to an employee's salary/promotion/location. If there is no change in the field, then the field is null in the record. Here is an example table:

    NAME~TITLE~SALARY~PROMOTION~DATE~LOCATION
    Sanchez ~ AccII ~ 45,000 ~ checked ~ 07/01/04 ~ null
    Sanchez ~ null ~ 40,000 ~ null ~ 07/01/03 ~ null
    Sanchez ~ AccI ~ 35,000 ~ null ~ 07/01/02 ~ NYC
    Andres ~ AccIII ~ 52,000 ~ checked ~ 03/01/04 ~ null
    Andres ~ AccII ~ 45,000 ~ checked ~ 03/01/03 ~ null
    Andres ~ null ~ 41,000 ~ null ~ 03/01/02 ~ null
    Andres ~ null ~ 38,000 ~ null ~ 03/01/01 ~ null
    Andres ~ AccI ~ 34,000 ~ null ~ 03/01/00 ~ CHICAGO
    Jones ~ null ~ 40,000 ~ null ~ 09/01/04 ~ null
    Jones ~ null ~ null ~ null ~ 12/01/03 ~ NYC
    Jones ~ AccI ~ 35,000 ~ null ~ 09/01/03 ~ CHICAGO

    I need a query/report which shows the salary and title change info. when an employee is promoted:

    NAME ~ OldTitle ~ OldSalary ~ Date ~ NewTitle ~ NewSalary
    Sanchez ~ AccI ~ 40,000 ~ 07/01/04 ~ AccII ~ 45,000
    Andres ~ AccI ~ 41,000 ~ 03/01/03 ~ AccII ~ 45,000
    Andres ~ AccII ~ 45,000 ~ 03/01/04 ~ AccI ~ 52,000

    I tried to filter on the Promotion field, and I get the 3 records of when they got promoted. However Ii do not get their old salary, which are referenced in records where the promotions box was unchecked.

    I can't seem to pull it this way. I think it has something to do with the null entries in the table. Any suggestions?

    Thanks, KT

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Stuff like this is hard to do in a query only, asspecially since you are pulling record 3 and want data from both record 2 and record 1.

    You will have to create 2 functions both working with recordsets to retrieve the previous value you desire. It aint beautifull, but it will work.

    Regardz

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select pro.name        as Name
         , ttk.title       as OldTitle
         , sal.salary      as OldSalary
         , pro.date        as Date
         , pro.title       as NewTitle
         , pro.salary      as NewSalary
      from employees as pro
    inner
      join employees as sal
        on pro.name 
         = sal.name
       and sal.salary is not null
       and sal.date          
         = ( select max(date)
               from employees
              where salary is not null
                and date < pro.date )
    inner
      join employees as ttl
        on pro.name 
         = ttl.name
       and ttl.title is not null
       and ttl.date          
         = ( select max(date)
               from employees
              where title is not null
                and date < pro.date )           
     where pro.promotion
         = 'checked'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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