select [fisyear], [category], (select [amount] from [RTA-Revenues] where (MONTH(receiveddate)=7 and CATEGORY=crsA.CATEGORY and FISYEAR=crsA.fisyear)) as jul, (select [amount] from [RTA-Revenues] where (MONTH(receiveddate)=8 and CATEGORY=crsA.CATEGORY and FISYEAR=crsA.fisyear)) as aug, (select [amount] from [RTA-Revenues] where (MONTH(receiveddate)=9 and CATEGORY=crsA.CATEGORY and FISYEAR=crsA.fisyear)) as sep from [rta-revenues] as crsA group by [fisyear],[category] order by FISYEAR, CATEGORYNote it does not use the CASE statement we had been playing with. What I don't understand is why the former did not work and why it insisted on having receiveddate in the group by clause, since it was not actually in the select list. It was just included in a function within the select list. Oh well, thanks for your help. As always, you made me think.
>select crsB.[category], jul, [fisyear] >>from (select distinct [category] from [RTA-Revenues]) as crsA >>inner join ( >>select [fisYear], [category], >>CASE WHEN month([receiveddate])=7 THEN sum([AMOUNT]) ELSE CAST(0 AS numeric(12,2)) END as Jul >>from [rta-revenues] >>group by [FISYEAR], [CATEGORY]) as crsB >>on crsA.[CATEGORY] = crsB.[CATEGORY] >>order by [FISYEAR], [CATEGORY] >>>>
>>>>select crsB.category, jul, [fisyear] >>>>from (select distinct category from [RTA-Revenues]) as crsA >>>>inner join ( >>>>select [fisYear], [category], >>>>CASE WHEN month([receiveddate])=7 THEN sum(AMOUNT) ELSE CAST(0 AS numeric(12,2)) END as Jul >>>>from [rta-revenues] >>>>group by receiveddate, FISYEAR, CATEGORY) as crsB >>>>on crsA.CATEGORY = crsB.CATEGORY >>>>order by FISYEAR >>>>>>>>