My goal:
I am trying to retrieve the "next" record based on the date from a given
record. That part is easy enough. However, part of the criteria is
that the "next" record must match as far as nullability.
In other words, if the archivedDate in my "known" record IS NULL, then I
want to look for the "next" record where archivedDate is also NULL.
Conversely, if archivedDate in my "known" record IS NOT NULL, then I
want to look for the "next" record where archivedDate is any date value.
The SQL below works perfectly, but I'm not convinced it is the best
solution. Would anyone have a better solution to this problem that they
would be willing to share?
SELECT TOP 1
a.alertId
FROM
Alerts a
INNER JOIN AlertParticipants ap
ON a.alertId = ap.alertId
WHERE
ap.colleagueId = '0000094'
AND
CASE
WHEN a.archivedDate IS NULL THEN 'Active'
ELSE 'Archived'
END = (SELECT
CASE
WHEN archivedDate IS NULL THEN 'Active'
ELSE 'Archived' END
FROM
Alerts
WHERE
alertId = 198)
AND
a.updatedDate < (SELECT
updatedDate
FROM
Alerts
WHERE
alertId = 198)
ORDER BY
updatedDate DESC
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314973
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
