Fancy SQL Select help - SQL Server Forums

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)

Fancy SQL Select help

Author

Topic

cmk8895
Starting Member

5 Posts

Posted - 2006-11-06 : 13:32:18

Hi all.

I am trying to clean up a set of SQL queries and can't figure out how to do something....

Two tables exist, one with order# and customer name, the other is a tracking table of order# and activity on the account. So the second table can have the same order number several times with different activity.

Now, let's say that I'm interested in finding the first time that each customer was called. Keep in mind that he may have been called many times and I want to know about just the first.

So I want something like this (which is NOT valid SQL code!!!):

SELECT
cust.order_no,
cust.cust_name,
min(act.activity_date) where descrip like '%call%' AS first_call
FROM
customer cust LEFT JOIN activity act
ON cust.cust_no = act.cust_no

Output should be a table with one line per order_no and cust_name and first_call.

That's the SQLesq idea of what I need to do and I can't for the life of me figure out how to do it (I won't go over the things I've tried unless someone really wants to know). Currently it is set up to use a temp table to find the min activity_date for each order_no, then use that table joined with the customer data in a second SELECT. I'd like to make it savvy and have it all done at once.

I'm 99% sure that there is a simple and elegant solution to this, but I'm totally confused. Could anybody give me an idea on where to start? In real life there are going to be a LOT more 'calculated' fields (min_of_whatever), but I wanted to keep it relatively simple for now.

cmk (intermediate with basic sql, newbie to sql optimization)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 13:45:43

[code]SELECT cust.order_no,
cust.cust_name,
min(case when descrip like '%call%' then act.activity_date end) AS first_call
FROM customer cust
LEFT JOIN activity act ON act.cust_no = cust.cust_no
GROUP BY cust.order_no,
cust.cust_name[/code]
Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-06 : 13:48:06

"Currently it is set up to use a temp table to find the min activity_date for each order_no, then use that table joined with the customer data in a second SELECT"

I reckon that's a good plan/ You could use a derived table, but IME the Temp Table will be faster, particularly if you Index it appropriately for the JOIN

Kristen

cmk8895
Starting Member

5 Posts

Posted - 2006-11-06 : 14:13:02

Thanks guys.

Peso: I had the min inside the case statement which wasn't working for me, your way works. Thanks.

Kristen: You are quite correct in your speed assumptions. I had thought that integrating it would speed things up, but it slowed it down A LOT.

Note to self: temp tables aren't a bad thing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources