Solved Help to Convert Single Range to Min and Max (RE: http://allenbrowne.com/ser-58.html) (1 Viewer)

[raziel3]

I'm trying to follow Allen Brown's method as seen here http://allenbrowne.com/ser-58.html to convert a single range to Min and Max Values

This is the Table

+----------+-------+------------+
|  SDate   | Class |   Range    |
+----------+-------+------------+
| 3-Mar-14 |    1  |     780.00 |
| 3-Mar-14 |    2  |   1,300.00 |
| 3-Mar-14 |    3  |   1,733.00 |
| 3-Mar-14 |    4  |   2,340.00 |
| 3-Mar-14 |    5  |   2,903.00 |
| 3-Mar-14 |    6  |   3,553.00 |
| 3-Mar-14 |    7  |   4,290.00 |
| 3-Mar-14 |    8  |   4,983.00 |
| 3-Mar-14 |    9  |   5,720.00 |
| 3-Mar-14 |   10  |   6,543.00 |
| 3-Mar-14 |   11  |   7,323.00 |
| 3-Mar-14 |   12  |   8,190.00 |
| 3-Mar-14 |   13  |   9,100.00 |
| 3-Mar-14 |   14  |  10,053.00 |
| 3-Mar-14 |   15  |  11,180.00 |
| 3-Mar-14 |   16  |  12,000.00 |
+----------+-------+------------+

These are the Results I'm looking for

+----------+-------+-------------+------------+
|  SDate   | Class |  MinRange   |  MaxRange  |
+----------+-------+-------------+------------+
| 3-Mar-14 |    1  |     780.00  |  1,299.99  |
| 3-Mar-14 |    2  |   1,300.00  |  1,732.99  |
| 3-Mar-14 |    3  |   1,733.00  |  2,339.99  |
| 3-Mar-14 |    4  |   2,340.00  |  2,902.99  |
| 3-Mar-14 |    5  |   2,903.00  |  3,552.99  |
| 3-Mar-14 |    6  |   3,553.00  |  4,289.99  |
| 3-Mar-14 |    7  |   4,290.00  |  4,982.99  |
| 3-Mar-14 |    8  |   4,983.00  |  5,719.99  |
| 3-Mar-14 |    9  |   5,720.00  |  6,542.99  |
| 3-Mar-14 |   10  |   6,543.00  |  7,322.99  |
| 3-Mar-14 |   11  |   7,323.00  |  8,189.99  |
| 3-Mar-14 |   12  |   8,190.00  |  9,099.99  |
| 3-Mar-14 |   13  |   9,100.00  |  10,052.99 |
| 3-Mar-14 |   14  |  10,053.00  |  11,179.99 |
| 3-Mar-14 |   15  |  11,180.00  |  11,999.99 |
| 3-Mar-14 |   16  |  12,000.00  |  infinity  |
+----------+-------+-------------+------------+

It should be noted that the Ranges updates ever so often so the actual table looks like this

+----------+-------+-----------+
|  SDate   | Class |   Range   |
+----------+-------+-----------+
| 3-Mar-14 |     1 |       780 |
| 3-Mar-14 |     2 |  1,300.00 |
| 3-Mar-14 |     3 |  1,733.00 |
| 3-Mar-14 |     4 |  2,340.00 |
| 3-Mar-14 |     5 |  2,903.00 |
| 3-Mar-14 |     6 |  3,553.00 |
| 3-Mar-14 |     7 |  4,290.00 |
| 3-Mar-14 |     8 |  4,983.00 |
| 3-Mar-14 |     9 |  5,720.00 |
| 3-Mar-14 |    10 |  6,543.00 |
| 3-Mar-14 |    11 |  7,323.00 |
| 3-Mar-14 |    12 |  8,190.00 |
| 3-Mar-14 |    13 |  9,100.00 |
| 3-Mar-14 |    14 | 10,053.00 |
| 3-Mar-14 |    15 | 11,180.00 |
| 3-Mar-14 |    16 | 12,000.00 |
| 5-Sep-16 |     1 |       867 |
| 5-Sep-16 |     2 |  1,473.00 |
| 5-Sep-16 |     3 |  1,950.00 |
| 5-Sep-16 |     4 |  2,643.00 |
| 5-Sep-16 |     5 |  3,293.00 |
| 5-Sep-16 |     6 |  4,030.00 |
| 5-Sep-16 |     7 |  4,853.00 |
| 5-Sep-16 |     8 |  5,633.00 |
| 5-Sep-16 |     9 |  6,457.00 |
| 5-Sep-16 |    10 |  7,410.00 |
| 5-Sep-16 |    11 |  8,277.00 |
| 5-Sep-16 |    12 |  9,273.00 |
| 5-Sep-16 |    13 | 10,313.00 |
| 5-Sep-16 |    14 | 11,397.00 |
| 5-Sep-16 |    15 | 12,653.00 |
| 5-Sep-16 |    16 | 13,600.00 |
+----------+-------+-----------+

Oh, and one more challenge is if the query can be built to be seen by Excel's Power Query.

 

[CJ_London]

Your ‘range’ query would be something like

select a.*, b.minrange-0.01 as maxrange

From mytable A left join mytable B

on a.sdate=b.sdate and b.class=a.class+1

edited code

 

[raziel3]

Thanks @CJ_London this solution is so simple I feel like an idiot.

Had no idea you could make joins like this

b.class=a.class+1

 

[CJ_London]

A useful thing to be able to do but can’t be designed in the QBE

Alternative is to put that part of the join as a criteria