This discussion is archived
8 Replies Latest reply: Mar 6, 2009 2:45 AM by 621483 RSS

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

689401 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    First get the data in required sequence by using order by clause.Then you can use lead/lag functions.

Legend

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