Get Customers That Have Not Bought In 6 Months 1

[dwg23]

I have been given the task of finding out which of our customers have not bought from us in 6 months and have no outstanding sales orders.

The information is contained in two tables: customers and sales

The customer number is common to both tables and I need to pull the customer number and name the customer table has a invoice date so i could look for that and the sales table has a status column that includes closed and open status's and a date printed column.

I am sure this is all I would need to put it together but am stumped as to how.

I know I would write something like.

Select Distinct I.Customer,
I.InvoiceToName
From
tblarInvoice I
Inner Join tblsoSO S
On I.CustomerShipTo = S.CustomerShipTo

Where max(I.dateprinted) <= '06/01/2013'
or max(S.dateordered) <= '06/01/2013'
and S.status = 'closed'

I am using S.status for a customer that placed a blanket order that could go back as much as a year. If S.status is open then I do not want to include the customer in the result set.
the above would not work as I think I would get every record back to the beginning of time and I just need those that have not ordered in the last 6 months. not to mention the problem with an aggregate in the Where statement. Which I know does not work but do not know how to make work even if it was what I wanted.

So I am stumped as to what to use to get the no orders for 6 months back.

 

[bborissov]

DECLARE @sixMonthsBefore datetime 
SET @sixMonthsBefore = DATEADD(mm, -6, DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) 
SET @sixMonthsBefore = DATEADD(dd, 1, @sixMonthsBefore) -- Just in case you have time portion

Select tblarInvoice.Customer,
       tblarInvoice.InvoiceToName
From tblarInvoice
    Inner Join tblsoSO On tblarInvoice.CustomerShipTo = tblsoSO.CustomerShipTo
GROUP BY tblarInvoice.Customer, tblarInvoice.InvoiceToName

HAVING MAX(tblarInvoice.dateprinted) < @sixMonthsBefore -- Not smaller or equal, just smaller :-)
OR     (MAX(tblsoSO.dateordered)     < @sixMonthsBefore -- brackets are very important when you have ANDs and ORs in one condition.
        AND tblsoSO.status = 'closed')

Borislav Borissov
VFP9 SP2, SQL Server

 

[dwg23]

bborissov,
Thank you! I did have to add tblsoso.status to the group by because it error on that.
The query does give me customers that have not bought in 6 months, but it also gives me customers that have open orders that are 6 months old.
I did not want to include customers that had out standing orders. one more thing as I am new to SQL Statments could you tell me what do the brackets do as far as processing the statement?

Thanks,
DWG23

 

[gmmastros]

Are referring to this?

-- brackets are very important when you have ANDs and ORs in one condition.

The brackets are important to control the order of operations.

Let's use this as an example.

Imagine you had a table like this:

EyeColor   ShoeSize
--------   --------
Blue       9.5
Blue       10
Brown      12
Brown      9.5

Now suppose you wanted all rows where EyeColor is blue and ShoeSize is 10 or 12. You would expect to get just 1 row (Blue, 10).

If you write:

Select * 
From   @Temp
Where  EyeColor = 'blue' 
       and shoesize = 10 
       or shoesize = 12

You will actually get 2 rows:
blue, 10
Brown, 12

If you write:

Select * 
From   @Temp
Where  EyeColor = 'blue' 
       and (
           shoesize = 10 
           or shoesize = 12
           )

Now you will only get the 1 row you expect.

The following code creates a table variable so that we can mock up some data. You can copy/paste this to a query window and execute it. It will run, but it will also not affect anything within your database.

Declare @Temp Table(EyeColor VarChar(10), ShoeSize Decimal(3,1))

Insert Into @Temp Values('blue',9.5)
Insert Into @Temp Values('blue',10)
Insert Into @Temp Values('brown',12)
Insert Into @Temp Values('brown',9.5)

Select * 
From   @Temp
Where  EyeColor = 'blue' 
       and shoesize = 10 
	   or shoesize = 12

Select * 
From   @Temp
Where  EyeColor = 'blue' 
       and (
           shoesize = 10 
           or shoesize = 12
           )

As you can see, the brackets (I call them parenthesis) allow you to control which rows are returned. It's easy to make this kind of mistake so I have a "rule" that I always follow. When mixing "and" with "or", always use parenthesis.

-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

 

[dwg23]

