#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0

    Question Issue pulling data from table


    I am using MSSQL server 2008 r2 and I am having trouble pulling data from a inventory price history table. The price history table is laid out as follows. Store # ITem # date price changed price
    The store # and Item # form the primary key for the table. I have been trying to figure out a way to get the last two price updates to generate a query to show if the price has changed by more than 25%. My issue is how to get those last two price changes. If anyone can help me with how to separate out those last two for each store # item # combo I would greatly appreciate it
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by jdbrau03
    The store # and Item # form the primary key for the table.
    no, they don't

    if they did, you could only have one row per store# and item#, and you wouldn't be asking for the last two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0
    You are correct, thank you for catching my error. It would be the those two and the datetime feild as to when the record was changed
    Originally Posted by r937
    no, they don't

    if they did, you could only have one row per store# and item#, and you wouldn't be asking for the last two
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Code:
    SELECT store_no
         , item_no
         , date_price_changed
         , price
      FROM price_history AS t
     WHERE ( SELECT COUNT(*) 
               FROM price_history  
              WHERE store_no = t.store_no
                AND item_no = t.item_no
                AND date_price_changed > t.date_price_changed ) < 2

    Comments on this post

    • jdbrau03 agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    3
    Rep Power
    0

    Thumbs up Right on!


    That is much more straight forward and faster than what I had come up with. Thank you very much for the help and teaching me a few things as well
    Originally Posted by r937
    Code:
    SELECT store_no
         , item_no
         , date_price_changed
         , price
      FROM price_history AS t
     WHERE ( SELECT COUNT(*) 
               FROM price_history  
              WHERE store_no = t.store_no
                AND item_no = t.item_no
                AND date_price_changed > t.date_price_changed ) < 2

IMN logo majestic logo threadwatch logo seochat tools logo