Horace's Friday Code Quiz
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Horace's Friday Code Quiz

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    Horace's Friday Code Quiz

    As you know, I'm not very good at this coding lark (but I'm trying)

    Here's the request..............

    We want the most recent (latest date_created or, where it isn't null, the date_updated) gross_family_income for each hurn on the W_Person table. W_Lifestyle only has purn on it. Certain lifestyle values are not acceptable (the where clauses).

    Here's my code (which doesn't work!!).................

    SELECT w_person.hurn,
    CASE WHEN w_lifestyle.date_updated IS NULL
    THEN w_lifestyle.date_created
    ELSE w_lifestyle.date_updated END highest_date,
    w_lifestyle.gross_family_income,
    row_number() over
    (PARTITION BY hurn ORDER BY highest_date desc) rn
    from w_lifestyle, w_person
    where w_lifestyle.purn = w_person.purn
    and w_lifestyle.gross_family_income is not null
    and w_lifestyle.gross_family_income <> 'Z'
    and w_lifestyle.gross_family_income <> 'X'
    and w_lifestyle.gross_family_income <> ' '
    order by highest_date
    where rn = 1

    (If you take out the rownum bit it flows through but gives me lots of rows when what I want is one.)

    Anyone bored/competent enough to help with this will be awarded a free pint....

    Much obliged.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    You need another SELECT wrapped round it.

    (BTW: NVL(w_lifestyle.date_updated, w_lifestyle.date_created) is less typing than the CASE).

    SELECT * FROM (
    SELECT w_person.hurn,
    CASE WHEN w_lifestyle.date_updated IS NULL
    THEN w_lifestyle.date_created
    ELSE w_lifestyle.date_updated END highest_date,
    w_lifestyle.gross_family_income,
    row_number() over
    (PARTITION BY hurn ORDER BY highest_date desc) rn
    from w_lifestyle, w_person
    where w_lifestyle.purn = w_person.purn
    and w_lifestyle.gross_family_income is not null
    and w_lifestyle.gross_family_income <> 'Z'
    and w_lifestyle.gross_family_income <> 'X'
    and w_lifestyle.gross_family_income <> ' '
    ) where rn = 1

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Haven't had time to test this yet, but thanks very much for your invaluable input.

    So close..... Must be improving

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width