Ah, Ha!
Excellent example!

That also helps explain the rather strange results that I have gotten in some other query's I have written.
Is there a book you would recommend to learn all of this?

 

[dwg23]

UpDate,
The above query does return customers that have bought in the last 6 months in addition to customers that have open orders.
I have tried to play around with it but no luck.
I am back to square one.

I will try to re-state the criteria in case I messed up and was not clear.

I am trying to get a list of all of the customers that have not ordered in the last 6 months, or have any open orders.
the information is contained in a table called tblarinvoice which has customer number, customer name and invoice date. their is another table called tblsoso, the common column between the two is the customer number. tblsoso has a column called status and another called dated ordered. the status is either open or closed I wanted to look at the status column and if their was a customer that had an date ordered of longer than 6 months but the status was open it would not be returned in the result set.
I am after the customer number, customer name and last date that they bought according to the tblarinvoice table.

Now my boss has said that he would take just the customer number, customer name and last sale date if that was the best I could do.

I wrote this query but it returns multiple customer numbers and dates, I am looking for just the last date that the customer ordered.

Select Distinct
tblarInvoice.CustomerShipTo,
tblarInvoice.ShipToName
From
tblarInvoice
Group By
tblarInvoice.CustomerShipTo, tblarInvoice.ShipToName

 

[gmmastros]

To get the last date ordered.....

Select tblarInvoice.CustomerShipTo,
       tblarInvoice.ShipToName,
       Max(tblarInvoice.InvoiceDate) As LastOrdered
From   tblarInvoice
Group By tblarInvoice.CustomerShipTo, tblarInvoice.ShipToName

One thing to be aware of... if a customer ordered something but had a different ship to, that would show on multiple lines in the result. Basically, the query I just wrote will return a distinct list of CustomerShipTo and ShipToName but also show the last date something was ordered.

Think of it this way... If I ordered a widget from you and had it shipped to my office and then later ordered another widget and had it shipped to my home, I would appear twice in the results because of the different ship to. The date shown would be the latest date shipped to each of the addresses.

Now... as to the original problem... can you try this:

Select I.Customer,
       I.InvoiceToName
From   tblarInvoice I
       Inner Join (
         Select Customer
         From   tblsoSO
         Group By Customer
         Having Count(NullIf(status, 'closed')) = 0
                And Max(dateordered) < '06/01/2013'
	     ) S 
         On I.Customer = S.Customer

If I am not mistaken, this query should only return customers where every sale is closed and the most recent sale was earlier than 6/1/2013.

I don't know if this query will work for you. If it does and you want me to explain it, just let me know.



-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

 

[dwg23]

gmmastros,
Time to explain!!!!
It looks like exactly what I was looking for! I will let my Boss see it in the morning. He is the one that can find the errors in a heart beat. But from the ones I checked it looks correct! Your query as it is returned multiple customer numbers so I added "distinct" to the select statement and it seems to have worked.

If I wanted to return the last date sold in the visible results would I just do this?

Select I.Customer,
I.InvoiceToName,
tblsoSO.dateordered,

From tblarInvoice I
Inner Join (
Select Customer
From tblsoSO
Group By Customer
Having Count(NullIf(status, 'closed')) = 0
And Max(dateordered) < '06/01/2013'
) S
On I.Customer = S.Customer


Again, please explain what you did and is there a book to learn this? As you can see I am in desperate need of some education.

 

[gmmastros]

Try this:

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:

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:

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.

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:

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.

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]

-- Put your code here

[/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

 

[dwg23]

Very nice!
You do an excellent job of explaining what you are doing and why.
I am going to save this page so I can refer back to it when I need to.
So are there any good books out there that explain how to write queries or do I just need to go online and get a through understanding of how the functions work?

 

[gmmastros]

I don't usually read technical books to learn new stuff. I usually read blogs and forum questions instead. I do know that Itzik Ben-gan is an excellent author with many books. I would recommend you get one of his books.

http://www.amazon.com/s/ref=nb_sb_s...d-keywords=itzik ben-gan&sprefix=itzi,aps,207

I would suggest that you get a copy of a book that matches your SQL Server version and has "TSQL Fundamentals" in the name. I've never read any of his books, but I know they have an excellent reputation.

-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

 

[dwg23]

Excellent and thank you again for all your help!