SQL challenge!

[schnee]

Hi,

I have this query:
SELECT
    prospectId,
    actions.dateAction
  FROM  (prospects
   LEFT JOIN actions ON prospects.prospectId=actions.prospect)

I would like to add a column that would be the date of the "most
imminent" action,
ie. the one with the sooner -- but not passed -- date in dateAction, if
there is at least one action, null otherwise.

Any one has an idea?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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/sql/message.cfm/messageid:3140
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Steve Nugent]

I think the answer you're looking for is to use an in-lined table query (I think the technical term is correlated sub-query), like so:

SELECT prospectId,
       actions.dateAction
 FROM  prospects
     LEFT JOIN (SELECT prospect, MAX(dateAction) as dateAction
          FROM actions
         WHERE dateAction <= Now()) as actions ON (prospects.prospectId=actions.prospect)

You can substitute now for whatever 'get the date for today' function your server supports.
       

>Hi,
>
>I have this query:
>SELECT
>    prospectId,
>    actions.dateAction
>  FROM  (prospects
>   LEFT JOIN actions ON prospects.prospectId=actions.prospect)
>
>I would like to add a column that would be the date of the "most
>imminent" action,
>ie. the one with the sooner -- but not passed -- date in dateAction, if
>there is at least one action, null otherwise.
>
>Any one has an idea?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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/sql/message.cfm/messageid:3146
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Steve Nugent]

That last query's not right (aggregate with no group by, what was I thinking?), it should be:

SELECT prospectId,
       actions.dateAction
 FROM  prospects
       LEFT JOIN (SELECT prospect, MAX(dateAction) as dateAction
                  FROM actions
                  WHERE dateAction <= Now()
                  GROUP BY prospect) as actions ON (prospects.prospectId=actions.
> prospect)

If that's still not giving the correct date (don't have time to test it, sorry), you'll have to do it with a having max(dateaction) <= now() inside the parens of the subquery.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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/sql/message.cfm/messageid:3147
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6