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

Discussion in 'ASP General' started by jason, Aug 14, 2003.

  1. jason

    jason Guest

    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
     
    jason, Aug 14, 2003
    #1
    1. Advertising

  2. > 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.
     
    Aaron Bertrand - MVP, Aug 14, 2003
    #2
    1. Advertising

  3. jason

    jason Guest

    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" <> wrote in message
    news:...
    > > 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, Aug 14, 2003
    #3
  4. jason

    jason Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    > > news:...
    > >>> 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, Aug 14, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Nova's Taylor
    Replies:
    7
    Views:
    689
    Peter Hansen
    Jun 19, 2004
  2. Richard Heathfield

    Re: Most recent C and C++ standards documents ?

    Richard Heathfield, May 20, 2006, in forum: C Programming
    Replies:
    2
    Views:
    284
    Robert Gamble
    May 20, 2006
  3. Nandhu
    Replies:
    0
    Views:
    451
    Nandhu
    Dec 4, 2007
  4. jason
    Replies:
    0
    Views:
    112
    jason
    Aug 14, 2003
  5. Martina
    Replies:
    2
    Views:
    140
    Anno Siegel
    Sep 10, 2005
Loading...

Share This Page