Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005

    Unanswered: SubQueries - Most recent Rate from joined table

    In a project I am working on, I have several tables that contain values with "effective dates"

    For example a table with pay rates can have several pay rates for the same position, as the pay rate changes from time to time.

    I am trying to structure a query that returns all of the records from a timesheet table and the corresponding pay rate for the job code (the job code is the link between the timesheet table and the pay rates table)

    The problem I am having is that the pay rate may change say twice in a year, so I have to return all records subsequent to the change pay rate date with the most recent pay rate.

    I hope this is making sense, because I am getting a little tired of this issue at the moment.

    Here is the sub query I have come up with so far, unfortunately it does not work,. hahaha

    As you will see, I created a view of the pay rates table sorting the records in descending order by effective date. In a single view, this works great, but on a sub query it gets a little wonky

    ANY suggestions you could offer would be greatly appreciated.

    SELECT TOP 100 PERCENT ts.ShiftDate, ts.idJobCode, COUNT(ts.eeLink) AS Points
    FROM dbo.tbl_ProcTimesheet ts INNER JOIN
    dbo.GratBanqPointsByDate ON ts.idJobCode = dbo.GratBanqPointsByDate.idJobCode
    WHERE (ts.Sequence = 0) AND (ts.ShiftDate BETWEEN CONVERT(DATETIME, '2005-11-27 00:00:00', 102) AND CONVERT(DATETIME, '2005-12-07 00:00:00',
    GROUP BY ts.ShiftDate, ts.idJobCode
    HAVING (ts.ShiftDate >=
    (SELECT TOP 1 (EffectiveDate)
    FROM dbo.GratBanqPointsByDate
    WHERE idJobCode = ts.idJobCode))
    ORDER BY ts.ShiftDate, ts.idJobCode

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    any suggestions? sure

    first of all, ditch "top 100 percent" -- that always strikes me as silly

    second, you don't need to explicitly CONVERT datetime strings to datetime, but if you're gonna do it, pick the right style code (you want 120, not 102)

    your correlated subquery is the right idea, but it should be applied in an ON condition as part of the join, and it should be applied to the job code table

    furthermore, i think there should be a correlation of pay code date to timesheet shift date

    the use of "greater than or equal to the TOP 1 ORDER BY" should simply be "equal to the MAX"

    well, that was a lot of changes, so i'm not 100% sure i got everything right ...
    SELECT ts.ShiftDate
         , ts.idJobCode
         , COUNT(ts.eeLink) AS Points
      FROM dbo.tbl_ProcTimesheet ts 
      JOIN dbo.GratBanqPointsByDate as jc
        ON ts.idJobCode = jc.idJobCode
       AND jc.EffectiveDate =
           ( SELECT max(EffectiveDate)
               FROM dbo.GratBanqPointsByDate
              WHERE idJobCode = ts.idJobCode
                AND EffectiveDate <= ts.ShiftDate )
     WHERE ts.Sequence = 0 
       AND ts.ShiftDate 
           BETWEEN '2005-11-27' AND '2005-12-07'
        BY ts.ShiftDate
         , ts.idJobCode
        BY ts.ShiftDate
         , ts.idJobCode | @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