Customer Penetration

[Chris]

I'm creating an extract to show customer penetration. The process is moving
aloing quiet nicely but I do have one problem I don't know how to solve.
Over a twelve month period I need to show a record if there was one month in
that 12 month time period where sales were 100 or greater. example:


Customer 1.
Jan05 Feb05 Mar05 Apr05 May05 Jun05 Jul05 Aug05 Sep05 Oct05 Nov05
Dec05
50 0 10 25 30 14 23 40
60 70 15 80

Customer 2.
Jan05 Feb05 Mar05 Apr05 May05 Jun05 Jul05 Aug05 Sep05 Oct05 Nov05
Dec05
50 0 10 25 30 14 23 100
60 70 15 80

In these two examples I would only be interested in customer 2 becaue in
Aug05 sales reached 100. the solution would be easy for me if I could just
sum the months up but that's not what I'm looking to do. I have to scan
each month by customer and determine what customers had one month in a 12
month period where sales were 100 or greater. This I don't know how to do.
Obvisouly the above data is just for example sake. Thanks in advance for
your help.

Chris

 

[Smartin]

I'm creating an extract to show customer penetration. The process is moving
aloing quiet nicely but I do have one problem I don't know how to solve.
Over a twelve month period I need to show a record if there was one month in
that 12 month time period where sales were 100 or greater. example:


Customer 1.
Jan05 Feb05 Mar05 Apr05 May05 Jun05 Jul05 Aug05 Sep05 Oct05 Nov05
Dec05
50 0 10 25 30 14 23 40
60 70 15 80

Customer 2.
Jan05 Feb05 Mar05 Apr05 May05 Jun05 Jul05 Aug05 Sep05 Oct05 Nov05
Dec05
50 0 10 25 30 14 23 100
60 70 15 80

In these two examples I would only be interested in customer 2 becaue in
Aug05 sales reached 100. the solution would be easy for me if I could just
sum the months up but that's not what I'm looking to do. I have to scan
each month by customer and determine what customers had one month in a 12
month period where sales were 100 or greater. This I don't know how to do.
Obvisouly the above data is just for example sake. Thanks in advance for
your help.

Chris

Hi Chris,

Without knowing your table structure we can't but provide a generalized
solution. This sort of construct might give you some ideas:

SELECT DISTINCT A.*
FROM CUSTOMERS AS A INNER JOIN CUSTOMERS AS B
ON A.ID = B.ID
WHERE B.SALES > 100

This returns everything about customers that have at least one sales > 100.

HTH

 

[Chris]

Actually that is the table structure. In the table we just have customer id
and then 12 feilds that represent the past 12 months so the table looks like
this:
Cust_Num Jan05 Feb05 Mar05 Apr05 May05 Jun05 Jul05 Aug05 Sep05
Oct05 Nov05

Does this help?

 

[Smartin]

Hi Chris,

The unnormalized table is going to make things a bit more tedious. Now
we are looking at a query such as

SELECT *
FROM Customers
WHERE
Field1 > 100
OR Field2 > 100
OR Field3 > 100
etc...
;

Should you ever add more fields to hold monthly data (which I strongly
do /not/ encourage), you will need to add more OR statements. By the
same token, should you rename any of the fields (which looks likely
since you refer to specific calendar months) you will need to change the
field name references in the WHERE clause every time.

This is why it is sometimes said "records are cheap, fields are
expensive". The expense comes in when you need to find related data in a
record.

On the other hand if you break down the structure to reflect the
entities properly, you would have one table of customers (with /no/
financials), and a separate table of financials with: a field that
identifies the customer, a field that indicates the month, /one month
per record/, and a field that holds the corresponding dollar amount.

Then you can capitalize on the "cheapness" of related data that is
stored in separate records, which is where my generalized example came
from. In this format the names of months, the number of months you want
to store, or check, is largely irrelevant, and if you change storage the
requirements e.g. to add months, a well designed query will not need to
be modified at all! Neither, for that matter, will the underlying tables.

Hoping this helps,