Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query Help with Max function

Status
Not open for further replies.

CFrockchelle

Programmer
Dec 17, 2002
11
US
Ok Heres the situtation. I need to show a list of customers who have a invoice date that is 2 years or older or they have a last ship date that is 2 years or older. I'm using the max function on the last date ship since there are several shipping dates and I need to get the lastest ship date to see if it is older than 2 years. When I use the max function of course it elimates the null values for the last date ship. The problem with this is that the customer may have a invoice date that is 2 years old or older but have a null last shipped date so therefore that customer is not displayed but I need them to display.Is there another way to write the query to show the null shipping dates?
Here is a generic version of what I have:

Select d.billID,d.cust_id,d.cust_name,d.lastInvoice, d.purged,max(o.dateshipped)
From CustomerData d left join OrderInf o
on d.cust_id = o.cust_id
Where datediff(yyyy,lst_ivcdat,getdate()) > 2
or purged = 'y'
group by d.billID,d.cust_id,d.cust_name,d.lastInvoice,
d.purged
having datediff(yyyy,max(dt_lst_shp),getdate()) > 2


Thanks in Advance for any help!!!






 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top