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

    Query Help

    690004
      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
          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
            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
              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
                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
                  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.