asked on

Alternative to crosstab query if possible.

Hi Experts,
I have a query that gives me a variable set of data based on the date range the user entered. Each record returns a price that I need to compare(or rather do some calculations) with previous records prices if they exist.
1. I created a crosstab query with row headings: CustomerName, Account, Product, datePurchased and column heading with the prices. The problem here is that I am getting headings such as $10.02, $40.25, $30.15... corresponding on the amount the customer spent on that date. How do I change the headings to say Price1, Price2, Price3 dynamically corresponding to the number of records for the customers. I would like to compare the records 2 at a time; finding the rate between price2/price1 then price3/price2 etc...

2. The other scenario I thought about would be to create a table from my resultset then add a last column that will allow me to run some calculation between the prices (current record vs previous record) if there is more than one record. I keep seeing example of how to add a new field to an existing table but I don't see any example with adding new field and data.

I'd appreciate any help.

GRayL

Show us several records of the data. Then show us what you would like to see.

GRayL

We really need to see the structure of the data source.

YvesBouele

ASKER

CustomerName        Account        Product DatePurchased       Prices        Amount
ACME 1 000000004      20031231      3/29/2004      2747.288136      $16,209.00
ACMCTWOHCY      00000000420031231      3/29/2004      2747.288136      $16,209.00
ACMCTHREECY       000000004      20031231      3/29/2004      2747.288136      $16,209.00
ACMCFOURCY4 000000004      20031231      3/29/2004      2747.288136      $16,209.00

YvesBouele

ASKER

Sorry, the last post was a mistake
This is a sample of the structure i am dealing with.

CustomerName Account Product DatePurchased Unit Price Amount
ACMC1ONECY 000000004 PRODUCT1 3/29/2004 1.08 $10.02
ACMC1ONECY 000000004 PRODUCT1 2/20/2005 2.03 $40.25
ACMC1ONECY 000000004 PRODUCT1 8/28/2006 1.14 $30.15
ACMC1ONECY 000000004 PRODUCT1 12/21/2007 2.06 $15.30

When I do the crosstab query , I get the following

CustomerName Account Product $10.02 $40.25 $30.15 $55.30
ACMC1ONECY 000000004 PRODUCT1 $10.02 $40.25 $30.15 $55.30

Eventually i would like something like this:
CustomerName Account Product Rate
ACMC1ONECY 000000004 PRODUCT1 40.25/10.02 =~401%
ACMC1ONECY 000000004 PRODUCT1 30.15/40.25 =~75%
ACMC1ONECY 000000004 PRODUCT1 15.30/30.15 =~50.7%

GRayL

So you have four different customers all using the same account (?) who purchased the same product on the same date for the same prices and amount. So what do you want to see?

GRayL

Sorry, my last post was a mistake. I should have re-freshed.

GRayL

As the Amount varies by the number purchased, shouldn't you be comparing Unit Price?

YvesBouele

ASKER

Hi GRayL,
it's all one customer ACMC1ONECY. I need to track all transactions made by one customer at a time.
you made one good point though, I should be comparing the unit prices instead. But the issue still remaining that I need to compare each subsequent events on the customer.

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

YvesBouele

ASKER

Please review your syntax for me. There are 4 open parentheses and 5 closing ones. also I don't understand the sign - in front of a.Product : ... AND b.Product=a.Product))-a.Product)/a.Product). Other than that I am getting quite confident in your solution

SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

YvesBouele

ASKER

there were few syntax error that I was able to clean up such as comma in "Select c.Amount, FROM" and space in "a. Product,". Also the result RateRange comes on the first record instead of the record of the subsequent occurrence. For example the first record should have 100% all the time but the second should show the percentage of the second purchase in relation to the first purchase

YvesBouele

ASKER

How will I apply the percentage format?

GRayL

This do it? I used Format(<long long sub query>,"Percent")

SELECT a.CustomerName, a.Account, a.Product, Format(((Select c.Amount FROM myTable AS c WHERE c.CustomerName=a.CustomerName AND c.Account=a.Account AND c.Product=a.Product AND c.DatePurchased IN (SELECT Min(b.DatePurchased) FROM myTable AS b WHERE b.DatePurchased>a.DatePurchased AND b.CustomerName=a.CustomerName AND b.Account=a.Account AND b.Product=a.Product))-a.Product)/a.Product),"Percent") AS RateChange FROM myTable AS a;

