3 Replies Latest reply: Jan 27, 2013 6:29 AM by odie_63 RSS

    Can we do this by SQL without going for PLSQL

    933417
      I have a requirement where we need to expire records by comparing records between two consecutive dates.
      If few records dint get carry forwarded to current date from previous date ,
      then those unseen records in current date have to be entered again under current date with type as OUT.
      Will explain using a example.
      My data set looks like as below.

      Select 'J1' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J2' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J3' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J4' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J5' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J6' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J1' ID ,2 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J2' ID ,2 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J3' ID ,2 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J4' ID ,2 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J8' ID ,3 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J9' ID ,3 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
      Select 'J10' ID ,3 DATE_INDIC,'IN' TYPE FROM DUAL

      When we minus records between DATE_INDIC 1 and 2 ,we will get IDs J5 and J6 .Those records are expired for DATE_INDIC 2.
      The way how we show those records as expired for DATE_INDIC 2 is by inserting those records again under DATE_INDIC 2 ,but with TYPE as 'OUT'.
      So ,I have to insert the below 2 records under DATE_INDIC 2 for completing the expiration process for DATE_INDIC 2.

      ID     DATE_INDIC     TYPE
      J5     2     OUT
      J6     2     OUT


      My question is how we can get the above 2 rows generated in SQL (without using PLSQL).As the comparison has to go between two consecutive dates,
      I feel only PLSQL has to be used where we can iterate among the rows and can produce above 2 rows.But just wanted to know if it can be still done using pure SQL.
      please guide me.

      Thanks in advance.
        • 1. Re: Can we do this by SQL without going for PLSQL
          Ronald Rood
          Hi,

          the correct answer is: YES (most of the time this is the case)
          If you want an explanation you are far better of in the PL/SQL Developers forum.

          Ronald.
          • 2. Re: Can we do this by SQL without going for PLSQL
            933417
            @Ronald Rood :You said YES.Do you mean the requirment can be addressed in SQL?
            IF so , can someone please pass on the code to me.I know PLSQL implementation of the problem.
            • 3. Re: Can we do this by SQL without going for PLSQL
              odie_63
              SQL> with t as (
                2  Select 'J1' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
                3  Select 'J2' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
                4  Select 'J3' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
                5  Select 'J4' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
                6  Select 'J5' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
                7  Select 'J6' ID ,1 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
                8  Select 'J1' ID ,2 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
                9  Select 'J2' ID ,2 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
               10  Select 'J3' ID ,2 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
               11  Select 'J4' ID ,2 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
               12  Select 'J8' ID ,3 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
               13  Select 'J9' ID ,3 DATE_INDIC,'IN' TYPE FROM DUAL UNION ALL
               14  Select 'J10' ID ,3 DATE_INDIC,'IN' TYPE FROM DUAL
               15  )
               16  select id, date_indic + 1 as date_indic, 'OUT' as type
               17  from (
               18   select id
               19        , date_indic
               20        , type
               21        , lead(date_indic) over(partition by id order by date_indic) as next_date_indic
               22        , max(date_indic) over() as last_date_indic
               23   from t
               24  )
               25  where ( next_date_indic is null or next_date_indic != date_indic + 1 )
               26  and date_indic < last_date_indic ;
               
              ID  DATE_INDIC TYPE
              --- ---------- ----
              J1           3 OUT
              J2           3 OUT
              J3           3 OUT
              J4           3 OUT
              J5           2 OUT
              J6           2 OUT
               
              6 rows selected