Query for "as of Date"

  • I am trying to query a table that has several records for each employee. Everytime there is a change to an employee in the application, a new record is created with an effective date in the table.

    What I want to do is to query the table to find out what a particular filed was as of a specific date. For example, as of 02/27/2009, what was the value of the mfgcode field.

    The problem that I'm having is there are several records with the same date for a given employee.

    emplid date mfgcode

    1 12/01/2008 abc

    1 01/21/2009 abc

    1 02/16/2009 def

    1 02/16/2009 def (some other field was changed on this record)

    1 02/19/2009 abc

    2 12/01/2008 abc

    2 02/16/2009 def

    The question for this set is As of 02/27/2009, what was the mfgcode for all employees.

    I only want 1 record for each employee as of 02/27/2009. Any help would be greatly appreciated.

  • Stephanie Smith (3/3/2009)


    I am trying to query a table that has several records for each employee. Everytime there is a change to an employee in the application, a new record is created with an effective date in the table.

    What I want to do is to query the table to find out what a particular filed was as of a specific date. For example, as of 02/27/2009, what was the value of the mfgcode field.

    The problem that I'm having is there are several records with the same date for a given employee.

    emplid date mfgcode

    1 12/01/2008 abc

    1 01/21/2009 abc

    1 02/16/2009 def

    1 02/16/2009 def (some other field was changed on this record)

    1 02/19/2009 abc

    2 12/01/2008 abc

    2 02/16/2009 def

    The question for this set is As of 02/27/2009, what was the mfgcode for all employees.

    I only want 1 record for each employee as of 02/27/2009. Any help would be greatly appreciated.

    I think something like this will do the job. This is untested code:

    SELECT e*

    FROM Employee e

    WHERE (SELECT TOP 1 e2.emplid, e2.date

    FROM Employee e2

    WHERE e2.emplid = e.emplid

    AND e2.date < @AsOfDate

    ORDER BY e2.Date DESC)

    You can do better stuff with joins. Here are a couple of other samples of ideas that you might try. I've always found that TOP 1 works best, consistently for large data sets, than these others:

    SELECT v.*

    FROM dbo.Version v

    WHERE v.documentId = 433

    AND v.VersionId = ( SELECT MAX(v2.VersionId)

    FROM dbo.[Version] v2

    WHERE v2.DocumentId = v.DocumentId

    )

    --or

    SELECT x.*

    FROM ( SELECT v.*,

    ROW_NUMBER()OVER (ORDER BY v.VersionId DESC) AS RowNum

    FROM dbo.[Version] v

    WHERE v.documentid = 433

    ) AS x

    WHERE x.RowNum = 1

    You can modify these to do the same thing with dates instead of INT's. Order, etc. is the same.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Dang smiley faces... I wish they wouldn't show up in code windows but still show in the rest of the post if desired. If you want to get rid of them in a hurry, replace all right parenthesis with & # 0 4 1 ; without the spaces (the spaces just make it so you can see the code here and shouldn't be included in the replacements).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I always forget they're there when I post. Makes me crazy.

    :D:P;):w00t::cool::hehe:

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (3/4/2009)


    I always forget they're there when I post. Makes me crazy.

    :D:P;):w00t::cool::hehe:

    haahhahaahahah yea the code looks very nice with them ...hahahaa great job Grant! Little joke here :hehe::hehe::hehe:

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Thank you. I will try these out.

    Stephanie

  • Grant Fritchey (3/4/2009)


    I always forget they're there when I post. Makes me crazy.

    :D:P;):w00t::cool::hehe:

    Heh... I don't get it... remember when they fixed it so spaces would be preserved? They just did that with character substitution... dunno why they couldn't do the same for right parenthesis and have a "white space" before them.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply