most recent row using 2 dates

• ixadams

  SSC Journeyman

  Points: 79

  More actions

  April 6, 2011 at 6:19 pm

  #389224

  Using SQL 2000

  I have an orders table and need to most recent order for a customer based on the Update Date first and if null then the Create Date (never Null)

  Then I need to join that row to the customer table and display the customer information with the most recent order all in a single row.

  I can do it with a single date but can't figure out how to do it when there are 2 date fields to evaluate....

  Help is much appreciated.

  Thanks in advance.

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  April 7, 2011 at 2:41 pm

  #1308832

  How about something like:

  SELECT Top 1 OrderNumber

  FROM Orders

  ORDER BY CASE WHEN UpdateDate IS NULL THEN CreateDate ELSE UpdateDate END DESC

  or

  SELECT Top 1 OrderNumber

  FROM Orders

  ORDER BY ISNULL(UpdateDate, CreateDate) DESC

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• ixadams

  SSC Journeyman

  Points: 79

  More actions

  April 7, 2011 at 3:26 pm

  #1308858

  Thanks!

  Heres what I ended up with before I say your reply.

  , IsNull(dbo.drug_eligibility.UpdateDate, CreateDate) AS SortDate

  its working. Thnaks again!