September 4, 2010 at 6:48 am
I have two tables. Customer and Case. The 2 tables are related by CustomerID. Each table has a date column: [dateadded]. Dateadded is either the date the customer record was added or the case record was added.
I am trying to return records from the Case table where EITHER the Customer.dateadded is less than 90 days from getdate() OR (and here's the difficult part):
Records in the Case table from a Customer who has not submitted a case in more than 90 days.
So, in english: Show me cases where the customer has been a customer for less than 90 days OR cases from a customer who haven't submitted a case in more than 90 days. (Cases for new customers or newly active customers)
In the result set, I don't want the old case to show up. So if a customer's current case# is 12345 and the most recent case for them is #12344 (dated 4/4/2010). Then only the 12345 (most recent) case would show up.
thank you so much for any help/insight/direction you can offer.
September 4, 2010 at 8:54 am
IN order to receive tested help please post the tables definition, some sample data and the required results.
Refer to the first link in my signature block for simple instructions on how to do so and then I am sure some one will be more than willingly to assist you with a tested solution.
September 4, 2010 at 11:06 am
Since your description is not very clear (maybe even contradictory when looking at #12345 and #12344 and the mixed usage of "most recent") I'm only guessing here:
Either Row_Number or a CASE statement might help here.
But without any sample data in a format Ron asked for already it's almost impossible to answer.
So, please take the time and help us help you.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply