473,320 Members | 1,863 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
3 1740
> 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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Greg Brunet | last post by:
I'm writing some routines for handling dBASE files. I've got a table (DBF file) object & field object already defined, and after opening the file, I can get the field info like this: >>>...
0
by: jason | last post by:
Hi - this post overlaps with another post (Tom K was helping) but has a differnt slant and explanation which I feel warrants a new post..... If one has a series of price reduction fields (3) and...
9
by: jason | last post by:
Access 2000 I need some help interogatting a table and extracting via ASP the final field in a row which has a value. In other words, I have a maximum of 10 fields but, at the user level he may...
2
by: Shaiguy | last post by:
I have a table containing the following fields: ProjectUpdateID (PrimaryKey) ProjectID UpdateDate I would like to create a Query in Ms Access 2000 which will return me the most recent 2...
45
by: Curt Geske | last post by:
I'm suprised no one suggested a union! #include <stdio.h> union _x { long lng; char byt; } X; void main( void )
5
by: rs | last post by:
I have a table with a timestamp field which contains the date and time. ie. 9/13/2004 9:10:00 AM. I would like to split this field into 2 fields, one with just the DATE portion ie 9/13/2004 and...
5
by: klall | last post by:
Hello. I need to extract date information from a memo field entered in the following way: 01/01/2005 - 31/12/2005 01/01/2004 - 31/12/2004 01/01/2003 - 31/12/2003 01/01/1996 - 31/12/1996. The...
16
by: Schultzy | last post by:
I have two tables: The first table is called: Products it has the following fields ProdName ProdId ProdType ProdTaxes
3
by: AllyFrog | last post by:
Hello, New here - wondering if someone may be able to help me with an issue I'm having. I have a table where I am recording details of shifts worked. Each record has the date of the shift,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.