8 Replies Latest reply: Mar 6, 2009 4:45 AM by 621483 RSS

    Query Help - Trying to get single date field into Date Range

    689401
      Greetings,

      I was wondering if someone could possibly help. I am trying to get the following data in to the format below it via a query.

      id     group     sub-group     Value     date
      1     abc     def          10     01-jan-2009
      1     abc     def          10     02-jan-2009
      1     abc     def          10     03-jan-2009
      1     abc     def          10     04-jan-2009
      1     abc     def          10     05-jan-2009
      1     abc     def          10     06-jan-2009
      1     abc     def          10     07-jan-2009
      1     abc     def          10     08-jan-2009
      1     abc     def          10     09-jan-2009
      1     abc     def          20     10-jan-2009
      1     abc     def          20     11-jan-2009
      1     abc     def          20     12-jan-2009
      1     abc     def          20     13-jan-2009
      1     abc     def          20     14-jan-2009
      1     abc     def          20     15-jan-2009
      1     abc     def          10     16-jan-2009
      1     abc     def          10     17-jan-2009
      1     abc     def          10     18-jan-2009
      1     abc     def          10     19-jan-2009
      1     abc     def          20     20-jan-2009
      1     abc     def          20     21-jan-2009



      I need to query to get this output
      id     group     sub-group     value     start_date     end_date
      1     abc     def          10     01-jan-2009     09-jan-2009
      1     abc     def          20     10-jan-2009     15-jan-2009
      1     abc     def          10     16-jan-2009     19-jan-2009
      1     abc     def          20     20-jan-2009     21-jan-2009




      Any help would be appreciated!

      -Bill
        • 1. Re: Query Help - Trying to get single date field into Date Range
          alvinder
          Try this
          with t as (
          select 10 subgroup, sysdate val from dual union all
          select 10 subgroup, sysdate+1 from dual union all
          select 10 subgroup, sysdate+2 from dual union all
          select 11 subgroup, sysdate+3 from dual union all
          select 11 subgroup, sysdate+4 from dual
          )
          select subgroup,min(val) from_date,max(val) to_date
          from t
          group by subgroup
          select subgroup,min(val) from_date,max(val) to_date
          from t
          group by subgroup


          output
          11     09/MAR/09     10/MAR/09
          10     06/MAR/09     08/MAR/09


          This won't work. Didn't look at the data carefully.


          Alvinder

          Edited by: alvinder on Mar 6, 2009 12:56 PM

          Edited by: alvinder on Mar 6, 2009 12:57 PM
          • 2. Re: Query Help - Trying to get single date field into Date Range
            alvinder
            with t as (
            select 1 id , 10 val, sysdate dt from dual union all
            select 1,10 , sysdate+1 from dual union all
            select 1,10 , sysdate+2 from dual union all
            select 1,11 , sysdate+3 from dual union all
            select 1,11 , sysdate+4 from dual union all
            select 1,10 , sysdate+6 from dual union all
            select 1,10 , sysdate+7 from dual union all
            select 1,11 , sysdate+8 from dual union all
            select 1,11 , sysdate+9 from dual 
            
            )
            select id,val,min(dt)from_dt,max(dt) to_dt
            from(
            select  id,val,dt,grp
                       from t
                       model
                       partition by (id)
                      dimension by (row_number()over(order by dt )   rn)
                      measures( dt,val,lag(val)over( order by dt ) next_val, 1 grp,1 grpprec)
                      rules
                      upsert
                      iterate (1000)
                      until(presentv(val[iteration_number+2],1,0)=0)
                      (  grpprec[any]=nvl(grp[cv()-1],1),
                         grp[any]= case when nvl( next_val[cv()],val[cv()]) =val[cv()]  then                
                         grpprec[cv()] else  grpprec[cv()]+1  end)
             )            
            group by id,val ,grp
            order by grp
            output
            1     10     06/MAR/09     08/MAR/09
            1     11     09/MAR/09     10/MAR/09
            1     10     12/MAR/09     13/MAR/09
            1     11     14/MAR/09     15/MAR/09
            • 3. Re: Query Help - Trying to get single date field into Date Range
              689401
              Alvinder, I really appreciate your help on this......

              I don't know if I am having trouble because I am tired or what, but can you do that again with actual table(property_rate_charge)/field names:

              ROOM_CODE     PROPERTY_NUMBER     RATE_LEVEL     INVENTORY_DATE     CHARGE_ID     AMOUNT
              J1Q1     54     TN1     4/1/2009 0:00     52510     180
              J1Q1     54     TN1     4/2/2009 0:00     52510     180
              J1Q1     54     TN1     4/3/2009 0:00     52510     170
              J1Q1     54     TN1     4/4/2009 0:00     52510     170
              J1Q1     54     TN1     4/5/2009 0:00     52510     170
              J1Q1     54     TN1     4/6/2009 0:00     52510     170
              J1Q1     54     TN1     4/7/2009 0:00     52510     170
              J1Q1     54     TN1     4/8/2009 0:00     52510     170
              J1Q1     54     TN1     4/9/2009 0:00     52510     170
              J1Q1     54     TN1     4/10/2009 0:00     52510     170
              J1Q1     54     TN1     4/11/2009 0:00     52510     170
              J1Q1     54     TN1     4/12/2009 0:00     52510     170
              J1Q1     54     TN1     4/13/2009 0:00     52510     170
              J1Q1     54     TN1     4/14/2009 0:00     52510     170
              J1Q1     54     TN1     4/15/2009 0:00     52510     170
              J1Q1     54     TN1     4/16/2009 0:00     52510     170
              J1Q1     54     TN1     4/17/2009 0:00     52510     170
              J1Q1     54     TN1     4/18/2009 0:00     52510     170
              J1Q1     54     TN1     4/19/2009 0:00     52510     170
              J1Q1     54     TN1     4/20/2009 0:00     52510     180
              J1Q1     54     TN1     4/21/2009 0:00     52510     180
              J1Q1     54     TN1     4/22/2009 0:00     52510     180
              J1Q1     54     TN1     4/23/2009 0:00     52510     180
              J1Q1     54     TN1     4/24/2009 0:00     52510     170
              J1Q1     54     TN1     4/25/2009 0:00     52510     170
              J1Q1     54     TN1     4/26/2009 0:00     52510     180
              J1Q1     54     TN1     4/27/2009 0:00     52510     180
              J1Q1     54     TN1     4/28/2009 0:00     52510     180
              J1Q1     54     TN1     4/29/2009 0:00     52510     180
              J1Q1     54     TN1     4/30/2009 0:00     52510     180


              I need :

              PROPERTY_NUMBER ROOM_CODE RATE_LEVEL AMOUNT START_DATE END_DATE


              Thank You!
              • 4. Re: Query Help - Trying to get single date field into Date Range
                689401
                Alvinder,


                I am Receiving Error 907 Missing Right Parnethesis on Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production yet on Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi the sample works.

                Could you advise how I can modify for 9 as that is where the table is located?

                (It wasn't my tiredness after all.....)

                Thanks,
                -Bill
                • 5. Re: Query Help - Trying to get single date field into Date Range
                  Christian Balz
                  Hello Bill,
                  WITH TABLE_A AS (SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')    INVENTORY_DATE, '52510' CHARGE_ID, 180 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+1  INVENTORY_DATE, '52510' CHARGE_ID, 180 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+2  INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+3  INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+4  INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+5  INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+6  INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+7  INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+8  INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+9  INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+10 INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+11 INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+12 INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+13 INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+14 INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+15 INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+16 INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+17 INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+18 INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+19 INVENTORY_DATE, '52510' CHARGE_ID, 180 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+20 INVENTORY_DATE, '52510' CHARGE_ID, 180 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+21 INVENTORY_DATE, '52510' CHARGE_ID, 180 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+22 INVENTORY_DATE, '52510' CHARGE_ID, 180 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+23 INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+24 INVENTORY_DATE, '52510' CHARGE_ID, 170 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+25 INVENTORY_DATE, '52510' CHARGE_ID, 180 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+26 INVENTORY_DATE, '52510' CHARGE_ID, 180 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+27 INVENTORY_DATE, '52510' CHARGE_ID, 180 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+28 INVENTORY_DATE, '52510' CHARGE_ID, 180 AMOUNT FROM DUAL UNION ALL
                                   SELECT 'J1Q1' ROOM_CODE, 54 PROPERTY_NUMBER, 'TN1' RATE_LEVEL, trunc(sysdate,'MM')+29 INVENTORY_DATE, '52510' CHARGE_ID, 180 AMOUNT FROM DUAL)
                  SELECT PROPERTY_NUMBER, ROOM_CODE, RATE_LEVEL, AMOUNT, TO_CHAR(MIN(INVENTORY_DATE),'DD-MON-YYYY') START_DATE, TO_CHAR(MAX(INVENTORY_DATE),'DD-MON-YYYY') END_DATE
                    FROM TABLE_A
                   WHERE INVENTORY_DATE <= trunc(LAST_DAY(sysdate),'dd') - 16
                   GROUP BY PROPERTY_NUMBER, ROOM_CODE, RATE_LEVEL, AMOUNT
                  UNION ALL
                  SELECT PROPERTY_NUMBER, ROOM_CODE, RATE_LEVEL, AMOUNT, TO_CHAR(MIN(INVENTORY_DATE),'DD-MON-YYYY') START_DATE, TO_CHAR(MAX(INVENTORY_DATE),'DD-MON-YYYY') END_DATE
                    FROM TABLE_A
                   WHERE INVENTORY_DATE >= trunc(LAST_DAY(sysdate),'dd') - 15
                  GROUP BY PROPERTY_NUMBER, ROOM_CODE, RATE_LEVEL, AMOUNT
                  ORDER BY START_DATE, AMOUNT
                  output
                  PROPERTY_NUMBER ROOM_CODE RATE_LEVEL     AMOUNT START_DATE           END_DATE
                  --------------- --------- ---------- ---------- -------------------- --------------------
                               54 J1Q1      TN1               180 01-MAR-2009          02-MAR-2009
                               54 J1Q1      TN1               170 03-MAR-2009          15-MAR-2009
                               54 J1Q1      TN1               170 16-MAR-2009          25-MAR-2009
                               54 J1Q1      TN1               180 20-MAR-2009          30-MAR-2009
                  regards

                  Christian Balz

                  Edited by: Christian Balz on 05/03/2009 20:00
                  • 6. Re: Query Help - Trying to get single date field into Date Range
                    566473
                    select property_number, room_code, rate_level, charge_id, amount, 
                           min(inventory_date) as start_date, max(inventory_date) as end_date
                      from (
                             select property_number, room_code, rate_level, 
                                    amount, charge_id, inventory_date,
                                    row_number() over(partition by property_number, room_code, 
                                                                   rate_level, charge_id
                                                          order by inventory_date) -
                                    row_number() over(partition by property_number, room_code, 
                                                                   rate_level, charge_id, amount
                                                 order by inventory_date) as grp_id
                               from property_rate_charge
                           )
                     group by property_number, room_code, rate_level, charge_id, amount, grp_id
                     order by property_number, room_code, rate_level, charge_id, start_date;
                    • 7. Re: Query Help - Trying to get single date field into Date Range
                      Aketi Jyuuzou
                      I used sense of "Tabibitosan" B-)
                      Group by preserving the order
                      with t as (
                      select 10 val, sysdate dt from dual union all
                      select 10 , sysdate+1 from dual union all
                      select 10 , sysdate+2 from dual union all
                      select 11 , sysdate+3 from dual union all
                      select 11 , sysdate+4 from dual union all
                      select 10 , sysdate+6 from dual union all
                      select 10 , sysdate+7 from dual union all
                      select 11 , sysdate+8 from dual union all
                      select 11 , sysdate+9 from dual)
                      select val,min(dt),max(dt)
                      from (select val,dt,
                            dt-Row_Number() over(partition by val order by dt) as distance
                            from t)
                      group by val,distance
                      order by min(dt);
                      
                      VAL  MIN(DT)   MAX(DT)
                      ---  --------  --------
                       10  09-03-06  09-03-08
                       11  09-03-09  09-03-10
                       10  09-03-12  09-03-13
                       11  09-03-14  09-03-15
                      • 8. Re: Query Help - Trying to get single date field into Date Range
                        621483
                        First get the data in required sequence by using order by clause.Then you can use lead/lag functions.