Calculating Expiry Date when you only have an Effective Date

Question

Post reply

Calculating Expiry Date when you only have an Effective Date

cls

SSC-Addicted

Points: 441
More actions
October 18, 2006 at 1:13 pm

#115143

I'm trying to create a script which will look at a table with the following fields:
Rate, Effective_Date, Currency_Code
What I'm trying to determine is the expiry date based on the effective dates of each RATE. (Expiry date would be the next Effective date minus one day, but I'm not sure how to script this).
I'm joining this table to another transaction table with the condition the transaction date must be between the Effective_Date and EXPIRY_DATE of the RATE. This is the only way I know of matching the correct RATE to each transaction.
Does anyone have any suggestions?
I'm hoping this is clear, if not please let me know.

Thanks
cls

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

You must LEFT join table to itself on JT.Effective_Date > T.Effective_Date

_____________
Code for TallyGenerator

Greg Goss SSCrazy Points: 2160 More actions · Answer 2

If you are dealing with a massive table, there may be a cleaner way to do this, but off-hand this will do the trick. I am not sure of your constraints, but if I could, I would add the discontinue date column to the table and then run an update script whenever the table is refreshed.

--create temp table and populate values

declare @table table (rate int, effDate smalldatetime, currencyCode varchar(5))

insert @table (rate, effDate, currencyCode)

values ('1','10/1/2006','USD')

insert @table (rate, effDate, currencyCode)

values ('2','11/1/2006','USD')

insert @table (rate, effDate, currencyCode)

values ('3','12/1/2006','EUR')

insert @table (rate, effDate, currencyCode)

values ('4','1/1/2007','EUR')

--select statement to determine discontinue date

select a.*, (select top 1 b.effDate from @table b where b.effDate > a.effDate and a.currencyCode = b.currencyCode) as 'discDate'

from @table a

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 3

Slightly modified version of Greg's post.

Create a view with the same logic as Greg's post, then u can use the view in any of your query.

Create view VW_Rate_Effectivity

as

select rate,EffDate,CurrencyCode,

/* Get the Next Record and subtract 1 second. If No next record found use 1/1/3000 */

isnull((Select top 1 DateAdd(ss,-1,Cast(b.effDate as datetime)) from Rate b where b.effDate > a.effDate and a.currencyCode = b.currencyCode),Cast('1/1/3000' as datetime)) as EffEndDate

from Rate a

Hope this helps.

Thanks

Sreejith

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Both solutions are wrong.

If you use TOP you must specify ORDER BY. Otherwise your result depends on occasional physical order of rows in table.

Try this:

--create temp table and populate values

create table #table (rate int, effDate smalldatetime, currencyCode varchar(5))

create index effDate on #table(effDate desc)