ba_trainer
asked on
Select DISTINCT(cust_nbr), MAX(cmplt_dt)
Select DISTINCT(cust_nbr), MAX(cmplt_dt)
FROM sales_datebook, ui_results_db.emp1
where cust_nbr IS NOT NULL
AND cmplt_dt > '05/14/2006'
AND emp_nbr = emp1
group by cust_nbr, cmplt_dt
XXXXXXX 5/24/2006
XXXXXXX 6/29/2006
XXXXXXX 7/12/2006
XXXXXXX 8/25/2006
XXXXXXX 8/31/2006
XXXXXXX 10/2/2006
XXXXXXX 10/6/2006
XXXXXXX 10/17/2006
The above query results in the above list, which is the correct list.
However, my need is for only the LAST or LATEST date that the account was called, not all dates.
The DISTRICT() and MAX() seem to have no effect...+
Can someone help? Thanks!!!~bat
FROM sales_datebook, ui_results_db.emp1
where cust_nbr IS NOT NULL
AND cmplt_dt > '05/14/2006'
AND emp_nbr = emp1
group by cust_nbr, cmplt_dt
XXXXXXX 5/24/2006
XXXXXXX 6/29/2006
XXXXXXX 7/12/2006
XXXXXXX 8/25/2006
XXXXXXX 8/31/2006
XXXXXXX 10/2/2006
XXXXXXX 10/6/2006
XXXXXXX 10/17/2006
The above query results in the above list, which is the correct list.
However, my need is for only the LAST or LATEST date that the account was called, not all dates.
The DISTRICT() and MAX() seem to have no effect...+
Can someone help? Thanks!!!~bat
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT Cust_nbr, Max(cmplt_dt)cmplt_dt
FROM (
Select DISTINCT(cust_nbr), MAX(cmplt_dt) cmplt_dt
FROM sales_datebook, ui_results_db.emp1
where cust_nbr IS NOT NULL
AND cmplt_dt > '05/14/2006'
AND emp_nbr = emp1
group by cust_nbr, cmplt_dt
)T
FROM (
Select DISTINCT(cust_nbr), MAX(cmplt_dt) cmplt_dt
FROM sales_datebook, ui_results_db.emp1
where cust_nbr IS NOT NULL
AND cmplt_dt > '05/14/2006'
AND emp_nbr = emp1
group by cust_nbr, cmplt_dt
)T
FROM sales_datebook, ui_results_db.emp1
where cust_nbr IS NOT NULL
AND cmplt_dt > '05/14/2006'
AND emp_nbr = emp1
AND cmplt_dt = (Select Max(cmplt_dt) from
sales_datebook, ui_results_db.emp1
where cust_nbr IS NOT NULL
AND cmplt_dt > '05/14/2006'
AND emp_nbr = emp1)