I can give you the first record always 100% but that is not what you showed at http:#a20367609. When you get the percent working, paste the whole SQL back here and I'll change the order as per your grading comments.

YvesBouele

ASKER

We have come very far already. I will try to be as clear as possible as to what i am looking for. This is the end result
CustomerName Product DatePurchased Unit Price Qty NetDiff Rate Amount NewAmount
ACMC1ONECY PRODUCT1 3/29/2004 1.08 80 80 100% $86.40 1 * 86.40
ACMC1ONECY PRODUCT1 2/20/2005 2.03 35 45 43.75% $71.05 0.437 * 71.05
ACMC1ONECY PRODUCT1 8/28/2006 1.14 20 15 44.44% $22.8 0.44 * 22.8
ACMC1ONECY PRODUCT1 12/21/2007 2.06 10 5 66.67% $20.60 0.667 * 20.60

ACMC1TWOCY PRODUCT2 3/29/2004 1.08 90 90 100% $97.2
ACMC1TWOCY PRODUCT2 2/20/2005 2.03 40 50 44.44% $81.2
ACMC1TWOCY PRODUCT2 8/28/2006 1.14 20 30 40% $22.8
ACMC1TWOCY PRODUCT2 12/21/2007 2.06 10 20 33.33% $20.60

Originally I don't have the Net, Rate and Amount fields
How did I calculate these numbers?
NetDiff: Previous Qty - Actual Qty
Rate: Actual Qty / Previous Qty
Amount: Qty * Unit Price
New Amount: Rate * Amount

when I run your code I get (for example) 43.75% on the first line and the rest is just as confusing
CustomerName Product DatePurchased Unit Price Qty NetDiff Rate
ACMC1ONECY PRODUCT1 3/29/2004 1.08 80 80 43.75%
i am sure we're on the right path I just need to sort out all this logic.

GRayL

Yves. I am having a difficult time sorting out the logic of the steps required to complete this task. I can generate this series

Qty PrevQty
80 0
35 80
20 35
10 20

It would appear to get the NetDiff I need this -(PrevQty-Qty) which fails for the fourth line, I get 10, you show 5??

YvesBouele

ASKER

yes it's 5, a typo error

YvesBouele

ASKER

yes it's 10 not 5

GRayL

That's been tearing my hair out for the last day. Now we're getting somewhere - but isn't this a new question?

YvesBouele

ASKER

If that a new question, that's the issue I have had all along. Are you suggesting I add more points to this already maxed out request ticket? I will do whatever is necessary to help me get to a solution.

GRayL

You state netdiff = prev qty-actual qty. By my calcs, when you are on the first record, the previous value is Null. I cannot generate a scenario that gives you the numbers you are looking for mathematically. Rate appears to be netDiff/prev qty - not as you stated. Here's what I get:

Running this query:

SELECT a.CustomerName, a.Account, a.Product, a.Qty, (
SELECT c.Qty FROM Sales AS c WHERE c.CustomerName=a.CustomerName AND c.Product = a.Product AND c.DatePurchased IN (
SELECT Max(b.DatePurchased) FROM Sales AS b WHERE b.DatePurchased<a.DatePurchased AND b.CustomerName=a.CustomerName AND b.Account=a.Account AND b.Product=a.Product)) AS PrevQty,PrevQty-A.Qty as NetDiff,Format(NetDiff/PrevQty,"Percent") As Rate
FROM Sales AS a;

I get this result:

CustomerName      Account      Product      Qty      PrevQty      NetDiff      Rate
ACMC1ONECY      000000004      PRODUCT1      80
ACMC1ONECY      000000004      PRODUCT1      35      80      45      56.25%
ACMC1ONECY      000000004      PRODUCT1      20      35      15      42.86%
ACMC1ONECY      000000004      PRODUCT1      10      20      10      50.00%
ACMC1TWOCY      000000004      PRODUCT2      90
ACMC1TWOCY      000000004      PRODUCT2      40      90      50      55.56%
ACMC1TWOCY      000000004      PRODUCT2      20      40      20      50.00%
ACMC1TWOCY      000000004      PRODUCT2      10      20      10      50.00%

To move on from here I think you should open a new question.

YvesBouele

ASKER

Thank you so much !!!