Re: SQL (Venn diagram type of logic) - Mailing list pgsql-sql

Hello Aaron,

Thank you for the help.  My apologies for the direct
email but i couldn't figure out how to reply to the
thread on the postresql site.

Once i get the purchases seperated by categories, I
plan to track the month on month status of each
account.

For example, in may 2006, based on her previous
purchaes, customer1 was in category A but in june
2006, she bought something in category B.  This would
make her in bucket 2.

bucket 1: A
bucket 2: A&B
bucket 3: A&C
bucket 4: B
bucket 5: B&C
bucket 6: C
bucket 7: A&B&C

(these are the combinations i was thinking about when
i had the venn diagram in my head.)

To follow with the example above:

In May, she would be counted as part of bucket 1 and
her total spending (life to may) will be part of
running total for bucket 1

In June,  she would be counted as part of bucket 2 and
her total spending (life to june) will be part of
running total for bucket 2

In the end, we will have count(customers),
sum(all_purchases) for each of the 7 buckets for each
month.  From a business perpective, we hope this
exercise will show the general migration patterns of
customers from one bucket to another over time.

I figured out a way to do it but it is no where near
efficient.

i added three boolean fields to the account table to
indicate catA, catB, and catC.  i update the table 3
times for each of the categories, turning on the
boolean fields where applicable.

then i select the count, and sum fields from an inner
join on account and purchase tables for each of the 7
buckets.  it works, but i would manually do it for
each month.

any thoughts would be most appreciated.  thanks and
please have a great day.