Issue pulling data from table

Dev Shed Forums Sponsor:

January 14th, 2013, 01:44 PM

jdbrau03

Registered User

Join Date: Jan 2013

Posts: 3

Time spent in forums: 24 m 32 sec
Reputation Power: 0

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

January 14th, 2013, 05:58 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,376

Time spent in forums: 3 Months 1 Week 2 Days 7 h 35 m 43 sec
Reputation Power: 4140

Quote:

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

January 15th, 2013, 08:58 AM

jdbrau03

Registered User

Join Date: Jan 2013

Posts: 3

Time spent in forums: 24 m 32 sec
Reputation 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

Quote:

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

January 18th, 2013, 05:21 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,376

Time spent in forums: 3 Months 1 Week 2 Days 7 h 35 m 43 sec
Reputation Power: 4140

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!

January 18th, 2013, 08:12 AM

jdbrau03

Registered User

Join Date: Jan 2013

Posts: 3

Time spent in forums: 24 m 32 sec
Reputation Power: 0

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

Quote:

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

Issue pulling data from table

Developer Shed Advertisers and Affiliates