Would this require a SELECT in a SELECT? 1

[LMGroup]

I'm using SQL Server 2005 and trying to get data from 3 tables. I can get the (simple) information from the first two tables (shown in the code) but I'm not sure how to get the third part of the data into the query from the third table.

SELECT Customer.CustomerN AS '#', Customer.CustomerName AS 'Customer', 
CASE	WHEN Salesperson.Salesperson LIKE 'T %' OR Salesperson.Salesperson LIKE 'O %' THEN SUBSTRING (Salesperson.Salesperson, 3, LEN (Salesperson.Salesperson))
		WHEN Salesperson.Salesperson LIKE 'Tie %' OR Salesperson.Salesperson LIKE 'Tbr %' OR Salesperson.Salesperson LIKE 'Obr %' THEN SUBSTRING (Salesperson.Salesperson, 5, LEN (Salesperson.Salesperson))
		ELSE Salesperson.Salesperson
END AS 'Sales',
CONVERT(varchar,Customer.CustStartDate,101) AS 'Start'

FROM   Customer Customer LEFT OUTER JOIN Salesperson Salesperson ON Customer.SalespersonN=Salesperson.SalespersonN

ORDER BY Customer.CustomerN

This returns the data as required (Customer #, Customer Name, Sales Person, Start Date)

I also need it to display the date of the customer's most recent order (if there is one) and how many days it's been since their last order. This should display "INACTIVE" if they have never placed an order. This data is in a 3rd table.

This was all working fine in a crystal report with a subreport, but I'm not sure how to convert it all into a SQL Server query.

Thanks for your help!

 

[jbenson001]

Use another Left Outer Join to the 3rd table. then you can use a CASE to display "Inactive" if the value is NULL.

 

[LMGroup]

I didn't think I could do all that within the SELECT statement. That's great!

Here's what I tried. The second CASE is causing an error: "Column 'Customer.CustomerN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." If I comment out the second case, everything is fine again.

SELECT Customer.CustomerN AS '#', Customer.CustomerName AS 'Customer', 
CASE	WHEN Salesperson.Salesperson LIKE 'T %' OR Salesperson.Salesperson LIKE 'O %' THEN SUBSTRING (Salesperson.Salesperson, 3, LEN (Salesperson.Salesperson))
		WHEN Salesperson.Salesperson LIKE 'Tie %' OR Salesperson.Salesperson LIKE 'Tbr %' OR Salesperson.Salesperson LIKE 'Obr %' THEN SUBSTRING (Salesperson.Salesperson, 5, LEN (Salesperson.Salesperson))
		ELSE Salesperson.Salesperson
END AS 'Sales',
CONVERT(varchar,Customer.CustStartDate,101) AS 'Start',
CASE	WHEN OpenJob.BookedDate IS NULL THEN 'INACTIVE'
		ELSE CONVERT(varchar,MAX(OpenJob.BookedDate),101)
END AS 'Last Order'

FROM   Customer Customer 
LEFT OUTER JOIN Salesperson Salesperson ON Customer.SalespersonN=Salesperson.SalespersonN
LEFT OUTER JOIN OpenJob OpenJob ON Customer.CustomerN = OpenJob.CustomerN

ORDER BY Customer.CustomerN

 

[jbenson001]

I am not sure why you would get that error. Are you showing the whole query, do you have a group by anywhere?

 

[LMGroup]

Further testing shows me that the MAX statement is the culprit. I really dislike that particular error message, perhaps because I don't understand how it is triggered or how to prevent it. In the past, I've ended up throwing all the fields into a group and thinking that this is the most ridiculous way to fix the problem. That can't possibly be the correct solution.

Obviously the SUBSTRING and CONVERT commands don't trigger that error, but MAX does. Should I be using something else? I only want to most recent order date.

 

[jbenson001]

This is how I would do it so that the SQL doesn't get unreadable and maintainable. Remember, there is no reason you have to put all your logic into one SQL statment.
So, I would take your original query, and change it to a SELECT INTO.

So something like SELECT col1, col2... etc
INTO #customers

Then the second SELECT would do the LEFT OUTER JOIN to the OpenJob table. Then in there you can do your case logic.
Doing it this way will keep it more readable and your group by will not be affected.

You are correct, you don't just want to start throwing columns into the group by, although this will eventually get rid of the error, it will affect your results.

 

[LMGroup]

Thanks for all your help. This is great!

 

[jbenson001]

Glad to help and hope this helped you out.