Count issue continued from "text then date ascending count"

  • Thread starter Thread starter Cameron
  • Start date Start date
C

Cameron

I am going back to a problem called "text then date ascending count" I tried
to address almost a year ago, here is the link
http://www.microsoft.com/office/com...4faf40-ff27-4cde-b94a-ed679a274fb0&sloc=en-us

Every solution has given me a different type of mis-sorting of the list. I
am trying to sort a list of sales records so that I can create a field

PurchaseRank. Each Sale has an alphanumeric customerID, PurchaseDate, and
PurchaseNumber. A Recordset could look like this:

customerID PurchaseDate PurchaseNumber
A10 7/29/2002 101
A10 8/20/2002 221
B20 3/19/2009 420
B20 4/23/2009 403
C30 6/15/2006 339
C30 6/15/2006 331
D40 7/22/2002 7671
D40 10/18/2004 20708




There are several issues creating a Purchase Rank for the set. One problem
is that the PrimaryKey PurchaseNumber isn't necessary in ascending order
based on

the PurchaeDate. This is because a PurchaseNumber can be placed on hold
once created until it is finalized, this is the case for customerID B12.

Another issue is there can be more than one purchase on the same day. In
this case I don't care which one is ranked first or second.

This is so simple to do for me in excel, I just sort by CustomerID and then
by PurchaseDate, then I use a countif formula to get the rank.

Example 1 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseNumber PurchaseRank
A10 7/29/2002 101 1
A10 8/20/2002 221 2
B20 3/19/2009 420 2
B20 4/23/2009 403 1
C30 6/15/2006 339 2
C30 6/15/2006 331 1
D40 7/22/2002 7671 1
D40 10/18/2004 20708 2

Example 2 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseRank PurchaseNumber
A10 7/29/2002 1 101
A10 8/20/2002 2 221
B20 3/19/2009 2 420
B20 4/23/2009 1 403
C30 6/15/2006 2 339
C30 6/15/2006 1 331
D40 7/22/2002 1 7671
D40 10/18/2004 2 20708



If anyone knows how to make this work, or a different way to do this, please
let me know. Thanks
 
Try this --
SELECT T.customerID, T.PurchaseDate, T.PurchaseNumber, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate<=
T.PurchaseDate) AS PurchaseRank
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

--
Build a little, test a little.


Cameron said:
I am going back to a problem called "text then date ascending count" I tried
to address almost a year ago, here is the link:
http://www.microsoft.com/office/com...4faf40-ff27-4cde-b94a-ed679a274fb0&sloc=en-us

Every solution has given me a different type of mis-sorting of the list. I
am trying to sort a list of sales records so that I can create a field

PurchaseRank. Each Sale has an alphanumeric customerID, PurchaseDate, and
PurchaseNumber. A Recordset could look like this:

customerID PurchaseDate PurchaseNumber
A10 7/29/2002 101
A10 8/20/2002 221
B20 3/19/2009 420
B20 4/23/2009 403
C30 6/15/2006 339
C30 6/15/2006 331
D40 7/22/2002 7671
D40 10/18/2004 20708




There are several issues creating a Purchase Rank for the set. One problem
is that the PrimaryKey PurchaseNumber isn't necessary in ascending order
based on

the PurchaeDate. This is because a PurchaseNumber can be placed on hold
once created until it is finalized, this is the case for customerID B12.

Another issue is there can be more than one purchase on the same day. In
this case I don't care which one is ranked first or second.

This is so simple to do for me in excel, I just sort by CustomerID and then
by PurchaseDate, then I use a countif formula to get the rank.

Example 1 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseNumber PurchaseRank
A10 7/29/2002 101 1
A10 8/20/2002 221 2
B20 3/19/2009 420 2
B20 4/23/2009 403 1
C30 6/15/2006 339 2
C30 6/15/2006 331 1
D40 7/22/2002 7671 1
D40 10/18/2004 20708 2

Example 2 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseRank PurchaseNumber
A10 7/29/2002 1 101
A10 8/20/2002 2 221
B20 3/19/2009 2 420
B20 4/23/2009 1 403
C30 6/15/2006 2 339
C30 6/15/2006 1 331
D40 7/22/2002 1 7671
D40 10/18/2004 2 20708



