This discussion is archived
5 Replies Latest reply: Feb 27, 2013 4:00 AM by Purvesh K RSS

Query Help

690004 Newbie
Currently Being Moderated
HI ALL,

i need a query help.have given the descrption below

create table test_type (claimtype varchar(200),date1 date,claimid number);

INSERT INTO test_type VALUES ('ORIG','26-feb-2013',100);

INSERT INTO test_type VALUES ('ORIG','26-feb-2012',100);

INSERT INTO test_type VALUES ('ADJ','26-feb-2013',101);
INSERT INTO test_type VALUES ('ORIG','26-JAN-2013',101);

INSERT INTO test_type VALUES ('ADJ','26-feb-2013',102);
INSERT INTO test_type VALUES ('ADJ','26-feb-2012',102);
INSERT INTO test_type VALUES ('ADJ','26-JAN-2013',102);

select * FROM test_type;

SELECT * FROM test_type WHERE claimid=100;

This should return max date and claimtype should be ORIG;

SELECT * FROM test_type WHERE claimid=101;

this should also return max date for claim type ORIG

SELECT * FROM test_type WHERE claimid=102;

for this id there are only ADJ there are no originals.

Can any one helpme in getting this query,by passing claim id i have to get max date.

if we have both claimtypes or only ORIG then we should get max date from ORIG.

If we dont have ORIG then we should get max date from ADJ

Thanks in Advance
  • 1. Re: Query Help
    moreajays Pro
    Currently Being Moderated
    Hi,

    Can you try this using max function ,
    SELECT max(date1) FROM test_type WHERE claimid=100;
    SELECT max(date1) FROM test_type WHERE claimid=101;
    SELECT max(date1) FROM test_type WHERE claimid=102;
    Thanks,
    Ajay More
    http://www.moreajays.com
  • 2. Re: Query Help
    Purvesh K Guru
    Currently Being Moderated
    One way of doing it:
    select claimtype, date1
      from (
    select claimtype, date1, row_number() over (partition by claimtype order by date1 desc) rn
      from test_type
     where claimid = &user_input
     order by claimtype desc
      ) a
     where rownum = 1;
    PS:- Never use Date columns without a TO_Date format. It relies on NLS_DATE settings and is likely to fail. hence, always associate date with an appropriate TO_DATE(date_string, date_format).
  • 3. Re: Query Help
    Girish Sharma Guru
    Currently Being Moderated
    Purvesh,

    At the moment, I am testing your query in SQL Server 2005 on my testing home PC by running your query with little changes (as per sql server) :

    I just added a new row like :

    INSERT INTO test_type VALUES ('ORIG','26-JAN-2012',102)

    select top 1 claimtype, date1
    from (
    select claimtype, date1, row_number() over (partition by claimtype order by date1 desc) as rn
    from test_type
    where claimid = 101
    -- order by claimtype desc This is just comment line
    ) a

    I am saying for claimid=101 and it is giving me ADJ,26-feb-2013 and If I says claimid=102 then it is giving ADJ,26-feb-2013; while I think OP is saying :
    if we have both claimtypes or only ORIG then we should get max date from ORIG.
    So, I think OP is asking if there is no row (in the query itself) for claimtype ORIG then only it should give max(date1) from claimtype ADJ; otherwise query should return max(date1) of claimtype ORIG. I am not sure about Oracle (because at this moment, I don't Oracle db on this PC) though; let us see what OP says!

    Regards
    Girish Sharma
  • 4. Re: Query Help
    Girish Sharma Guru
    Currently Being Moderated
    Is this answers your question? In this Oracle table I added one more more just for testing purpose :

    INSERT INTO test_type VALUES ('ORIG','26-JAN-2012',102);
    select
    case when nvl(a.cnt,0)=0 then b.claimtype else a.claimtype end claimtype,
    case when nvl(a.cnt,0)=0 then b.md else a.md end date1,
    case when nvl(a.cnt,0)=0 then b.claimid else a.claimid end claimid
    from
    (select
    max(claimtype) claimtype,
    sum(case when claimtype='ORIG' then 1 else 0 end) cnt,
    max(date1) md,
    max(claimid) claimid
    from test_type
    where claimtype='ORIG' and claimid=101) a,
    (
    select
    max(claimtype) claimtype,
    count(*) cnt,
    max(date1) md,
    max(claimid) claimid
    from test_type
    where claimid=101) b
    /
    
    CLAIMTYPE            DATE1        CLAIMID
    -------------------- --------- ----------
    ORIG                 26-JAN-13        101
    
    select
    case when nvl(a.cnt,0)=0 then b.claimtype else a.claimtype end claimtype,
    case when nvl(a.cnt,0)=0 then b.md else a.md end date1,
    case when nvl(a.cnt,0)=0 then b.claimid else a.claimid end claimid
    from
    (select
    max(claimtype) claimtype,
    sum(case when claimtype='ORIG' then 1 else 0 end) cnt,
    max(date1) md,
    max(claimid) claimid
    from test_type
    where claimtype='ORIG' and claimid=102) a,
    (
    select
    max(claimtype) claimtype,
    count(*) cnt,
    max(date1) md,
    max(claimid) claimid
    from test_type
    where claimid=102) b
    /
    
    CLAIMTYPE            DATE1        CLAIMID
    -------------------- --------- ----------
    ORIG                 26-JAN-12        102
    Regards
    Girish Sharma

    Edited by: Girish Sharma on Feb 27, 2013 11:21 AM
    NVL added.
  • 5. Re: Query Help
    Purvesh K Guru
    Currently Being Moderated
    Thanks for taking time to visit my solution.

    Sorry to come late on this.

    For Claim ID as 102, you got result as 26-Jan-2012 and ADJ, even when an entry existed for ORIG, because the Order By clause was commented.

    Order By clause is used to Sort the result set in Descending order, thus guarantee that if ORIG is present, the it will always come first.

    Here is again a simple version of same query:
    select claimtype, date1
      from (
    select claimtype, date1--, row_number() over (partition by claimtype order by date1 desc) rn
      from test_type
     where claimid = &user_input
     order by claimtype desc, date1 desc
      ) a
     where rownum = 1;
    Analytic function is not required (surprisingly it wasn't even used earlier, I just made a note of it :) ).

    Easiest way to achieve this is to sort the data by Claim Type in descending order and if Similar claims exists i.e. same date, then add a sort on it too.

    I hope I have not missed any scenario.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points