Change a specific date to the most recent date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Change a specific date to the most recent date

Author

Topic

jaimealvarez
Starting Member

31 Posts

Posted - 2012-10-22 : 16:01:04

Hi,
I have a CASE line which works well but I need to do a change. The line is currently looking at an effective date of 1/1/2012, I want to change that so that it looks at the most recent date. For example, if there are two effective dates one for 1/1/2012 and the other for 6/1/2012 it will choose 6/1/2012.

The code is:

WHEN matter.mrtcode = 10 then (select timerate.tkrt10 FROM son_db.dbo.timerate timerate WHERE (timerate.tkinit=timecard.ttk) AND (timerate.tkeffdate={ts '2012-01-01 00:00:00'}) AND (timerate.tkrtcur='USD'))

Any help would be appreciate it. Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-22 : 16:11:14

Do you want just the MAX date or is there some special logic about a date for 1/1/2012 existing also? What is 1/1/2012 doesn't exist?

jaimealvarez
Starting Member

31 Posts

Posted - 2012-10-22 : 16:18:50

Max date would work great, 1/1/12 doesn't need to exist. I'm just not sure how to use the MAX in that line.
Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-22 : 17:07:32

Without sample data and expected output, here is a guess:

WHEN matter.mrtcode = 10 
    THEN 
    (
        select MAX(timerate.tkrt10)
        FROM son_db.dbo.timerate timerate 
        WHERE 
            timerate.tkinit = timecard.ttk
            AND timerate.tkrtcur='USD'
    )
ELSE
    ...

It might be faster to join to a derived table rather than use a corrilated sub-query. For example:

SELECT
    ...
FROM
    timecard
INER JOIN
    (
        SELECT 
            tkinit, 
            MAX(tkrt10) AS Maxtkrt10
        FROM 
            son_db.dbo.timerate 
        WHERE 
            timerate.tkrtcur='USD'
        GROUP BY
            tkinit
    ) AS TimeRate
    ON timecard.tkinit = TimeRate.tkinit
...

jaimealvarez
Starting Member

31 Posts

Posted - 2012-10-23 : 09:56:54

Thank you, I used the derived table and it worked!. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources