473,703 Members | 2,340 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1754
> 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 ListingPriceCha nges
(
ListingID INT FOREIGN KEY REFERENCES Listings(Listin gID),
NewPrice DECIMAL(19,2),
ChangeDate SMALLDATETIME
)

Then you could simply say:

SELECT TOP 1 NewPrice, ChangeDate
FROM ListingPriceCha nges
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...C an 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....B UT, 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 ListingPriceCha nges...

Thanks for getting me on the right track!
-Jason

"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote in message
news:es******** ******@TK2MSFTN GP10.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 ListingPriceCha nges
(
ListingID INT FOREIGN KEY REFERENCES Listings(Listin gID),
NewPrice DECIMAL(19,2),
ChangeDate SMALLDATETIME
)

Then you could simply say:

SELECT TOP 1 NewPrice, ChangeDate
FROM ListingPriceCha nges
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 tblListingsPric eChanges
WHERE (([LID] Is Null Or [tblListingsPric eChanges].[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*******@yaho o.com> wrote in message
news:un******** ******@TK2MSFTN GP09.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...C an 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....B UT, 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
ListingPriceCha nges...

Thanks for getting me on the right track!
-Jason

"Aaron Bertrand - MVP" <aa***@TRASHasp faq.com> wrote in message
news:es******** ******@TK2MSFTN GP10.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 ListingPriceCha nges
(
ListingID INT FOREIGN KEY REFERENCES Listings(Listin gID),
NewPrice DECIMAL(19,2),
ChangeDate SMALLDATETIME
)

Then you could simply say:

SELECT TOP 1 NewPrice, ChangeDate
FROM ListingPriceCha nges
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
3834
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: >>> tbl.Fields() What I would like to do is be able to extract the field names into a single, separate list. It should look like:
0
1310
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 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...
9
1958
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 he only enter values into the first four. I need to ALWAYS extract the final or last field that has a value. In the example below this would be PriceField5 which has a value of $162,000. The user stopped at this field and left the rest blank.
2
3874
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 records (and also show the above 3 fields) So suppose the table looks like this:
45
3718
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
102532
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 the other with just the TIME portion. ie 9:10:00 AM. I can make the table view display what I want by placing the same data in 3 fields and setting the display property to 'General Date',' Medium Time' and 'Short Date' but the underlying data...
5
1986
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 number of entries varies. I would welcome advice on a more elegant way of extracting all the date information (first date, second date, third date, etc and appending to another table, say tblDates) rather than the crude way I have used left
16
1921
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
4356
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, the start time, end time and other details. Most shifts are only one-offs so they can be entered individually, but every so often there is a series of them. I want to make it so that my user can enter the first date of the series, and the last date of...
0
9244
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9004
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8961
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7853
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6585
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4679
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3114
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2439
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.