Posted by mantrid on 12/27/07 22:57

Excellent thanks for that

To answer your questions

> That's the first time I've ever seen a inner join of a table with itself.
> Geez.

I did this as the only way I could think of to get my data displayed as I
wanted. This will be difficult to explain, but may be worth it as you may
offer a better solution. To explain its probably best to give some examples
of data and how I display it.

I wish the data to be displayed as follows

pdate pamount pprice sdate samount sprice
14/7/05 20 0.11 3/5/07 20 0.20
13/7/05 30 0.14 3/5/07 30 0.20

This data is stored in the table as follows

transactionid type linkid date amount price
1 p 1 14/7/05 20 0.11
2 p 1 13/7/05 30 0.14
3 s 1 3/5/07 50 0.20

So its basically all to do with displaying p transactions with its
associated s transaction in the same row.
Initially my table structurte was such that I had a separate field for all s
values and p values so I didnt have to do this but I had to change it for
other reasons, that I wont go into now.



>
> Other thing: what are ColA, ColB and ColC in your example???
>

Just colum names i made up in the example to explain the layout. they are
not in the actual code.
colA is cgttransactions.datetime colB is yrsheld (which is basically
DATEDIFF(cgttransactions.datetime,cgttransactions_1.datetime) rounded to
nearest year) and colC is profnloss.

My ordering at the moment is

ORDER BY
DATEDIFF(cgttransactions.datetime,cgttransactions_1.datetime) ";

should be

ORDER BY
cgttransactions.datetime;

and the cumulation should be by
DATEDIFF(cgttransactions.datetime,cgttransactions_1.datetime)



Ian

[Back to original message]