March 3, 2009 at 7:09 am
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.
March 3, 2009 at 8:19 am
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
March 3, 2009 at 7:46 pm
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
Change is inevitable... Change for the better is not.
March 4, 2009 at 5:57 am
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
March 4, 2009 at 6:52 am
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:
March 4, 2009 at 12:04 pm
Thank you. I will try these out.
Stephanie
March 4, 2009 at 4:23 pm
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy