I am having issues combining `with rollup` and `GROUPING`. How to correct?

I recently was introduced to a feature called with rollup. I am adding into a report I use in Excel for trends. Issue is, I want to have the t0.DocDate ordered on date from oldest to newest date.

I read an article on: http://mangalpardeshi.blogspot.com/2009/08/rollup-and-order-by.html

I formatted my code to be similar to the example in the article but am getting this error message after doing so: Conversion failed when converting date and/or time from character string.

Is there anyway to correct this error in the query? If so, it would be very much appreciated by anyone who could assist me!

My code is:

select  CASE WHEN GROUPING(t0.DocDate) = 1 THEN 'Total' ELSE T0.DocDate END AS 'DocDate',
        COUNT(T1.Itemcode) [# of Cross Sell],
        SUM(T1.Price * t1.Quantity) [Cross Sell $]

from ORDR t0 inner join RDR1 t1 on t0.DocEntry=t1.DocEntry

where t0.CANCELED <> 'Y'
and t1.U_SII_XSell = 'Y'

group by t0.DocDate WITH ROLLUP
order by GROUPING(t0.docdate);

Answer

You need to convert your DocDate to a VARCHAR if you wish to use 'Total' for the rollup column value. The column cannot be both types.

To then order it by date, and have your Total column last, you need to just add t0.DocDate to your ORDER BY clause.

select  CASE WHEN GROUPING(t0.DocDate) = 1 THEN 'Total' ELSE Cast(T0.DocDate As Varchar(15)) END AS 'DocDate',
        COUNT(T1.Itemcode) [# of Cross Sell],
        SUM(T1.Price * t1.Quantity) [Cross Sell $]

from ORDR t0 inner join RDR1 t1 on t0.DocEntry=t1.DocEntry

where t0.CANCELED <> 'Y'
and t1.U_SII_XSell = 'Y'

group by t0.DocDate WITH ROLLUP
order by GROUPING(t0.docdate), t0.docdate;