1

As a follow-up to my previous question, there is an alternative that I would like to explore. My table layout is as follows:

T1:  ID  |  Date  |  Hour  | Interval

The intervals span from 1 to 12 (the 5-minute interval in the hour), the hour goes from 1 to 24, and the date/ID is arbitrary. When I am pulling the data from an external source, there are some rows that have an interval of 0. When there is a 0 for the interval, this indicates that the entire hour is covered (i.e. all intervals from 1 to 12).

Example data:

T1:  1  |  1/1/2011  |  1  |  1
     1  |  1/1/2011  |  1  |  4
     1  |  1/1/2011  |  1  |  0

     1  |  1/1/2011  |  2  |  2
     1  |  1/1/2011  |  2  |  5

     1  |  1/1/2011  |  3  |  0

Notice that there is an overlap of the intervals and a 0-flagged interval. Is there any way I can expand the 0 intervals into all 12 intervals for the hour? So, I basically want to transform the example data into:

T1:  1  |  1/1/2011  |  1  |  1
     1  |  1/1/2011  |  1  |  2
     1  |  1/1/2011  |  1  |  3
     1  |  1/1/2011  |  1  |  4
     ...
     1  |  1/1/2011  |  1  |  11
     1  |  1/1/2011  |  1  |  12

     1  |  1/1/2011  |  2  |  2
     1  |  1/1/2011  |  2  |  5

     1  |  1/1/2011  |  3  |  1
     1  |  1/1/2011  |  3  |  2
     1  |  1/1/2011  |  3  |  3
     ...
     1  |  1/1/2011  |  3  |  11
     1  |  1/1/2011  |  3  |  12

Is something like this even possible using SQL?

Community
  • 1
  • 1
Breakthrough
  • 2,444
  • 2
  • 23
  • 37

1 Answers1

1

Create a table, "intervals", with 12 rows containing a single field, "iinterval", with values from 1 to 12.

iinterval
        1
        2
        3
        4
        5
        6
        7
        8
        9
       10
       11
       12

You can then do a cross join between the intervals table and those T1 rows where interval is zero. (I renamed the T1 fields Date, Hour, and Interval because those are all reserved words.)

SELECT T1.ID, T1.idate, T1.ihour, intervals.iinterval
FROM T1, intervals
WHERE T1.iinterval=0;

Since there is no JOIN condition specified, every T1 row (which has zero interval) will be joined with every intervals row.

ID  idate    ihour iinterval
 1  1/1/2011     1         1
 1  1/1/2011     1         2
 1  1/1/2011     1         3
 1  1/1/2011     1         4
 1  1/1/2011     1         5
 1  1/1/2011     1         6
 1  1/1/2011     1         7
 1  1/1/2011     1         8
 1  1/1/2011     1         9
 1  1/1/2011     1        10
 1  1/1/2011     1        11
 1  1/1/2011     1        12

If that gets what you wanted for the rows where interval equals zero, you can use it in a UNION query to add those rows to the T1 rows where the interval is not zero.

SELECT a.ID, a.idate, a.ihour, a.iinterval
FROM T1 AS a
WHERE a.iinterval<>0
UNION
SELECT T1.ID, T1.idate, T1.ihour, intervals.iinterval
FROM T1, intervals
WHERE T1.iinterval=0;

I hope this will point you to something useful, but doubt this is exactly what you ultimately want. My reservation is because of this condition: "Notice that there is an overlap of the intervals and a 0-flagged interval". I don't know what should happen in response to those overlaps.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Basically, I'm inner-joining this result set with another table, which only contains "valid" intervals (i.e. 1 through 12). It should overlap existing ones (and get rid of the interval 0 rows), kind of how my result set looks (the hour 1 data in the end only contains intervals 1 through 12, no duplicates). However, even if the method does leave me with duplicates, I'm sure I could create another query to filter out only the distinct rows... – Breakthrough Jul 07 '11 at 18:53
  • UNION returns only distinct rows, so you may be covered on that point already (if I understood your comment correctly). There is also UNION ALL, which operates faster than just UNION and would return all rows including duplicates. But that's not what I was suggesting. – HansUp Jul 08 '11 at 01:19
  • Ah, sorry, I didn't know that `UNION` only returned distinct rows. I shall accept this answer, but I'm curious, do you think there is a way to solve this problem without the use of an intermediate table (or is that outside the scope of the structured query language itself?) – Breakthrough Jul 08 '11 at 01:37
  • Yes, I think so. You don't have to save the results of the UNION query in a table. Save it as a named query, then use that query and your other table as the data sources in a new query. – HansUp Jul 08 '11 at 01:45