Hey

I am simply posting this as it may help some who with their SQL queries when they want to use the "GROUP BY" option within their queries.

The following query is valid in normal circumstances but when used within an XSLT file and the jesper plug-in it fails with the following error

"An error occurred during a call to extension function 'SQLXml'. See InnerException for a complete description of the error... GROUP BY and aggregate functions are currently not supported with FOR XML AUTO... "

SELECT 
   category, RecvdDate, count(callid) as dailyTotal 
FROM 
   calllog 
WHERE 
   category IN ('Hardware','Other Software','Wholesale Operations') AND 
   RecvdDate <= '2011-05-15' AND 
   RecvdDate >= '2011-04-17' 
GROUP BY 
   category, RecvdDate
ORDER BY 
  category, RecvdDate

To get around this you need to wrap the query with another SELECT statement as follows:

SELECT outputTotals.category, outputTotals.RecvdDate, outputTotals.dailyTotal FROM 
    (
  SELECT 
     category, RecvdDate, count(callid) as dailyTotal 
   FROM 
       calllog 
    WHERE 
      category IN ('Hardware','Other Software','Wholesale Operations') AND 
       RecvdDate <= '2011-05-15' AND 
       RecvdDate >= '2011-04-17' 
   GROUP BY 
       category, RecvdDate
  ORDER BY 
       category, RecvdDate
    ) AS outputTotals 
ORDER BY
    category, RecvdDate

I hope this helps someone as I love using this great plug-in.

Regards

Nigel