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
LVL 1
Anthony MatovuBusiness Analyst, MTN UgandaAsked:
Who is Participating?
 
chaauConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.