August 12, 2013 at 6:05 pm
19table has following fields and data as follows
ClientNo ItemCode NewPrice PriceChangedDate
11234 205.00 8/1/2013
11234 200 7/30/2013
11234 195 7/2/2013
11234 190 6/30/2013
Whenever there is price changes, it records new price in this table.
I need to return the NewPrice for the given clientNo, ItemCode and InvoicedDate.
For e.g for InvocedDate of 7/1/2013, there may not be any records in the table with the PriceChanedDate.
However, it should retrun NewPrice of 190 as Price from 6/30 to 7/1 remained 190. So it should return 190
Any idea best way to write a function for this?
August 12, 2013 at 6:06 pm
19Table is a Typo. I meant MyTable.
August 12, 2013 at 10:03 pm
SQL_Surfer (8/12/2013)
19table has following fields and data as followsClientNo ItemCode NewPrice PriceChangedDate
11234 205.00 8/1/2013
11234 200 7/30/2013
11234 195 7/2/2013
11234 190 6/30/2013
Whenever there is price changes, it records new price in this table.
I need to return the NewPrice for the given clientNo, ItemCode and InvoicedDate.
For e.g for InvocedDate of 7/1/2013, there may not be any records in the table with the PriceChanedDate.
However, it should retrun NewPrice of 190 as Price from 6/30 to 7/1 remained 190. So it should return 190
Any idea best way to write a function for this?
Pretty sparse on details. You have been around here long enough to know we need more details than this.
Maybe it is as simple order by PriceChangedDate? Hard to know what you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2013 at 12:57 am
Based on the very vague details provided this is the in-line table valued function I would use:
CREATE FUNCTION dbo.ClientProductPrice (
@ClientNo INT,
@ItemCode INT,
@InvoiceDate DATETIME -- in SQL Server 2008 and later can be DATE
)
RETURNS TABLE
AS
RETURN(
WITH PriceRec AS (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY PriceChangedDate DESC),
NewPrice
FROM
MyTable
WHERE
ClientNo = @ClientNo AND
ItemCode = @ItemCode AND
PriceChangedDate <= @InvoiceDate
)
SELECT NewPrice FROM PriceRec WHERE rn = 1
);
No promises that this will work as I didn't create a table to do any testing.
August 13, 2013 at 12:40 pm
It works. I've tested it on SQL Server 2005 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply