Hello all, I know that SQL DB's don't have inherent Crosstab capabilities
and I need to create a report for a customer that is setup like so:

Product Department/Category/Name Jan Feb March Total
Beverages
Coke 33.33 44.44 55.55 133.32
Sprite 33.33 44.44 55.55 133.32

Summary Band =======================> xx.xx xx.xx xx.xx xxx.xx

I have tried two ways so far. One, I have been populating a mem table
manually with the values which is very close since there are many separate
queries being run. Secondly, I have tried the query below which is faster,
just not as fast as I'd like it. Can there be any improvement on the query?
I have included the PLAN as well.

If you require the table schemas, I will post them too. Just didn't want to
send a huge post if not necessary.

Thank you,

Lee


Plan

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Plan
PLAN SORT (SORT (JOIN (IP INDEX (ITEMSPERMANENT2_ITEMDATE),FD INDEX
(RDB$PRIMARY7))))
PLAN (I1 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))
PLAN (I2 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))
PLAN (I3 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))
PLAN (I4 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))
PLAN (I5 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))
PLAN (I6 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))
PLAN (I7 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))
PLAN (I8 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))
PLAN (I9 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))
PLAN (I10 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))
PLAN (I11 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))
PLAN (I12 INDEX (ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))

Adapted Plan
PLAN SORT (SORT (JOIN (IP INDEX (ITEMSPERMANENT2_ITEMDATE),FD INDEX
(INTEG_17)))) PLAN (I1 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE)) PLAN (I2 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE)) PLAN (I3 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE)) PLAN (I4 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE)) PLAN (I5 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE)) PLAN (I6 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE)) PLAN (I7 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE)) PLAN (I8 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE)) PLAN (I9 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE)) PLAN (I10 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE)) PLAN (I11 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE)) PLAN (I12 INDEX
(ITEMSPERMANENT2_PRODUCTID,ITEMSPERMANENT2_ITEMDATE))

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

SELECT
IP.PRODUCTDESCRIPTION,
IP.ProductID,
IP.CATEGORY,
FD.FoodDepartmentName,
(SELECT SUM(I1.ItemPrice) FROM ItemsPermanent2 I1
WHERE EXTRACT(MONTH FROM I1.ItemDate) = 1 AND
I1.ItemDate >= :StartDate AND I1.ItemDate <= :EndDate AND
I1.productid = IP.ProductID ) AS JanSales,
(SELECT SUM(I2.ItemPrice) FROM ItemsPermanent2 I2
WHERE EXTRACT(MONTH FROM I2.ItemDate) = 2 AND
I2.ItemDate >= :StartDate AND I2.ItemDate <= :EndDate AND
I2.productid = IP.ProductID) AS FebSales,
(SELECT SUM(I3.ItemPrice) FROM ItemsPermanent2 I3
WHERE EXTRACT(MONTH FROM I3.ItemDate) = 3 AND
I3.ItemDate >= :StartDate AND I3.ItemDate <= :EndDate AND
I3.productid = IP.ProductID) AS MarSales,
(SELECT SUM(I4.ItemPrice) FROM ItemsPermanent2 I4
WHERE EXTRACT(MONTH FROM I4.ItemDate) = 4 AND
I4.ItemDate >= :StartDate AND I4.ItemDate <= :EndDate AND
I4.productid = IP.ProductID) AS AprSales,
(SELECT SUM(I5.ItemPrice) FROM ItemsPermanent2 I5
WHERE EXTRACT(MONTH FROM I5.ItemDate) = 5 AND
I5.ItemDate >= :StartDate AND I5.ItemDate <= :EndDate AND
I5.productid = IP.ProductID) AS MaySales,
(SELECT SUM(I6.ItemPrice) FROM ItemsPermanent2 I6
WHERE EXTRACT(MONTH FROM I6.ItemDate) = 6 AND
I6.ItemDate >= :StartDate AND I6.ItemDate <= :EndDate AND
I6.productid = IP.ProductID) AS JunSales,
(SELECT SUM(I7.ItemPrice) FROM ItemsPermanent2 I7
WHERE EXTRACT(MONTH FROM I7.ItemDate) = 7 AND
I7.ItemDate >= :StartDate AND I7.ItemDate <= :EndDate AND
I7.productid = IP.ProductID) AS JulSales,
(SELECT SUM(I8.ItemPrice) FROM ItemsPermanent2 I8
WHERE EXTRACT(MONTH FROM I8.ItemDate) = 8 AND
I8.ItemDate >= :StartDate AND I8.ItemDate <= :EndDate AND
I8.productid = IP.ProductID) AS AugSales,
(SELECT SUM(I9.ItemPrice) FROM ItemsPermanent2 I9
WHERE EXTRACT(MONTH FROM I9.ItemDate) = 9 AND
I9.ItemDate >= :StartDate AND I9.ItemDate <= :EndDate AND
I9.productid = IP.ProductID) AS SepSales,
(SELECT SUM(I10.ItemPrice) FROM ItemsPermanent2 I10
WHERE EXTRACT(MONTH FROM I10.ItemDate) = 10 AND
I10.ItemDate >= :StartDate AND I10.ItemDate <= :EndDate AND
I10.productid = IP.ProductID) AS OctSales,
(SELECT SUM(I11.ItemPrice) FROM ItemsPermanent2 I11
WHERE EXTRACT(MONTH FROM I11.ItemDate) = 11 AND
I11.ItemDate >= :StartDate AND I11.ItemDate <= :EndDate AND
I11.productid = IP.ProductID) AS NovSales,
(SELECT SUM(I12.ItemPrice) FROM ItemsPermanent2 I12
WHERE EXTRACT(MONTH FROM I12.ItemDate) = 12 AND
I12.ItemDate >= :StartDate AND I12.ItemDate <= :EndDate AND
I12.productid = IP.ProductID) AS DecSales

FROM
ItemsPermanent2 IP

INNER Join FoodDepartments FD ON (FD.fooddepartmentid = IP.FoodDepartmentID)
WHERE
(IP.ITEMDATE >= :StartDate) AND
(IP.ITEMDATE <= :EndDate)
GROUP BY
FD.FoodDepartmentName,
IP.PRODUCTDESCRIPTION,
IP.ProductID,
IP.CATEGORY
ORDER BY
FD.FoodDepartmentName,
IP.CATEGORY,
IP.PRODUCTDESCRIPTION