Re: Tricky ACE report problem
In article <kcjDCHD9n.727@netcom.com>, Kate Juliff <kcj@netcom.com> wrote:
>I have a table that amongth other columns has
>
>order_no line_item_no due_date
>
>and I've been asked to do a report with the following layout
>
>For the 7 days prior to today
>
>date1 date2 date3 ... date7
>
>order_no, line order_no, line order_no, line
>
>such that there are 7 columns, with the date headings and below each
>date a list of all items that fall due on that date.
OK, let's say I want to know what invoice numbers and line numbers
we have in the database for the last seven days.
Just to get a feel for what data we are going to find, I run
the following query:-
select inv_date, inv_mast.inv_num, in_line1
from inv_mast, inv_item
where inv_mast.inv_num = inv_item.inv_num
and inv_date > today - 7
into temp t1 ;
select inv_date, count(*)
from t1
group by 1
order by 1
which returns:-
inv_date (count(*))
07/27/1995 4
07/28/1995 124
07/29/1995 30
08/01/1995 9
08/02/1995 30
(I should point out that this is a test database and doesn't reflect
the pace of business at Informix Software, Inc. It would be pretty
sad if it did.)
Next, an ACE report which prints out the following:-
[ I chopped off the first and last columns ("today - 7" and "today - 0")
as a friendly gesture to those who are reading on 80-column terminals) ]
today - 6 today - 5 today - 4 today - 3 today - 2 today - 1
----------+------------+------------+------------+------------+------------
451788 1 | 452445 1 | 452629 1 | | | 452859 1 |
451788 2 | 452445 2 | 452629 2 | | | 452859 2 |
451788 3 | 452445 3 | 452631 1 | | | 452859 3 |
451788 4 | 452445 4 | 452631 2 | | | 452859 4 |
| 452445 5 | 452635 1 | | | 452859 5 |
| 452445 6 | 452635 2 | | | 452859 6 |
| 452445 7 | 452635 3 | | | 452947 1 |
| 452445 8 | 452635 4 | | | 452947 2 |
| 452445 9 | 452635 5 | | | 452947 3 |
| 452445 10 | 452635 6 | | | |
| 452446 1 | 452637 1 | | | |
| 452446 2 | 452637 2 | | | |
| 452446 3 | 452637 3 | | | |
| 452446 4 | 452637 4 | | | |
| 452446 5 | 452637 5 | | | |
| 452446 6 | 452637 6 | | | |
| 452446 7 | 452639 1 | | | |
| 452446 8 | 452639 2 | | | |
| 452446 9 | 452639 3 | | | |
| 452446 10 | 452641 1 | | | |
| 452447 1 | 452641 2 | | | |
| 452447 2 | 452641 3 | | | |
| 452447 3 | 452641 4 | | | |
| 452447 4 | 452641 5 | | | |
| 452447 5 | 452643 1 | | | |
| 452447 6 | 452643 2 | | | |
| 452447 7 | 452643 3 | | | |
| 452447 8 | 452643 4 | | | |
| 452447 9 | 452643 5 | | | |
| 452447 10 | 452643 6 | | | |
| 452447 11 | | | | |
| 452447 12 | | | | |
| 452447 13 | | | | |
| 452447 14 | | | | |
| 452447 15 | | | | |
| 452447 16 | | | | |
| 452447 17 | | | | |
| 452448 1 | | | | |
| 452448 2 | | | | |
| 452448 3 | | | | |
| 452448 4 | | | | |
| 452448 5 | | | | |
| 452448 6 | | | | |
| 452448 7 | | | | |
| 452448 8 | | | | |
| 452448 9 | | | | |
| 452448 10 | | | | |
| 452448 11 | | | | |
| 452448 12 | | | | |
| 452448 13 | | | | |
| 452448 14 | | | | |
| 452448 15 | | | | |
| 452448 16 | | | | |
| 452448 17 | | | | |
| 452449 1 | | | | |
| 452449 2 | | | | |
| 452449 3 | | | | |
| 452450 1 | | | | |
today - 6 today - 5 today - 4 today - 3 today - 2 today - 1
----------+------------+------------+------------+------------+------------
| 452450 2 | | | | |
| 452450 3 | | | | |
| 452451 1 | | | | |
| 452451 2 | | | | |
| 452451 3 | | | | |
| 452451 4 | | | | |
| 452451 5 | | | | |
| 452451 6 | | | | |
| 452451 7 | | | | |
| 452451 8 | | | | |
| 452451 9 | | | | |
| 452451 10 | | | | |
| 452451 11 | | | | |
| 452451 12 | | |