fliter out the records with max date field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

fliter out the records with max date field

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-08-19 : 10:22:40

Ok, I admit, I forgot hwo to do this, and my time is running out, so I will just ask.
I need to get a list of customer address who have placed an order in a given time frame. Plain enough request. Everything is fine untill they further ask to throw in the contact name for the last order on file.
The name is on order_header, which joins the order on orderID. But I got more than I want. All the contact names from the same account shows up, not just the latest one. I know I have to do a max on OrderDate from order table, but just could not get it to work with all the joins tangle together.

I'd appreciate your help!

Here is one of my work in progress select.

SELECT distinct ac.account_number, ac.account_name,
ah.head_contact_name, jt.ShipDt,
ad.address_line1, ad.address_line2,
ad.address_line3, ad.city, ad.state,
ad.postal_code
FROM ((stsJobTracking jt JOIN (( account ac JOIN address ad
ON ac.account_number = ad.account_number) JOIN address_use au
ON ( ac.account_number = au.account_number) AND
( ad.address_number = au.address_number))
ON jt.AccountNumber = ac.account_number) join account_status st on ac.account_number=st.CUST_ID
) join dbo.order_header ah on jt.CO_Number=ah.head_order_nbr
WHERE au.address_type='m' and st.AR_CUST<>'C'
AND jt.ShipDt>'1/1/2003'

Kristen
Test

22859 Posts

Posted - 2005-08-19 : 10:54:14

How about:

Change

ah.head_contact_name
to
MAX(ah.head_contact_name) as head_contact_name

and add

GROUP BY ac.account_number, ac.account_name,
jt.ShipDt,
ad.address_line1, ad.address_line2,
ad.address_line3, ad.city, ad.state,
ad.postal_code

and take out the DISTINCT

Kristen

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-08-19 : 11:09:55

Thanks Kristen!
You mean Max on ShipDt? Max(contact_name) will get me the longest name.
With every field listed in the group by, I still get multiple contact names with different ship dates associated with the same address, such as this one:
1000 AAA ADVERTISING MIKE 2004-03-23 00:00:00.000 333 MICHIGAN #1003 Cincinnati OH
1000 AAA ADVERTISING GEORGE 2003-08-21 00:00:00.000 333 MICHIGAN #10033 Cincinnati OH

Where did I miss?

Kristen
Test

22859 Posts

Posted - 2005-08-19 : 13:33:48

Ah ... well if you are only interested in Customers who have at least one shipment after '1/1/2003' (i.e. you do not need to see the actualy shipment dates) then remove the JOIN to the Shipment table and use

WHERE ... EXISTS (SELECT * FROM stsJobTracking jt WHERE jt.AccountNumber = ac.account_number AND jt.ShipDt>'1/1/2003')

Dunno if I've got the right key fields on that table, but I expect you get the idea!

Alternatively, if you want to see one shipment date (regardless of hoe many shipments there where) then use MIN(jt.ShipDt) or MAX(jt.ShipDt) and remove that column from the GROUP BY that I proposed

Kristen

Kristen
Test

22859 Posts

Posted - 2005-08-19 : 13:34:39

PS You could use the AVERAGE jt.ShipDt if you like

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources