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

  • Hi,

    I had a query running in Oracle which I'm now trying to run in SQL Server:

    The oracle Query is:

    SELECT MIN (cc.ClaCaseID) KEEP (DENSE_RANK LAST ORDER BY rl.YearOfIncident) AS latest_cla_case_no

    FROM Staging.ClaCases cc

    ,Staging.Losses rl

    WHERE cc.NameID = rl.NameID

    AND rl.YearOfIncident < TRUNC (cc.DiscoverDate)

    AND rl.LossType < 1000

    AND rl.timestamp < TRUNC (cc.DiscoverDate)

    AND cc.QuestionClassID IN (20,25)

    AND TRUNC (cc.DiscoverDate) - rl.YearOfIncident < 1095

    Group by cc.ClaCaseID

    First issue I dealt with was the TRUNC (cc.DiscoverDate) and replaced this with DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate))

    Next I tried this code in SQL Server which doesn't work

    SELECTMIN (cc.ClaCaseID) KEEP (DENSE_RANK LAST ORDER BY rl.YearOfIncident) AS latest_cla_case_no

    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 DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate)) - rl.YearOfIncident < 1095

    Group by cc.ClaCaseID

    Any Help would be greatly appreciated.

  • try below code

    SELECT MIN (cc.ClaCaseID),DENSE_RANK() over(ORDER BY rl.YearOfIncident) AS latest_cla_case_no

    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 DATEADD(dd, 0, DATEDIFF(dd, 0, cc.DiscoverDate)) - rl.YearOfIncident < 1095

    Group by cc.ClaCaseID

  • I received a date error so I fixed this error, it was the subtracting of two dates.

    I then received the following error:

    Msg 8120, Level 16, State 1, Line 1

    Column 'Staging.Losses.YearOfIncident' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    So added rl.YearOfIncident to the group by clause. However this made me realize that your fix is not what I require.

    The reason I used the dense rank was because there where multiple ClaCaseID in the Staging.Losses table and I only needed to return the latest one once the YearOfIncident had been ranked according to the criteria.

    This query was joined to another table which had unique ClaCaseID and then a NVL2 was performed ie ISNULL function was performed returning 'Y' if there was a value and 'N' if there was not.

    So seem like I'm back to the drawing board....

    SELECT MIN (cc.ClaCaseID) KEEP (DENSE_RANK LAST ORDER BY rl.YearOfIncident) AS latest_cla_case_no

    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

  • mic.con87 (12/7/2011)


    So seem like I'm back to the drawing board....

    SELECT MIN (cc.ClaCaseID) KEEP (DENSE_RANK LAST ORDER BY rl.YearOfIncident) AS latest_cla_case_no

    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

    Try this:

    SELECT MIN (ClaCaseID)

    FROM (

    SELECT cc.ClaCaseID, RID= DENSE_RANK() OVER (ORDER BY rl.YearOfIncident DESC) AS latest_cla_case_no

    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) tmp

    WHERE RID = 1

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I tried your code and still no luck:

    Error message I receive is:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'ClaCaseID'.

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword 'AS'

  • mic.con87 (12/7/2011)


    I tried your code and still no luck:

    Error message I receive is:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'ClaCaseID'.

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword 'AS'

    I went a bit too fast.

    SELECT MIN (ClaCaseID)

    FROM (

    SELECT cc.ClaCaseID, RID= DENSE_RANK() OVER (ORDER BY rl.YearOfIncident DESC) 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) tmp

    WHERE RID = 1

    Although you should be able to debug code like that yourself. The AS error was easy to find, I accidentally assigned two aliased to the DENSE_RANK.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • The code works but it doesn't return the individual ClaCaseID's, rather a single value. I tried to add a group by clause but that didn't help, neither did adding a partition by clause before the order by clause. Any suggestions?

  • For the date truncation, I prefer one of these two conversions:

    declare @date datetime = getdate() ;

    select @date as [ThisIsNow]

    , convert(date, @date) [asDate]

    , convert(datetime, (convert(date, @date))) as [asDateTime] ;

    I'm not familiar with Oracle, but is this what you're looking for?

    select min(cc.ClaCaseID) over (order by rl.YearOfIncident ) as latest_cla_case_no

    _____________________________________________________
    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

  • mic.con87 (12/7/2011)


    The code works but it doesn't return the individual ClaCaseID's, rather a single value. I tried to add a group by clause but that didn't help, neither did adding a partition by clause before the order by clause. Any suggestions?

    Ah dammit. I thought you were using KEEP FIRST, that's why I added the WHERE RID = 1 clause so that you get only one result. If you delete that WHERE clause and add the original GROUP BY in the inner query, you should get your result.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I'm sorry to be such a pain, I tried this:

    SELECT MIN (ClaCaseID)

    FROM (

    SELECT cc.ClaCaseID, RID= DENSE_RANK() OVER (ORDER BY rl.YearOfIncident DESC)

    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, rl.YearOfIncident) tmp

    and it still returns a single result, in fact the first ClaCaseID that is listed in the table. I've never come across the RID, maybe that has something to do with it? I did try take this out but then the tmp alias is invalid.

  • Ah yes, you are still using the MIN aggregate, so you will of course get only one result.

    I should drink more coffee.

    I just noticed you are using in fact "MIN (cc.ClaCaseID) KEEP (DENSE_RANK LAST ORDER BY rl.YearOfIncident)" in your Oracle query.

    Shouldn't that return only one result, as LAST specifies that you only want the last item of the sequence?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I'm not sure what the expected result is, maybe you can provide an example?

    In any case, following your remarks, I think that, instead of the order by, a partition by is needed in my example. I've also added a row_number() in case you need that:

    select rl.YearOfIncident

    , min(cc.ClaCaseID) over (partition by rl.YearOfIncident) as latest_cla_case_no

    , row_number() over (partition by rl.YearOfIncident, order by cc.ClaCaseID) as [row_no]

    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

    Does that help?

    _____________________________________________________
    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

  • Basically the LAST looks for the latest YearOfIncident once it has been ranked and keeps that record. In Oracle you need to keep the min. The query may look at several ClaCaseID each with different YearOfIncident's. It will then rank them according to YearOfIncident and keep the latest.

    For example:

    YearOfIncident ClaCaseID

    2007-11-01 1665

    2007-12-01 1666

    2005-02-01 1666

    2005-02-01 1667

    2007-12-01 1667

    Should become:

    YearOfIncident ClaCaseID

    2007-11-01 1665

    2007-12-01 1666

    2007-12-01 1667

    I actually received help on an oracle forum for this query.

    Here is the original link, maybe that will halep you to understand my problem and perhaps even suggest a better method.

    https://forums.oracle.com/forums/thread.jspa?messageID=9954205&#9954205

  • Your example seems to be rather simple and can be solved like this:

    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

    order by cc.ClaCaseID

    Or am I missing something?

    _____________________________________________________
    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

  • Thank-you for your responses everyone. The last post made me realize that perhaps I am over complicating this. I am still curious to see how my original question could be made to work in SQL Server. I'm not sure if it is the optimal way to perform what I require. If you look at my original post on an oracle forum https://forums.oracle.com/forums/thread.jspa?messageID=9954205&#9954205

    You will see that I pretty much used the solution that Erwin Dockx suggested. I had performance issues when joining onto another table and that is why I was seeking a better method.

    select z.ClaCaseID from (

    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

    )z

    which ultimately would become

    ,CASE WHEN (select z.ClaCaseID from (

    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

    )z

    where z.ClaCaseID = ccx.ClaCaseID)IS NOT NULL THEN 'Y' ELSE 'N')

    Thanks again for your contributions!!!

Viewing 15 posts - 1 through 15 (of 16 total)

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