Tricky query grouping

[Guest]

Okay I have a table that lists products and all their prices. In addition
there is a field that lists the "status" of the item (sale, full price). I
want to have a query that shows me just every place the status changes and on
what date. The problem is that an item could have 4 different sale prices
and then be placed back at full price (seasonal items) and then returned to
sale. So with a simple grouping my output would look like:

Item 1 $20 1/1/05 Full Price
Item 1 $15 3/1/05 Sale
Item 1 $12 4/1/05 Sale
Item 1 $20 8/1/05 Full Price
Item 1 $18 11/1/05 Sale

I want an output like this:
Item 1 1/1/05 Full Price
Item 1 3/1/05 Sale
Item 1 8/1/05 Full Price
Item 1 11/1/05 Sale

Anyone have any ideas or suggestions? Thanks!

 

[Tom Ellison]

Dear JLamb:

The thing you want done can be done using a correlated subquery. This
subquery would return the status of the immediately previous row for the
same item. You could then exclude the current row if the previous one is
for the same status.

There will be a potential problem here. There must never be two rows for
the same item and the same date. Obviously, this makes the job of
determining whether the previous row is for the same status, since there
would be two rows equally qualified to be the previous row. I recommend
there be a constraint on the table in the form of a unique index by Item and
Date. Otherwise, the subquery will fail by virtue of returning both rows.
Since the table makes the job ambiguous, this is as it should be.

If you wish, I can provide the query you need. You must first post the
query that produces the first set you show, the raw data. Do not use a
wildcare (*) for the column names, but show them individually so I can use
them to produce the remainder of the query.

Tom Ellison

 

[Guest]

I know how to write a correlated subquery, but it would have to have only one
SELECT item, in this case that would be the status. But you would need to
pull the max date < the current row date. How would you accomplish this
without putting max([stat dt]) in the SELECT clause? A subquery within the
correlated subquery?

 

[Tom Ellison]

Dear JLamb:

Why do you believe you must NOT put MAX([stat dt]) in the SELECT clause?

How about:

SELECT Item, Amount, [stat dt], Status
FROM YourTable T
WHERE Status <> (SELECT Status FROM YourTable T1
WHERE T1.Item = T.Item AND T1.[stat dt] =
(SELECT MAX([stat dt] FROM YourTable T2
WHERE T2.Item = T.Item AND T2.[stat dt] < T.[stat dt]))

Again, this will not work if there are multiple rows for an Item on the same
date.

I have seen this structure work in Access Jet, and I have seen it fail. I'm
not sure what determines whether it will work or not. This query would have
no problem in MSDE. I don't know which you use, but for really serious
query work, I recommend MSDE strongly. However, this CAN be done in Jet
even if the above fails for you. Let me know if you need details of this.

You must, of course, ammend the above to use your actual column and table
names.

Tom Ellison

I know how to write a correlated subquery, but it would have to have only
one
SELECT item, in this case that would be the status. But you would need to
pull the max date < the current row date. How would you accomplish this
without putting max([stat dt]) in the SELECT clause? A subquery within
the
correlated subquery?

Dear JLamb:

The thing you want done can be done using a correlated subquery. This
subquery would return the status of the immediately previous row for the
same item. You could then exclude the current row if the previous one is
for the same status.

There will be a potential problem here. There must never be two rows for
the same item and the same date. Obviously, this makes the job of
determining whether the previous row is for the same status, since there
would be two rows equally qualified to be the previous row. I recommend
there be a constraint on the table in the form of a unique index by Item
and
Date. Otherwise, the subquery will fail by virtue of returning both
rows.
Since the table makes the job ambiguous, this is as it should be.

If you wish, I can provide the query you need. You must first post the
query that produces the first set you show, the raw data. Do not use a
wildcare (*) for the column names, but show them individually so I can
use
them to produce the remainder of the query.

Tom Ellison

 

[Guest]

As I said with a correlated subquery you can only return one value which in
this case would have to be [status]. This code does use a second nested
correlated subquery within the subquery as I suspected was the only way to
accomplish your original suggestion. I believe this may work, but I was
hoping (probably unrealistically) for a much simpler solution. Thanks!

Dear JLamb:

Why do you believe you must NOT put MAX([stat dt]) in the SELECT clause?

How about:

SELECT Item, Amount, [stat dt], Status
FROM YourTable T
WHERE Status <> (SELECT Status FROM YourTable T1
WHERE T1.Item = T.Item AND T1.[stat dt] =
(SELECT MAX([stat dt] FROM YourTable T2
WHERE T2.Item = T.Item AND T2.[stat dt] < T.[stat dt]))

Again, this will not work if there are multiple rows for an Item on the same
date.

I have seen this structure work in Access Jet, and I have seen it fail. I'm
not sure what determines whether it will work or not. This query would have
no problem in MSDE. I don't know which you use, but for really serious
query work, I recommend MSDE strongly. However, this CAN be done in Jet
even if the above fails for you. Let me know if you need details of this.

You must, of course, ammend the above to use your actual column and table
names.

Tom Ellison

I know how to write a correlated subquery, but it would have to have only
one
SELECT item, in this case that would be the status. But you would need to
pull the max date < the current row date. How would you accomplish this
without putting max([stat dt]) in the SELECT clause? A subquery within
the
correlated subquery?

Dear JLamb:

The thing you want done can be done using a correlated subquery. This
subquery would return the status of the immediately previous row for the
same item. You could then exclude the current row if the previous one is
for the same status.

There will be a potential problem here. There must never be two rows for
the same item and the same date. Obviously, this makes the job of
determining whether the previous row is for the same status, since there
would be two rows equally qualified to be the previous row. I recommend
there be a constraint on the table in the form of a unique index by Item
and
Date. Otherwise, the subquery will fail by virtue of returning both
rows.
Since the table makes the job ambiguous, this is as it should be.

If you wish, I can provide the query you need. You must first post the
query that produces the first set you show, the raw data. Do not use a
wildcare (*) for the column names, but show them individually so I can
use
them to produce the remainder of the query.

Tom Ellison


Okay I have a table that lists products and all their prices. In
addition
there is a field that lists the "status" of the item (sale, full
price).
I
want to have a query that shows me just every place the status changes
and
on
what date. The problem is that an item could have 4 different sale
prices
and then be placed back at full price (seasonal items) and then
returned
to
sale. So with a simple grouping my output would look like:

Item 1 $20 1/1/05 Full Price
Item 1 $15 3/1/05 Sale
Item 1 $12 4/1/05 Sale
Item 1 $20 8/1/05 Full Price
Item 1 $18 11/1/05 Sale

I want an output like this:
Item 1 1/1/05 Full Price
Item 1 3/1/05 Sale
Item 1 8/1/05 Full Price
Item 1 11/1/05 Sale

Anyone have any ideas or suggestions? Thanks!