Post reply

KEEP (DENSE_RANK LAST ORDER BY) from Oracle to SQL Server

  • December 8, 2011 at 2:06 am

    #1418522

    Happy to help mic.con87

    Seeing what you try to do, I think you can create a view that returns the result of your subquery. Then use that view in an outer join and use it's ClaCaseId in the case of your select, like so:

    create view vGetMaxYearOfIncidentForClaCaseId

    as

    select max(rl.YearOfIncident) as YearOfIncident

    , cc.ClaCaseID

    from Staging.ClaCases cc

    , Staging.Losses rl

    where cc.NameID = rl.NameID

    and rl.YearOfIncident < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))

    and rl.LossType < 1000

    and rl.RecordCreated < dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate))

    and cc.QuestionClassID in (20, 25)

    and datediff(day, dateadd(dd, 0, datediff(dd, 0, cc.DiscoverDate)), rl.YearOfIncident) < 1095

    group by cc.ClaCaseID

    go

    select case when z.ClaCaseID is not null then 'Y' else 'N' end

    from [YourTable] as ccx

    left outer join vGetMaxYearOfIncidentForClaCaseId as z on z.ClaCaseID = ccx.ClaCaseID

    _____________________________________________________
    Do not go past the mark you aimed for, but learn when to stop.

    You can find me on LinkedIn.
    I support The Programmer's Bill of Rights.

    MCITP, MCDBA, MCSD

  • December 8, 2011 at 2:18 am

    #1418529

    Brilliant!!! Great idea, thanks for the suggestion 🙂

Viewing 2 posts - 16 through 16 (of 16 total)

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