March 24, 2008 at 4:54 pm
I'm trying to write a query to give me the latest EntryDate record based on the value of the AsOfDate column. The date in the AsOfDate column should drive which record is returned. In the example below, I'm looking to return the EntryDate of "2007-01-17 14:47:29.000" based on the AsOfDate column. The trick is that the query needs to be dynamic because I'll never know what the AsOfDate is until the query is ran.
Thanks,
Kyle
CREATE TABLE DATES
(
ASOFDATE DATETIME,
ENTRYDATE DATETIME
)
INSERT INTO DATES (ASOFDATE, ENTRYDATE)
VALUES ('2007-01-01', '2007-01-04 10:26:05.000')
INSERT INTO DATES (ASOFDATE, ENTRYDATE)
VALUES ('2007-01-01', '2007-01-16 13:50:56.000')
INSERT INTO DATES (ASOFDATE, ENTRYDATE)
VALUES ('2007-01-01', '2007-01-17 14:47:29.000')
INSERT INTO DATES (ASOFDATE, ENTRYDATE)
VALUES ('2007-01-01', '2007-02-07 09:06:14.000')
INSERT INTO DATES (ASOFDATE, ENTRYDATE)
VALUES ('2007-01-01', '2007-03-07 13:38:30.000')
INSERT INTO DATES (ASOFDATE, ENTRYDATE)
VALUES ('2007-01-01', '2007-05-14 08:07:08.000')
INSERT INTO DATES (ASOFDATE, ENTRYDATE)
VALUES ('2007-01-01', '2007-05-14 13:01:31.000')
March 24, 2008 at 5:09 pm
In what way is EntryDate "2007-01-17 14:47:29.000" the latest one for ASOFDATE? All of your example ASOFDATEs are 2007-01-01.
March 24, 2008 at 9:21 pm
Kyle, can you show what you'd want returned as far as other data or is it just the max date using some criteria?
Also, show what you've tried. This almost sounds like homework or a test question. We'd like to see that you've made and effort.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 25, 2008 at 7:29 am
Here is the example I've been working with. I'm attempting to use a correlated subquery to get the information but it's returning the entry date of "5-14-2007 13:01:31" where it should be returning the "01-17-2007 14:47:29" value. I'm not sure how to restrict the query and limit it to return the latest date in the EntryDate field constrained by the AsOfDate field.
Any suggestions?
SELECT *
FROM DATES D1
WHERE ENTRYDATE =
(
SELECT MAX(D2.ENTRYDATE)
FROM DATES D2
WHERE D1.ASOFDATE = D2.ASOFDATE
)
March 25, 2008 at 9:19 am
Okay, I got the solution I wanted. I wasn't filtering on the ASOFDATE to limit the return of the ENTRYDATE field. The query below now returns what I was looking for.
SELECT *
FROM DATES D1
WHERE ENTRYDATE =
(
SELECT MAX(D2.ENTRYDATE)
FROM DATES D2
WHERE DATEPART(yyyy, ASOFDATE) = DATEPART(yyyy, ENTRYDATE)
AND DATEPART(mm, ASOFDATE) = DATEPART(mm, ENTRYDATE)
)
March 26, 2008 at 9:11 am
Kyle,
Thanks for the update and solution.
Happy T-SQL'ing!
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply