March 13, 2012 at 12:34 pm
I have 2 tables and need to create function as follows
CERTAE Table Current -- 200,000 Recorde
(
Date DATETIME
value DECIMAL(18,10)
)
CERTAE Table History 90,000,000 Records
(
Date DATETIME
value DECIMAL(18,10)
)
CREATE FUNCTION Getrate (@DATE DATETIME)
RETURNS TABLE
AS
RETURN
(
SELECT
DATE,Rate
FROM Current WHERE DATE < @DATE
UNION ALL
SELECT
DATE,Rate
FROM HistoryWHERE DATE = @DATE
);
Now what i need is if I have rate in Current table I dont want to query History table it's as that will very expencive query
need fot this function is
this is centralise Database where we store rates and i call this function using OPENQUERY to retrive rate for given date.
is their a batter way to write such quries??
Thanks For help in advance
March 13, 2012 at 1:21 pm
Not totally sure what you are after but is this close?
CREATE FUNCTION Getrate (@DATE DATETIME)
RETURNS TABLE
AS
create table #Result
(
Date datetime,
Value Decimal(18,10)
)
insert #Result
SELECT
DATE,Rate
FROM Current WHERE DATE < @DATE
if @@ROWCOUNT = 0
insert #Result
SELECT
DATE,Rate
FROM History WHERE DATE = @DATE
RETURN
(
select DATE, Rate from #Result
);
By the way, you really should not use SQL reserved words (Date, Value, Current). It can be a real pain to work with and you have to wrap them with [ ] in some cases.
_______________________________________________________________
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply