Extract most recent price reduction and date in series of fields in same row

[jason]

If one has a series of price reduction fields (3) and corresponding date
fields (3) in each record.....how would one check the row to see which of
these fields contain non-null or non-empty values and then only display the
two fields which hold the last price reduction and the last date for this
price reduction:

ListingID | Reduced_Price_1 | Reduced_Price_1_Date | Reduced_Price_2 |
Reduced_Price_2_Date | etc
1 | 100 000 | 08/10/03 | 80
0000 | 15/10/03 | etc


....To thus display only:

80 0000 | 15/10/03

I would appreciate some help on how to do this?

Many thanks
Jason

 

[Aaron Bertrand - MVP]

ListingID | Reduced_Price_1 | Reduced_Price_1_Date | Reduced_Price_2 |

Reduced_Price_2_Date | etc

Ugh, does your schema really look like this? Why does it go horizontal like
that? Does it go all the way across to infinite, or is your table limited
to

You really should study some texts on normalization. How I would construct
this schema is:

CREATE TABLE Listings
(
ListingID INT PRIMARY KEY,
ListingName VARCHAR(32),
OriginalPrice DECIMAL(19,2)
)

CREATE TABLE ListingPriceChanges
(
ListingID INT FOREIGN KEY REFERENCES Listings(ListingID),
NewPrice DECIMAL(19,2),
ChangeDate SMALLDATETIME
)

Then you could simply say:

SELECT TOP 1 NewPrice, ChangeDate
FROM ListingPriceChanges
WHERE ListingID = 1
ORDER BY ChangeDate DESC

I would appreciate some help on how to do this?

With your current schema, it's not going to be pretty at all. In fact I
would bet that a redesign would be easier, and would lead to more
scalability and better performance in the future. (You might also consider
using column names that aren't a PITA to type.)

You could try to "pivot" the information, e.g.

CREATE TABLE #tmp (rPrice DECIMAL(19,2), rDate SMALLDATETIME)
INSERT #tmp
SELECT Reduced_Price_1, Reduced_Price_1_Date FROM Listings WHERE
ListingID = 1
INSERT #tmp
SELECT Reduced_Price_2, Reduced_Price_2_Date FROM Listings WHERE
ListingID = 2
INSERT #tmp
SELECT Reduced_Price_3, Reduced_Price_3_Date FROM Listings WHERE
ListingID = 3
....

SELECT TOP 1 rPrice, rDate FROM #tmp ORDER BY rDate DESC


Note that I'm assuming you're using SQL Server. Please be sure to specify
the database and version you are working with in the future; this will help
people avoid wasting time pursuing the wrong solution.

 

[jason]

Sorry Aarron - neglected to say this is an Access 2000 application...Can I
use your CREATE queries in this fashion


Your first CREATE statment mirrors my existing Listings Primary table....

I did originally attempt to use an additional CHANGE table to store the
changes but found that later reporting queries were complicated by
duplicates due to the re-occurance of the ListingsID....BUT, I see your
final select clause overcomes this by using TOP to get the final one by date
and price reduction

Could you possibly help me to get these queries to work in Access - this is
my first attempt at using a CREATE QUERY and I seem to be picking up syntax
errors with the second query: CREATE TABLE ListingPriceChanges...

Thanks for getting me on the right track!
-Jason

 

[jason]

ok - done! The only problem is that I created a query based on the Select
top 1 ... which only returns ONE record rather than ALL records with the
most recently entered date to avoid duplicates:

PARAMETERS LID long;
SELECT TOP 1 ListingsID, NewPrice, ChangeDate
FROM tblListingsPriceChanges
WHERE (([LID] Is Null Or [tblListingsPriceChanges].[ListingsID]=[LID]))
ORDER BY ChangeDate DESC;

.....If I select * ALL then I will get duplicates in my query which I do not
want - so I am back to square one.

- Jason