7 Replies Latest reply: Nov 13, 2012 3:30 PM by 857043 RSS

    Compare Rows with Contractor ID and pick row with Min hire Date

    857043
      Hi,

      I have a scenario where i need to flag a row with Minimum Hire Date as Primary 'P' whenever there are multiple records with same contractor ID. I need an SQL for this

      For Example

      Contractor_ID Name Hire_Date End_Date Primary_Flag

      123 Tom 9/11/2011 9/11/2012

      123 Tom 9/12/2012 5/12/2012


      I Need to flag the First row as 'P'. This is an example where there are thousands of records where i need to flag. Some instances there may be 3 or 4 records for same contractor_id where i need to flag 'P' for record with Min Hire_Date


      Thanks,
      Ram
        • 1. Re: Compare Rows with Contractor ID and pick row with Min hire Date
          Frank Kulash
          Hi, Ram,

          So, for each row, you need to know the earliest hire_date for the same contractor_id. That sounds like a job for the analytic MIN function:
          SELECT     contractor_id, name, hire_date, end_date
          ,     CASE
                   WHEN  hire_date = MIN (hire_date) OVER (PARTITION BY  contractopr_id)
                   THEN  'P'
                   ELSE  NULL     -- or whatever you want
               END
          FROM     table_x
          ;
           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          If you're asking about a DML statement, such as UPDATE, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: Compare Rows with Contractor ID and pick row with Min hire Date
            AlbertoFaenza
            Hi Ram,

            how about this merge to update your table?
            MERGE INTO contractors c1
                 USING (SELECT contractor_id
                             , MIN (hire_date) OVER (PARTITION BY contractor_id) min_hire_date
                          FROM contractors) c2
                    ON (c1.contractor_id = c2.contractor_id
                        AND c1.hire_date = c2.min_hire_date)
            WHEN MATCHED
            THEN
               UPDATE SET primary_flag = 'P';
            This one will work if for the same contractor_id you don't have same MIN(hire date).

            Regards.
            Al
            • 3. Re: Compare Rows with Contractor ID and pick row with Min hire Date
              Frank Kulash
              Hi,
              Alberto Faenza wrote:
              ... This one will work if for the same contractor_id you don't have same MIN(hire date).
              If that's what OP wants, then we can use the aggregate MIN function, like this:
              MERGE INTO contractors c1
                   USING (SELECT contractor_id
                               , MIN (hire_date)     AS min_hire_date
                            FROM contractors
                         GROUP BY contractor_id
                       ) c2
                      ON (c1.contractor_id = c2.contractor_id
                          AND c1.hire_date = c2.min_hire_date)
              WHEN MATCHED
              THEN
                 UPDATE SET primary_flag = 'P';
              This way, it doesn't matter if 2 (or more) rows with the same contractor_id have the same hire_date.
              • 4. Re: Compare Rows with Contractor ID and pick row with Min hire Date
                AlbertoFaenza
                Good point Frank.

                Thanks for finding it out.

                Regards.
                Al
                • 5. Re: Compare Rows with Contractor ID and pick row with Min hire Date
                  857043
                  Thanks Frank and Alberto for the Answers as it did help me .....Sorry as i have an extension to that question on the same subject.

                  I would like to only Query the Data and use it with my ETL. In the above case when i have same contractor_ID i would like to mark row with Min Hire Date as Primary 'P' but there are some cases where rows will have same hire_date for a contractor_id then in that case i would like to mark row with max end date as Primary 'P'. Can you please let me know the SQL for that.....I am providing the create and insert statements.

                  My Database version is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

                  CREATE TABLE CONTRACTOR
                  (
                  CONTRACTOR_NUMBER NUMBER(7),
                  FULL_NAME VARCHAR2(10 BYTE),
                  JOB_TITLE VARCHAR2(42 BYTE),
                  HIRE_DATE DATE,
                  END_DATE DATE
                  )
                  /
                  Insert into CONTRACTOR
                  (CONTRACTOR_NUMBER,FULL_NAME,JOB_TITLE,HIRE_DATE, END_DATE)
                  Values
                  (1862180, 'Tom','Developer',TO_DATE('08/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
                  Insert into CONTRACTOR
                  (CONTRACTOR_NUMBER,FULL_NAME,JOB_TITLE,HIRE_DATE, END_DATE)
                  Values
                  (1862180, 'Tom','Developer',TO_DATE('12/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/30/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
                  Insert into CONTRACTOR
                  (CONTRACTOR_NUMBER,FULL_NAME,JOB_TITLE,HIRE_DATE, END_DATE)
                  Values
                  (1862181, 'Sam','Developer',TO_DATE('09/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('9/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
                  Insert into CONTRACTOR
                  (CONTRACTOR_NUMBER,FULL_NAME,JOB_TITLE,HIRE_DATE, END_DATE)
                  Values
                  (1862181, 'Sam','Developer',TO_DATE('09/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

                  /

                  Expected Query Output

                  Contractor_number,Full_Name,Job_title,Hire_Date,End_Date,Primary_Flag
                  1862180,Tom,Developer,08/01/2011,11/30/2012,'P'
                  1862180,Tom,Developer,08/01/2011,11/30/2012
                  1862181,Sam,Developer,9/01/2012,9/30/2012
                  1862181,Sam,Developer,9/01/2012,12/30/2012,'P'

                  Thanks,
                  Ram
                  • 6. Re: Compare Rows with Contractor ID and pick row with Min hire Date
                    Frank Kulash
                    Hi,

                    If you want to make sure that only 1 row per contractor is flagged as 'P', even if rows are identical, then I suggest you do something based on the analytic ROW_NUMBER function, like this:
                    SELECT       contractor_number
                    ,       full_name
                    ,       job_title
                    ,       hire_date
                    ,       end_date
                    ,       CASE
                               WHEN  1 = ROW_NUMBER () OVER ( PARTITION BY  contractor_number
                                                                    ORDER BY          hire_date
                                                  ,                end_date     DESC
                                                )
                               THEN  'P'
                           END          AS primary_flag
                    FROM       contractor
                    ORDER BY  contractor_number
                    ,            hire_date
                    ,       end_date
                    ;
                    Output:
                    CONTRACTOR
                       _NUMBER FULL_NAME  JOB_TITLE  HIRE_DATE  END_DATE   P
                    ---------- ---------- ---------- ---------- ---------- -
                       1862180 Tom        Developer  8/01/2011  11/30/2012 P
                       1862180 Tom        Developer  12/01/2012 11/30/2013
                       1862181 Sam        Developer  9/01/2012  9/30/2012
                       1862181 Sam        Developer  9/01/2012  12/30/2012
                       1862181 Sam        Developer  9/01/2012  12/30/2012 P
                    • 7. Re: Compare Rows with Contractor ID and pick row with Min hire Date
                      857043
                      Thanks Frank for the Help...Appreciate for the quick response...!