Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 83
  • Last Modified:

get the maximum value on a condition

Dear All

I want to write a sql statement giving me the most recent date when a customer paid for different products by product

Result should be like this

Customer ='James", last_date_paid_for_product1='20160101", last_date_paid_for_product2='20160201", last_date_paid_for_product2
3='20160201",

Data is in this format
Customer            Date_of_pay         Product1          Product2           Product3
A                           20161010                200                                                300
A                           20161011                200                                                
A                           20161012                200                                                
A                           20161013                                           400                     300
A                           20161014                                                                      300

Result of the sql should be
Customer ='A", last_date_paid_for_product1='20161012", last_date_paid_for_product2='20161013", last_date_paid_for_product2
3='20161014",


Thank you

Anthony
0
Anthony Matovu
Asked:
Anthony Matovu
1 Solution
 
chaauCommented:
I would use this simple query:
SELECT
   Customer            
  ,MAX(CASE WHEN product1 IS NOT NULL THEN Date_of_pay END) as last_date_paid_for_product1
  ,MAX(CASE WHEN product2 IS NOT NULL THEN Date_of_pay END) as last_date_paid_for_product2
  ,MAX(CASE WHEN product3 IS NOT NULL THEN Date_of_pay END) as last_date_paid_for_product3
FROM products
GROUP BY Customer

Open in new window

0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
did you make a mistake? did you mean to write this:
Customer ='A', last_date_paid_for_product1='20161012', last_date_paid_for_product2='20161013', last_date_paid_for_product3='20161014'

What is the data type for Date_of_pay ?
0
 
Gustav BrockCIOCommented:
You should rephrase this completely. You state two different resultsets, and have tagged the question wildly:

MS SQL Server MS Access .NET Programming

so no one knows what your are doing.

/gustav
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now