If anyone knows how to make this work, or a different way to do this, please
let me know. Thanks
Click to expand...
 
SELECT T.customerID, T.PurchaseDate
, 1 + (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID
AND T1.PurchaseDate < T.PurchaseDate
) AS PurchaseRank
, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

That will get you closer but you will still have ties for purchases on
the same date by the same customer.

WHERE are you going to use this PurchaseRank. If you intend to use it
in a report, there is a much simpler way of getting a one up number for
each line in a group of records. You don't even need to calculate it in
the query.

Simply group the report by customerId and sort by Purchase date (and if
desired purchaseNumber). Add a control to the detail line of the report
and set its source to =1. Then choose RUNNING SUM OVER GROUP.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am going back to a problem called "text then date ascending count" I tried
to address almost a year ago, here is the link:
http://www.microsoft.com/office/com...4faf40-ff27-4cde-b94a-ed679a274fb0&sloc=en-us

Every solution has given me a different type of mis-sorting of the list. I
am trying to sort a list of sales records so that I can create a field

PurchaseRank. Each Sale has an alphanumeric customerID, PurchaseDate, and
PurchaseNumber. A Recordset could look like this:

customerID PurchaseDate PurchaseNumber
A10 7/29/2002 101
A10 8/20/2002 221
B20 3/19/2009 420
B20 4/23/2009 403
C30 6/15/2006 339
C30 6/15/2006 331
D40 7/22/2002 7671
D40 10/18/2004 20708




There are several issues creating a Purchase Rank for the set. One problem
is that the PrimaryKey PurchaseNumber isn't necessary in ascending order
based on

the PurchaeDate. This is because a PurchaseNumber can be placed on hold
once created until it is finalized, this is the case for customerID B12.

Another issue is there can be more than one purchase on the same day. In
this case I don't care which one is ranked first or second.

This is so simple to do for me in excel, I just sort by CustomerID and then
by PurchaseDate, then I use a countif formula to get the rank.

Example 1 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseNumber PurchaseRank
A10 7/29/2002 101 1
A10 8/20/2002 221 2
B20 3/19/2009 420 2
B20 4/23/2009 403 1
C30 6/15/2006 339 2
C30 6/15/2006 331 1
D40 7/22/2002 7671 1
D40 10/18/2004 20708 2

Example 2 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseRank PurchaseNumber
A10 7/29/2002 1 101
A10 8/20/2002 2 221
B20 3/19/2009 2 420
B20 4/23/2009 1 403
C30 6/15/2006 2 339
C30 6/15/2006 1 331
D40 7/22/2002 1 7671
D40 10/18/2004 2 20708



If anyone knows how to make this work, or a different way to do this, please
let me know. Thanks
Click to expand...
 
This fails the C30 case where the PurchaseDate for more than one sale is the
same. It give you identital sale rank of 2.

KARL DEWEY said:
Try this --
SELECT T.customerID, T.PurchaseDate, T.PurchaseNumber, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate<=
T.PurchaseDate) AS PurchaseRank
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

--
Build a little, test a little.


Cameron said:
I am going back to a problem called "text then date ascending count" I tried
to address almost a year ago, here is the link:
http://www.microsoft.com/office/com...4faf40-ff27-4cde-b94a-ed679a274fb0&sloc=en-us

Every solution has given me a different type of mis-sorting of the list. I
am trying to sort a list of sales records so that I can create a field

PurchaseRank. Each Sale has an alphanumeric customerID, PurchaseDate, and
PurchaseNumber. A Recordset could look like this:

customerID PurchaseDate PurchaseNumber
A10 7/29/2002 101
A10 8/20/2002 221
B20 3/19/2009 420
B20 4/23/2009 403
C30 6/15/2006 339
C30 6/15/2006 331
D40 7/22/2002 7671
D40 10/18/2004 20708




There are several issues creating a Purchase Rank for the set. One problem
is that the PrimaryKey PurchaseNumber isn't necessary in ascending order
based on

the PurchaeDate. This is because a PurchaseNumber can be placed on hold
once created until it is finalized, this is the case for customerID B12.

Another issue is there can be more than one purchase on the same day. In
this case I don't care which one is ranked first or second.

This is so simple to do for me in excel, I just sort by CustomerID and then
by PurchaseDate, then I use a countif formula to get the rank.

Example 1 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseNumber PurchaseRank
A10 7/29/2002 101 1
A10 8/20/2002 221 2
B20 3/19/2009 420 2
B20 4/23/2009 403 1
C30 6/15/2006 339 2
C30 6/15/2006 331 1
D40 7/22/2002 7671 1
D40 10/18/2004 20708 2

Example 2 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseRank PurchaseNumber
A10 7/29/2002 1 101
A10 8/20/2002 2 221
B20 3/19/2009 2 420
B20 4/23/2009 1 403
C30 6/15/2006 2 339
C30 6/15/2006 1 331
D40 7/22/2002 1 7671
D40 10/18/2004 2 20708



If anyone knows how to make this work, or a different way to do this, please
let me know. Thanks
Click to expand...
Click to expand...
 
This fails the C30 Test Case where there is more than one sale for the same
day, this solution gives the rank of 1 for both sales.

John Spencer said:
SELECT T.customerID, T.PurchaseDate
, 1 + (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID
AND T1.PurchaseDate < T.PurchaseDate
) AS PurchaseRank
, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

That will get you closer but you will still have ties for purchases on
the same date by the same customer.

WHERE are you going to use this PurchaseRank. If you intend to use it
in a report, there is a much simpler way of getting a one up number for
each line in a group of records. You don't even need to calculate it in
the query.

Simply group the report by customerId and sort by Purchase date (and if
desired purchaseNumber). Add a control to the detail line of the report
and set its source to =1. Then choose RUNNING SUM OVER GROUP.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am going back to a problem called "text then date ascending count" I tried
to address almost a year ago, here is the link:
http://www.microsoft.com/office/com...4faf40-ff27-4cde-b94a-ed679a274fb0&sloc=en-us

Every solution has given me a different type of mis-sorting of the list. I
am trying to sort a list of sales records so that I can create a field

PurchaseRank. Each Sale has an alphanumeric customerID, PurchaseDate, and
PurchaseNumber. A Recordset could look like this:

customerID PurchaseDate PurchaseNumber
A10 7/29/2002 101
A10 8/20/2002 221
B20 3/19/2009 420
B20 4/23/2009 403
C30 6/15/2006 339
C30 6/15/2006 331
D40 7/22/2002 7671
D40 10/18/2004 20708




There are several issues creating a Purchase Rank for the set. One problem
is that the PrimaryKey PurchaseNumber isn't necessary in ascending order
based on

the PurchaeDate. This is because a PurchaseNumber can be placed on hold
once created until it is finalized, this is the case for customerID B12.

Another issue is there can be more than one purchase on the same day. In
this case I don't care which one is ranked first or second.

This is so simple to do for me in excel, I just sort by CustomerID and then
by PurchaseDate, then I use a countif formula to get the rank.

Example 1 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseNumber PurchaseRank
A10 7/29/2002 101 1
A10 8/20/2002 221 2
B20 3/19/2009 420 2
B20 4/23/2009 403 1
C30 6/15/2006 339 2
C30 6/15/2006 331 1
D40 7/22/2002 7671 1
D40 10/18/2004 20708 2

Example 2 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseRank PurchaseNumber
A10 7/29/2002 1 101
A10 8/20/2002 2 221
B20 3/19/2009 2 420
B20 4/23/2009 1 403
C30 6/15/2006 2 339
C30 6/15/2006 1 331
D40 7/22/2002 1 7671
D40 10/18/2004 2 20708



If anyone knows how to make this work, or a different way to do this, please
let me know. Thanks
Click to expand...
Click to expand...
 
I said that would be the case. Would you care to tell us where you are going
to use the query result? As I said, if you are trying to use it in a report
then you may not need to do this at all in a query.

What is the importance of breaking the ties? Perhaps there is some other
solution.

You could always use math on purchasedate and purchase rank and then do the
comparison. An expression like the following should give a unique value for
the comparison/ranking:
T1.PurchaseDate*100000 + T1.PurchaseNumber


SELECT T.customerID, T.PurchaseDate
, 1 + (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID
AND T1.PurchaseDate*100000 + T1.PurchaseNumber
< T.PurchaseDate*100000+T.PurchaseNumber
) AS PurchaseRank
, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

This should work as long as your purchase number does not exceed 99999. If
that is possibility you can increase the multiplier used in the expression.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
This works. I just wish there was a solution without using math that
lengthens the process time. I will probably reduce the multiplier to allow
this query to run faster. There are currently only about 12000 records in
the table and so the highest PurchaseNumber is about the same. It is really
easy to do in excel by sorting the CustomerID and then PurchaseDate by
ascending order. Then I run the function COUNTIF($A$2:$A2, A2) for the
PurchaseRank, column A is CustomerID. The results of this query are used to
add the PurchaseRank to the sales table. This number is important for many
different programs we have. We rank the total purchases of a customer (not
listed in this query) and the history number (PurchaseRank) so we can see
that a customer has purchased from us say 5 times, and see the order of
purchases with the PurchaseRank. I actually tried this solution last year
but I did not add in the multiplier or the 1+, what is the 1+ for?
Thanks very much. If anyone else has any other ideas please reply.
Cameron


John Spencer said:
I said that would be the case. Would you care to tell us where you are going
to use the query result? As I said, if you are trying to use it in a report
then you may not need to do this at all in a query.

What is the importance of breaking the ties? Perhaps there is some other
solution.

You could always use math on purchasedate and purchase rank and then do the
comparison. An expression like the following should give a unique value for
the comparison/ranking:
T1.PurchaseDate*100000 + T1.PurchaseNumber


SELECT T.customerID, T.PurchaseDate
, 1 + (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID
AND T1.PurchaseDate*100000 + T1.PurchaseNumber
< T.PurchaseDate*100000+T.PurchaseNumber
) AS PurchaseRank
, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

This should work as long as your purchase number does not exceed 99999. If
that is possibility you can increase the multiplier used in the expression.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
This fails the C30 Test Case where there is more than one sale for the same
day, this solution gives the rank of 1 for both sales.

:
SNIP
Click to expand...
Click to expand...
 
The expression returns zero for the first rank and 1 for the second, etc.
Adding 1 to the expression returns ranks starting with 1 rather than zero.

The multiplier being smaller will not have any increase on the speed of the
operation. The only way to make things faster would be to store the
calculated value in your table and then apply an index to that stored value.
Once that was done, you could see a fair increase in speed.

So you would need another field - PositionNumber (Double or Long integer if
you decrease the multiplier to 10000 or less) that stores the value of
PurchaseDate*100000 + PurchaseNumber and then index that field. You would have
to have some mechanism (an update query or code in the entry form to populate
the field). Apply an index to the field and then use the field in place of
the expression.

There is an alternative method of calculating ranking that is sometimes
faster. That should look something like the following.

SELECT T.customerID, T.PurchaseDate
, 1 + Count(T2.CustomerID) AS PurchaseRank
, T.PurchaseNumber
FROM Sales AS T LEFT JOIN Sales as T2
ON T.CustomerID = T2.CustomerID
AND T.PurchaseDate*100000 + T.PurchaseNumber
< T2.PurchaseDate*100000+T2.PurchaseNumber
GROUP BY T.customerID, T.PurchaseDate, PurchaseNumber
ORDER BY T.customerID, T.PurchaseDate, PurchaseNumber;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
This works. I just wish there was a solution without using math that
lengthens the process time. I will probably reduce the multiplier to allow
this query to run faster. There are currently only about 12000 records in
the table and so the highest PurchaseNumber is about the same. It is really
easy to do in excel by sorting the CustomerID and then PurchaseDate by
ascending order. Then I run the function COUNTIF($A$2:$A2, A2) for the
PurchaseRank, column A is CustomerID. The results of this query are used to
add the PurchaseRank to the sales table. This number is important for many
different programs we have. We rank the total purchases of a customer (not
listed in this query) and the history number (PurchaseRank) so we can see
that a customer has purchased from us say 5 times, and see the order of
purchases with the PurchaseRank. I actually tried this solution last year
but I did not add in the multiplier or the 1+, what is the 1+ for?
Thanks very much. If anyone else has any other ideas please reply.
Cameron


