6 Replies Latest reply: Mar 28, 2013 9:17 AM by michaelrozar17 RSS

    Formatting data

    michaelrozar17
      Please help me in formatting the data.I want to group the below table data based on the Grade column for a header_data with start_time and end_time displayed in range. I was trying with group by, partitions etc but no luck. I use version 10gr2.
      create table rel_data_mf (header_data varchar2(10),start_time varchar2(100),end_time varchar2(100),grade varchar2(1));
      
      --table rel_data_mf data as comma separated values
      header_data,start_time,end_time,Grade
      ENG,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U
      ENG,2013-03-29 01:00:00-05:00,2013-03-29 02:00:00-05:00,U
      ENG,2013-03-29 02:00:00-05:00,2013-03-29 03:00:00-05:00,U
      ENG,2013-03-29 03:00:00-05:00,2013-03-29 04:00:00-05:00,A
      ENG,2013-03-29 04:00:00-05:00,2013-03-29 05:00:00-05:00,A
      ENG,2013-03-29 05:00:00-05:00,2013-03-29 06:00:00-05:00,A
      ENG,2013-03-29 06:00:00-05:00,2013-03-29 07:00:00-05:00,A
      ENG,2013-03-29 07:00:00-05:00,2013-03-29 08:00:00-05:00,U
      ENG,2013-03-29 08:00:00-05:00,2013-03-29 09:00:00-05:00,U
      ENG,2013-03-29 09:00:00-05:00,2013-03-29 10:00:00-05:00,U
      
      MATH,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U
      MATH,2013-03-29 01:00:00-05:00,2013-03-29 02:00:00-05:00,U
      MATH,2013-03-29 02:00:00-05:00,2013-03-29 03:00:00-05:00,U
      MATH,2013-03-29 03:00:00-05:00,2013-03-29 04:00:00-05:00,B
      MATH,2013-03-29 04:00:00-05:00,2013-03-29 05:00:00-05:00,B
      MATH,2013-03-29 05:00:00-05:00,2013-03-29 06:00:00-05:00,B
      MATH,2013-03-29 06:00:00-05:00,2013-03-29 07:00:00-05:00,B
      MATH,2013-03-29 07:00:00-05:00,2013-03-29 08:00:00-05:00,U
      MATH,2013-03-29 08:00:00-05:00,2013-03-29 09:00:00-05:00,U
      MATH,2013-03-29 09:00:00-05:00,2013-03-29 10:00:00-05:00,U
      MATH,2013-03-29T10:00:00-05:00,2013-03-29 11:00:00-05:00,U
      MATH,2013-03-29T11:00:00-05:00,2013-03-29 12:00:00-05:00,U
      MATH,2013-03-29T12:00:00-05:00,2013-03-29 13:00:00-05:00,U
      
      --Required output
      header_data,start_time,end_time,Grade
      ENG,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
      ENG,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,A
      ENG,2013-03-29 07:00:00-05:00,2013-03-29 10:00:00-05:00,U
      MATH,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
      MATH,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,B
      MATH,2013-03-29 07:00:00-05:00,2013-03-29 13:00:00-05:00,U
        • 1. Re: Formatting data
          APC
          Could you explain the business rules underlying the output? Understanding the derivation is crucial to providing a solution.

          Cheers, APC
          • 2. Re: Formatting data
            Frank Kulash
            Hi,
            michaelrozar17 wrote:
            Please help me in formatting the data.I want to group the below table data based on the Grade column for a header_data with start_time and end_time displayed in range. I was trying with group by, partitions etc but no luck. I use version 10gr2.
            create table rel_data_mf (header_data varchar2(10),start_time varchar2(100),end_time varchar2(100),grade varchar2(1));
            Thanks for posting the CREATE TABLE statement.
            Don't forget to post INSERT statements to populate the table.
            --table rel_data_mf data as comma separated values
            header_data,start_time,end_time,Grade
            ENG,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U 
            ...
            If str is a comma-delimited string, then
            REGEXP_SUBSTR ( str
                          , '[^,]+'
                          , 1
                          , 3
                          )
            is the 3rd comma delimited part. (This assumes that the first 2 parts have at least 1 character. If not, that is, if you have data like ',,2013-03-29 01:00:00-05:00,U', then it's a little more complicated, but only a little.)
            For example:
            SELECT  REGEXP_SUBSTR ( 'ENG,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U'
                            , '[^,]+'
                            , 1
                            , 3
                            )          AS part_3
            FROM    dual;
            Output:
            PART_3
            -------------------------
            2013-03-29 01:00:00-05:00
            Edited by: Frank Kulash on Mar 28, 2013 9:56 AM
            Added example
            • 3. Re: Formatting data
              Ramin Hashimzadeh
              i think you want something like this , check plz:
              SQL> WITH t(header_data,start_time,end_time,Grade) AS
                2  (
                3  SELECT 'ENG','2013-03-29 00:00:00-05:00','2013-03-29 01:00:00-05:00','U' FROM dual UNION ALL
                4  SELECT 'ENG','2013-03-29 01:00:00-05:00','2013-03-29 02:00:00-05:00','U' FROM dual UNION ALL
                5  SELECT 'ENG','2013-03-29 02:00:00-05:00','2013-03-29 03:00:00-05:00','U' FROM dual UNION ALL
                6  SELECT 'ENG','2013-03-29 03:00:00-05:00','2013-03-29 04:00:00-05:00','A' FROM dual UNION ALL
                7  SELECT 'ENG','2013-03-29 04:00:00-05:00','2013-03-29 05:00:00-05:00','A' FROM dual UNION ALL
                8  SELECT 'ENG','2013-03-29 05:00:00-05:00','2013-03-29 06:00:00-05:00','A' FROM dual UNION ALL
                9  SELECT 'ENG','2013-03-29 06:00:00-05:00','2013-03-29 07:00:00-05:00','A' FROM dual UNION ALL
               10  SELECT 'ENG','2013-03-29 07:00:00-05:00','2013-03-29 08:00:00-05:00','U' FROM dual UNION ALL
               11  SELECT 'ENG','2013-03-29 08:00:00-05:00','2013-03-29 09:00:00-05:00','U' FROM dual UNION ALL
               12  SELECT 'ENG','2013-03-29 09:00:00-05:00','2013-03-29 10:00:00-05:00','U' FROM dual UNION ALL
               13  SELECT 'MATH','2013-03-29 00:00:00-05:00','2013-03-29 01:00:00-05:00','U' FROM dual UNION ALL
               14  SELECT 'MATH','2013-03-29 01:00:00-05:00','2013-03-29 02:00:00-05:00','U' FROM dual UNION ALL
               15  SELECT 'MATH','2013-03-29 02:00:00-05:00','2013-03-29 03:00:00-05:00','U' FROM dual UNION ALL
               16  SELECT 'MATH','2013-03-29 03:00:00-05:00','2013-03-29 04:00:00-05:00','B' FROM dual UNION ALL
               17  SELECT 'MATH','2013-03-29 04:00:00-05:00','2013-03-29 05:00:00-05:00','B' FROM dual UNION ALL
               18  SELECT 'MATH','2013-03-29 05:00:00-05:00','2013-03-29 06:00:00-05:00','B' FROM dual UNION ALL
               19  SELECT 'MATH','2013-03-29 06:00:00-05:00','2013-03-29 07:00:00-05:00','B' FROM dual UNION ALL
               20  SELECT 'MATH','2013-03-29 07:00:00-05:00','2013-03-29 08:00:00-05:00','U' FROM dual UNION ALL
               21  SELECT 'MATH','2013-03-29 08:00:00-05:00','2013-03-29 09:00:00-05:00','U' FROM dual UNION ALL
               22  SELECT 'MATH','2013-03-29 09:00:00-05:00','2013-03-29 10:00:00-05:00','U' FROM dual UNION ALL
               23  SELECT 'MATH','2013-03-29 10:00:00-05:00','2013-03-29 11:00:00-05:00','U' FROM dual UNION ALL
               24  SELECT 'MATH','2013-03-29 11:00:00-05:00','2013-03-29 12:00:00-05:00','U' FROM dual UNION ALL
               25  SELECT 'MATH','2013-03-29 12:00:00-05:00','2013-03-29 13:00:00-05:00','U' FROM dual
               26  )
               27  SELECT header_data,
               28         min(start_time),
               29         max(end_time),
               30         Grade
               31  FROM(
               32  SELECT t.*, abs(row_number() OVER (PARTITION BY header_data,grade ORDER BY start_time)-ROWNUM) rn
               33  FROM t
               34  )
               35  GROUP BY header_data,grade,rn
               36  ORDER BY header_data,rn
               37  /
              HEADER_DATA MIN(START_TIME)           MAX(END_TIME)             GRADE
              ----------- ------------------------- ------------------------- -----
              ENG         2013-03-29 00:00:00-05:00 2013-03-29 03:00:00-05:00 U
              ENG         2013-03-29 03:00:00-05:00 2013-03-29 07:00:00-05:00 A
              ENG         2013-03-29 07:00:00-05:00 2013-03-29 10:00:00-05:00 U
              MATH        2013-03-29 00:00:00-05:00 2013-03-29 03:00:00-05:00 U
              MATH        2013-03-29 03:00:00-05:00 2013-03-29 07:00:00-05:00 B
              MATH        2013-03-29 07:00:00-05:00 2013-03-29 13:00:00-05:00 U
              6 rows selected
              
              SQL> 
              • 4. Re: Formatting data
                michaelrozar17
                The data in the table rel_data_mf is extrapolated in hourly basis, with column start_time and end_time holds each hours time in a day in 24 hours format(actually there must be 24 intervals, i have just shortened it). I want the data to be grouped based on the columns header_data and grade, the twist (to me :)) is start_time and end_time column must come in range. If the hourly extrapolated data is as below i.e for header_data 'ENG' from start_time 00:00:00 to 03:00:00 grade is 'U'. For the same header_data 'ENG' from start_time 03:00:00 to 07:00:00 grade is 'A', the output expected is as shown below:
                --extrapolated hourly data
                header_data,start_time,end_time,Grade
                ENG,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U
                ENG,2013-03-29 01:00:00-05:00,2013-03-29 02:00:00-05:00,U
                ENG,2013-03-29 02:00:00-05:00,2013-03-29 03:00:00-05:00,U
                ENG,2013-03-29 03:00:00-05:00,2013-03-29 04:00:00-05:00,A
                ENG,2013-03-29 04:00:00-05:00,2013-03-29 05:00:00-05:00,A
                ENG,2013-03-29 05:00:00-05:00,2013-03-29 06:00:00-05:00,A
                ENG,2013-03-29 06:00:00-05:00,2013-03-29 07:00:00-05:00,A
                
                --output grouped with start and end time column in range format
                header_data,start_time,end_time,Grade
                ENG,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
                ENG,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,A
                • 5. Re: Formatting data
                  Ramin Hashimzadeh
                  did you check my query?
                  • 6. Re: Formatting data
                    michaelrozar17
                    Thanks Ramin Hashimzadeh, that's the expected result.