Try this:
Code:
Select Distinct
I.Customer,
I.InvoiceToName,
S.LastSoldDate
From tblarInvoice I
Inner Join (
Select Customer, Max(dateordered) As LastSoldDate
From tblsoSO
Group By Customer
Having Count(NullIf(status, 'closed')) = 0
And Max(dateordered) < '06/01/2013'
) S
On I.Customer = S.Customer
The reason it returned multiple rows is because some customers have multiple rows in the invoice table. It would probably be better to join to a customer table. Specifically, you probably have a table in your database that has 1 row per customer. Joining to this table would allow you to remove the distinct and would therefore execute faster while still returning the same results.
Now for the explanation...
The key to this query is having a clear idea of what you want. You kept talking about not returning rows with open orders and throwing around the 6 month thing. After thinking about this for a couple minutes, it occurred to me that you only wanted customers where all of the statuses are closed.
The part of the query that accommodates the requirement of "all rows are closed" is this:
Having Count(NullIf(status, 'closed')) = 0
When understanding expressions like this, it's best to start at the inside and work your way out. [tt]Having Count([!]NullIf(status, 'closed')[/!]) = 0[/tt]
The NullIf function requires 2 parameters. If the first parameter is the same as the second parameter NullIf will return NULL. If the parameters are different, NullIf returns the value of the first parameter.
For example:
Code:
Select NullIf('open','closed')
Select NullIf('closed','closed')
The first query will return 'open'. The 2nd query will return 'closed'.
Next, let's understand the Count function. [tt]Having [!]Count([/!]NullIf(status, 'closed')[!])[/!] = 0[/tt]
The Count function is considered an aggregate function. By this, I mean that it works on a group of rows. The group of rows is defined in the group by clause. Other aggregate functions would be sum, avg, min, max, etc... The count function takes 1 parameter and always returns an integer. The important thing to understand here is that the count function only considered non-null values. If you have 10 rows in the group and 4 of them are NULL, count will return 6. When you combine the count function and nullif function like I did here you will get a count of rows that are not 'closed'. Since we only want customers where every status is closed, we use "having (count of NOT closed) = 0".
Now, let's look at this:
Code:
Select Customer
From tblsoSO
Group By Customer
Having Count(NullIf(status, 'closed')) = 0
If you run the query above, you will get a distinct list of customers where every status is closed. This will be distinct because there is a Group By on Customer and the having clause prevents those customers with open orders.
The next part of your requirements is that the last order is greater than 6 months ago. Since we are already grouping on customer, we can simply add another condition for the date requirement.
Code:
Select Customer
From tblsoSO
Group By Customer
Having Count(NullIf(status, 'closed')) = 0
[!]And Max(dateordered) < '06/01/2013'[/!]
If you run the query above, you will get the customers that have every order status closed and the most recent order date occurred before 6/1/2013. Unfortunately, this query does not show that date, but we can easily add it, like this:
Code:
Select Customer,
Max(dateordered) As LastSoldDate
From tblsoSO
Group By Customer
Having Count(NullIf(status, 'closed')) = 0
And Max(dateordered) < '06/01/2013'
Now if you run the query, you will see the customer and the LastDateSold. It will still only be customers where every status is closed and last order date occurred before 6/1/2013.
Your next requirement is to show the customer name which is stored in another table. To do this, we need to join this query to another table. The technique I used is called a "derived table". Basically, you can write a query and then use that query in a larger one while treating the inner query as though it were an actual table.
Code:
Select Distinct
I.Customer,
I.InvoiceToName,
[!]S.[/!]LastSoldDate
From tblarInvoice I
Inner Join [!]([/!]
[blue]Select Customer, Max(dateordered) As LastSoldDate
From tblsoSO
Group By Customer
Having Count(NullIf(status, 'closed')) = 0
And Max(dateordered) < '06/01/2013'[/blue]
[!]) S [/!]
On I.Customer = [!]S[/!].Customer
Take a look at the code above. It's exactly the same as the first code block in this reply (with different things colored in a certain way). The blue part is just the previous query that we wrote. The parts in red show you how to use the query as a derived table. Notice the open and close parenthesis around the query. This basically tells SQL to evaluate the query first. The S after the close parenthesis is the derived table alias. When referring to columns within the derived table, you must always use the alias (so sql doesn't get confused about what you are referring to). Notice how the S alias is used in the ON clause and also in the SELECT clause.
PS: When posting code, it's easier for everyone to read if you use TGML markup. To post a code block, do this:
[ignore]
[/ignore]
-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
