>>>select (sum(iif(Month=1,Amount,000000000000)) as Jan, ...., Year, Category group by Year, Category - use this as a derived table.
>>>
>>>Actually, looks like this select alone will give you what you want.
>>
>>There should be no need to sum. We are not combining records, only positioning them. What I'm getting is:
>>Category, Sep, Oct, Nov, Fisyear
>>Cat1, ##.##, ##.##, ##.##,2007
>>Cat2, ##.##, ##.##, ##.##,2007
>>Cat3, ##.##, ##.##, ##.##,2007
>>
>>What I want is:
>>Category, Sep, Oct, Nov, Fisyear
>>Cat1, ##.##, ##.##, ##.##,2007
>>Cat2, ##.##, ##.##, ##.##,2007
>>Cat3, ##.##, ##.##, ##.##,2007
>>Cat1, ##.##, ##.##, ##.##,2008
>>Cat2, ##.##, ##.##, ##.##,2008
>>Cat3, ##.##, ##.##, ##.##,2008
>>
>>The reason I'm only getting the first year is because the first cursor (crsA) which contains the year, is not being iterated. I had thought that I could use the second select as the derived table and wrap it into the first as in
>>select dist fisyear, ;
>>(select dist category, ;
>> nvl((select amount ;
>> from rta_revenues ;
>> where fisyear = crsA.fisyear and ;
>> category = crsB.category and ;
>> month(receiveddate) = 9),0) as sep , ;
>> nvl((select amount ;
>> from rta_revenues ;
>> where fisyear = crsA.fisyear and ;
>> category = crsB.category and ;
>> month(receiveddate) = 10),0) as oct , ;
>> nvl((select amount ;
>> from rta_revenues ;
>> where fisyear = crsA.fisyear and ;
>> category = crsB.category and ;
>> month(receiveddate) = 11),0) as nov , ;
>> crsA.fisyear as fisyear ;
>> from rta_revenues crsB) ;
>> from rta_revenues crsA order by fisyear
>>
>>but this throws an error that says the subquery is invalid.
>
>Re-read my message one more time (I made a small change). If you only have one record per category in a month, then just remove SUM from my select statement and group by. It would give you the result you want, very simple select.
>
>
>select Category, CAST(iif(Month(receiveddate)=1,Amount,0000000000.00) as N(5,2)) as Jan, ...., FisYear
Here is the final
select orderby, jul,aug,sep,oct,nov,dec,Jan,Feb,Mar,apr,may,jun,FisYear ;
from rta_revenuecategories crsA;
inner join ( ;
select ;
sum(iif(month(receiveddate)=7,Amount,000000000000.00)) as jul, ;
sum(iif(month(receiveddate)=8,Amount,000000000000.00)) as aug, ;
sum(iif(month(receiveddate)=9,Amount,000000000000.00)) as sep, ;
sum(iif(month(receiveddate)=10,Amount,000000000000.00)) as oct, ;
sum(iif(month(receiveddate)=11,Amount,000000000000.00)) as nov, ;
sum(iif(month(receiveddate)=12,Amount,000000000000.00)) as dec, ;
sum(iif(month(receiveddate)=1,Amount,000000000000.00)) as Jan, ;
sum(iif(month(receiveddate)=2,Amount,000000000000.00)) as Feb, ;
sum(iif(month(receiveddate)=3,Amount,000000000000.00)) as Mar, ;
sum(iif(month(receiveddate)=4,Amount,000000000000.00)) as apr, ;
sum(iif(month(receiveddate)=5,Amount,000000000000.00)) as may, ;
sum(iif(month(receiveddate)=6,Amount,000000000000.00)) as jun, ;
FisYear, Category ;
from rta_revenues ;
group by FisYear, Category) crsB;
on crsA.Category = crsB.Category ;
order by FisYear, orderby
Actually, your original worked. It seems the sum was needed to enable the group by. I had to add an additional table with an inner join because the Group By messed up the sort order of the output. This one seems to work fine. Thanks for your help. :)
