SQL Server 2000 script

25 pts.
Tags:
Conditional SQL statements
Microsoft SQL Server 2000
SQL
SQL Query
SQL Server Scripts
Hi , How can we use SQL 2000 script to call 2nd maximum date? case : Item A has few price with different effective_date, Ihow can i call an item's price with second max(effective_date). Thanks in advance.

Answer Wiki

Thanks. We'll let you know when a new response is added.

The question is a bit vague. I am assuming the question is:
– we have item records with one or more prices that change over time
– any given item record may have one or more prices
– if an item has two or more price records, how can I get the next to the last price?

If there are always two or more records for each item, the following query will return the one previous to the current price:
(assume a table with “itemno”, “price”, and “pricedate” columns)

select price
from
(
select itemno, price, pricedate,
row_number() over (order by pricedate desc) rowno
from kcctest1
where itemno = 1 — <the item number would go here>
) T
where rowno = 2

This creates an internally ordered set of results (a temporary table T) inside the parentheses, using the row_number function with the order by clause, then selects the price from row numbered 2, which would be the second most recent price.

However, if some items have only one price record, the above will fail, since there will be no record with rowno = 2. The following will work as long as there is one or more price:

select top 1 *
from
(
select top 100 price, pricedate
from
(
select itemno, price, pricedate,
row_number() over (order by pricedate desc) rowno
from kcctest1
where itemno = 1
) T
where rowno <= 2
order by pricedate
) T2

This does the same thing, except it takes the top 2 records (if 2 exist, else just the one record), reorders them by date, and then takes the “top” one, which is the older of the two.

Personally, I prefer to write stored procedures to return the value of interest. The code is a lot more readable for someone trying to maintain it later, as in:

(oracle syntax)
function RecentPrice (p_in_itemno in <tbl>.itemno%type)
return p_out_price <tbl>.price%type
vPrice <tbl>.price%type;
vPrice2 <tbl>.price%type;
cursor curPrice is select price from <tbl> where itemno = p_in_itemno order by pricedate desc;
begin
open curPrice;
fetch curPrice into vPrice;
if curPrice%notfound then
p_out_price := 0; — return 0 if no records found
else
fetch curPrice into vPrice2;
if curPrice%notfound then
p_out_price = vPrice; — only one price record, return that price
else
p_out_price = vPrice2; — 2 or more price records, return next most recent
end if;
end if;
close curPrice;
return;
end;

Changing this procedure to use a parameter to select the “nth” oldest price is then a simple exercise.

*** Ok – I was lazy and threw the Oracle code in because I can write that in my sleep. I haven’t written as much in Transact SQL, so this was a good exercise. Here is the function to return the Nth most recent price, in Transact SQL this time.

CREATE FUNCTION NthFromLastPrice ( @ItemNo as integer, @nWhich as integer )
RETURNS float

— given 1..n price records for a given ItemNo, return the Nth most recent
— price, based on the nWhich value
— if nWhich = 1 return the most recent price, else iterate until
— we run out of records (return the last valid one) or the counter runs out
— if nWhich = 0, or no records found, return 0.00

BEGIN
DECLARE @return_price float
DECLARE @temp_price float
DECLARE @iCtr integer

DECLARE price_cursor CURSOR FOR
SELECT price FROM kcctest1 with (nolock)
WHERE ItemNo = @ItemNo
ORDER BY pricedate desc

set @iCtr = @nWhich

set @return_price = 0.0

if @iCtr > 0
begin

OPEN price_cursor

— Perform the first fetch.
FETCH NEXT FROM price_cursor into @return_price

— Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0 and @iCtr > 1
BEGIN
— This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM price_cursor into @temp_price
if @@FETCH_STATUS = 1
set @return_price = @temp_price

set @iCtr = @iCtr – 1
END

CLOSE price_cursor
DEALLOCATE price_cursor

end

RETURN @return_price

END

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Neither of those ROWNUMBER examples will work. SQL Server 2000 doesn't have that function, it's new in SQL Server 2005. This code will do the trick for you.
    SELECT TOP (1) *
    FROM (
                   SELECT TOP (2) *
                   FROM YourTable
                   ORDER BY DateEntered
    ) a
    ORDER BY DateEntered DESC
    66,360 pointsBadges:
    report
  • BrentSheets
    Moved by Moderator for Beginer2008: Let make clear my question.. I got an Item and update new price in every new quarter. Usually we'll call Max(Effect_date) to retrieve latest price. But now we'd like to call last updated price (last before latest price) instead latest price. I've tried as per yours advice yet error prompt.
    6,925 pointsBadges:
    report
  • Kccrosser
    Did you try using the function I provided? (NthFromLastPrice) You will need to change the table and column names to match your database, but I have tested it and it will return the correct price. Simply call it with the item number (or other index to the item collection) and "2" for the 2nd parameter. Once it compiles (with the right table and column names), you can easily test it with: select * from NthFromLastPrice(<itemnumber>, 2)
    3,830 pointsBadges:
    report
  • Kccrosser
    Sorry - you call it as: select dbo.NthFromLastPrice(<itemnumber>, 2) (or use your appropriate database object name) Brain freeze while typing.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following