Query for "as of Date"

Question

Query for "as of Date"

Stephanie Portwood

SSC Enthusiast

Points: 176
More actions
March 3, 2009 at 7:09 am

#128116

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.

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

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

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004600 More actions · Answer 2

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)

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 3

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

Dugi SSCoach Points: 17998 More actions · Answer 4

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]

Stephanie Portwood SSC Enthusiast Points: 176 More actions · Answer 5

Stephanie Portwood

SSC Enthusiast

Points: 176

March 4, 2009 at 12:04 pm

#953487

Thank you. I will try these out.

Stephanie

Jeff Moden SSC Guru Points: 1004600 More actions · Answer 6

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)

Query for "as of Date"

Cookies on SQLServerCentral