By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,995 Members | 1,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,995 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Jul 19 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
> 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.
Jul 19 '05 #2

P: n/a
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

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:es**************@TK2MSFTNGP10.phx.gbl...
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.

Jul 19 '05 #3

P: n/a
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
"Bob Barrows" <re*******@yahoo.com> wrote in message
news:un**************@TK2MSFTNGP09.phx.gbl...
Don't worry about the CREATE QUERY statements. Id you prefer using the table design tool in Access, go ahead and do it, using the CREATE TABLE statements as a guideline.

jason wrote:
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

"Aaron Bertrand - MVP" <aa***@TRASHaspfaq.com> wrote in message
news:es**************@TK2MSFTNGP10.phx.gbl...
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.


Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.