To answer my own question. This appears to be doing what I want it to do:
SELECT distinct fgh.goalID, fgh.GoalStatusID, fgh.GoalStageOfChangeID as
FirstSOCID, fgh.GoalHistoryDate as FirstDate,
lgh.GoalStageOfChangeID as LSOCID, lgh.GoalHistoryDate as LastDate
FROM tblGoalHistory as fgh, tblGoalHistory as lgh
WHERE fgh.GoalID = 130 and lgh.GoalID = fgh.GoalID and
(fgh.GoalHistoryDate in (select top 1 GoalHistoryDate from
tblGoalHistory where GoalID = 130 order by GoalHistoryDate asc)) and
(lgh.GoalHistoryDate in (select top 1 GoalHistoryDate from
tblGoalHistory where GoalID = 130 order by GoalHistoryDate desc))
I think that if only one entry exists in "GoalHistory" that entry will be
returned for both "FirstEntry" and "LastEntry". For my purposes I don't
think it matters.
At 09:05 AM 2/27/2005, you wrote:
> I have a table, like this:
>
>Goal (GoalID, GoalText)
>
> It is associated with this table:
>
>GoalHistory (GoalHistoryID, GoalID, GoalStatus, GoalStageOfChange,
>GoalHistoryDate)
>
> Given the GoalID, how do I write a query that will return both the
>"oldest" and "newest" entry in GoalHistory. I would have no problem doing
>this in two queries, but have not had luck condensing it into a single query.
>
>
>
>--
>Jeffry Houser, Web Developer, Writer, Songwriter, Recording Engineer
>AIM: Reboog711 | Phone: 1-203-379-0773
>--
>My Books: <http://www.instantcoldfusion.com>
>My Recording Studio: <http://www.fcfstudios.com>
>--
>When did Reality Become TV
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2189
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