John Spencer said:
I said that would be the case. Would you care to tell us where you are going
to use the query result? As I said, if you are trying to use it in a report
then you may not need to do this at all in a query.

What is the importance of breaking the ties? Perhaps there is some other
solution.

You could always use math on purchasedate and purchase rank and then do the
comparison. An expression like the following should give a unique value for
the comparison/ranking:
T1.PurchaseDate*100000 + T1.PurchaseNumber


SELECT T.customerID, T.PurchaseDate
, 1 + (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID
AND T1.PurchaseDate*100000 + T1.PurchaseNumber
< T.PurchaseDate*100000+T.PurchaseNumber
) AS PurchaseRank
, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

This should work as long as your purchase number does not exceed 99999. If
that is possibility you can increase the multiplier used in the expression.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
This fails the C30 Test Case where there is more than one sale for the same
day, this solution gives the rank of 1 for both sales.

:
SNIP
Results:
customerID PurchaseDate PurchaseRank PurchaseNumber
A10 7/29/2002 1 101
A10 8/20/2002 2 221
B20 3/19/2009 2 420
B20 4/23/2009 1 403
C30 6/15/2006 2 339
C30 6/15/2006 1 331
D40 7/22/2002 1 7671
D40 10/18/2004 2 20708



If anyone knows how to make this work, or a different way to do this, please
let me know. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Try this --
SELECT T.customerID, T.PurchaseDate, T.PurchaseNumber, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate &
T1.PurchaseNumber<= T.PurchaseDate & T.PurchaseNumber) AS PurchaseRank
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

--
Build a little, test a little.


Cameron said:
This fails the C30 case where the PurchaseDate for more than one sale is the
same. It give you identital sale rank of 2.

KARL DEWEY said:
Try this --
SELECT T.customerID, T.PurchaseDate, T.PurchaseNumber, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate<=
T.PurchaseDate) AS PurchaseRank
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

--
Build a little, test a little.


Cameron said:
I am going back to a problem called "text then date ascending count" I tried
to address almost a year ago, here is the link:
http://www.microsoft.com/office/com...4faf40-ff27-4cde-b94a-ed679a274fb0&sloc=en-us

Every solution has given me a different type of mis-sorting of the list. I
am trying to sort a list of sales records so that I can create a field

PurchaseRank. Each Sale has an alphanumeric customerID, PurchaseDate, and
PurchaseNumber. A Recordset could look like this:

customerID PurchaseDate PurchaseNumber
A10 7/29/2002 101
A10 8/20/2002 221
B20 3/19/2009 420
B20 4/23/2009 403
C30 6/15/2006 339
C30 6/15/2006 331
D40 7/22/2002 7671
D40 10/18/2004 20708




There are several issues creating a Purchase Rank for the set. One problem
is that the PrimaryKey PurchaseNumber isn't necessary in ascending order
based on

the PurchaeDate. This is because a PurchaseNumber can be placed on hold
once created until it is finalized, this is the case for customerID B12.

Another issue is there can be more than one purchase on the same day. In
this case I don't care which one is ranked first or second.

This is so simple to do for me in excel, I just sort by CustomerID and then
by PurchaseDate, then I use a countif formula to get the rank.

Example 1 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseNumber PurchaseRank
A10 7/29/2002 101 1
A10 8/20/2002 221 2
B20 3/19/2009 420 2
B20 4/23/2009 403 1
C30 6/15/2006 339 2
C30 6/15/2006 331 1
D40 7/22/2002 7671 1
D40 10/18/2004 20708 2

Example 2 that fails to Rank CustomerID B20 correctly:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Sales] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseNumber <=
T.PurchaseNumber) AS PurchaseRank, T.PurchaseNumber
FROM Sales AS T
ORDER BY T.customerID, T.PurchaseDate;

Results:
customerID PurchaseDate PurchaseRank PurchaseNumber
A10 7/29/2002 1 101
A10 8/20/2002 2 221
B20 3/19/2009 2 420
B20 4/23/2009 1 403
C30 6/15/2006 2 339
C30 6/15/2006 1 331
D40 7/22/2002 1 7671
D40 10/18/2004 2 20708



If anyone knows how to make this work, or a different way to do this, please
let me know. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top