Post reply

Replace Row_Number() to make the query SQL2000 Compatible

  • October 14, 2010 at 10:32 am

    #230350

    Select A.CLI_ID, CLI_SEQ, CL_ID, A.CLR_ID, UPPER(A.CL_Status) CL_Status, A.S_Code

    , A.START_DATE, A.END_DATE, A.UPDATED_AT, A.CLSH_ID

    From (

    Select ROW_NUMBER() OVER (PARTITION BY B.CLI_ID ORDER BY B.CLI_ID, START_DATE, CLSH_ID) AS CLI_SEQ

    , CL_ID, b.CLR_ID, c.CL_Status, S_Code, c.START_DATE, c.END_DATE, C.UPDATED_AT

    , c.CLSH_ID

    From table2 a (nolock)

    inner join (

    Select aa.CLR_ID, aa.CLI_ID

    From table1 aa (nolock)

    inner join (

    Select CLR_ID, min(item_id) min_item_id

    From table1 (nolock)

    Group by CLR_ID

    ) bb on aa.CLR_ID = bb.CLR_ID

    ) b on a.CLR_ID = b.CLR_ID

    inner join table3 c (nolock) on b.claim_item_id = c.claim_item_id

    ) A

    Where S_Code = 'CLOSED'

    AND CL_Status = 'CLOSED'

    AND CLI_SEQ = 1

    There is an exception to every rule, except this one...

  • October 14, 2010 at 10:56 am

    #1237053

    You'll either need to use the quirky update running totals routine (search for "running totals" on this site), and accept the risks/liabities that has, or use a cursor, to make it work in SQL 2000. I'd use a cursor. Much as I dislike them, a fast-forward, local cursor will be the most reliable, fastest way to do this. (Quirky update is faster, but less reliable.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

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