MS SQL Server: Rollup with Datetime by Year

MS SQL Server: Rollup with Datetime by Year: "Title:
Rollup with Datetime by Year"
SELECT CASE WHEN (Grouping (DATEPART(year,date_and_time)) = 1) THEN '- All Years -' ELSE CAST(DATEPART(year,CAST(SUM(CAST(DATEPART(year,date_and_time) as int))/count(*) as datetime))as varchar) END as 'Year',CAST(sum(attempt) as varchar) as 'Attempts', CAST(sum(success) as varchar) as 'Success', CAST(avg((success/attempt)*100) as varchar)+'%' as 'Success Rate', CAST(sum(failure) as varchar) as 'Failure', CAST(avg((failure/attempt)*100) as varchar)+'%' as 'Failure Rate', CAST(sum(cancelled) as varchar) as 'Cancelled', CAST(avg((cancelled/attempt)*100) as varchar)+'%' as 'Cancelled Rate', CAST(sum(macro_no_value) as varchar) as 'Macro', CAST(avg((macro_no_value/attempt)*100) as varchar)+'%' as 'Macro Rate' FROM apr_stats GROUP BY (DATEPART(year,date_and_time)) with ROLLUP ORDER BY (DATEPART(year,date_and_time))


www.svdeals.com super value deals