Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

function help Expand / Collapse
Author
Message
Posted Monday, August 12, 2013 6:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
19table has following fields and data as follows

ClientNo ItemCode NewPrice PriceChangedDate
1 1234 205.00 8/1/2013
1 1234 200 7/30/2013
1 1234 195 7/2/2013
1 1234 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?
Post #1483515
Posted Monday, August 12, 2013 6:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
19Table is a Typo. I meant MyTable.
Post #1483516
Posted Monday, August 12, 2013 10:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:35 PM
Points: 13,253, Visits: 12,087
SQL_Surfer (8/12/2013)
19table has following fields and data as follows

ClientNo ItemCode NewPrice PriceChangedDate
1 1234 205.00 8/1/2013
1 1234 200 7/30/2013
1 1234 195 7/2/2013
1 1234 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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1483549
Posted Tuesday, August 13, 2013 12:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:09 PM
Points: 23,218, Visits: 31,905
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.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1483586
Posted Tuesday, August 13, 2013 12:40 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:41 PM
Points: 522, Visits: 234
It works. I've tested it on SQL Server 2005
Post #1483902
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse