Something like the following?

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#Diagnosis','U') IS NOT NULL

DROP TABLE #Diagnosis

--===== Create the test table with

CREATE TABLE #Diagnosis

(

RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED

, DateDataRepresents Date

, CodeNumber DECIMAL(4,1)

, Location varchar(5)

)

INSERT INTO #Diagnosis

(DateDataRepresents, CodeNumber, Location)

SELECT '2012-12-16', 30.1, 'NY' UNION ALL

SELECT '2012-12-16', 40.1, 'LA' UNION ALL

SELECT '2012-12-16', 40.1, 'LA' UNION ALL

SELECT '2012-12-16', 40.1, 'LA' UNION ALL

SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

SELECT '2012-12-16', 50.1, 'NY' UNION ALL

SELECT '2012-12-16', 50.1, 'LA' UNION ALL

SELECT '2012-12-16', 40.1, 'LA' UNION ALL

SELECT '2012-12-16', 30.1, 'NY' UNION ALL

SELECT '2012-12-16', 40.1, 'NY' UNION ALL

SELECT '2012-12-16', 30.1, 'NY' UNION ALL

SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

SELECT '2012-12-16', 50.1, 'NY' UNION ALL

SELECT '2012-12-16', 50.1, 'LA' UNION ALL

SELECT '2012-12-16', 50.1, 'NY' UNION ALL

SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

SELECT '2012-12-16', 30.1, 'NY' UNION ALL

SELECT '2012-12-16', 50.1, 'LA' UNION ALL

SELECT '2012-12-16', 50.1, 'LA' UNION ALL

SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

SELECT '2012-12-16', 40.1, 'LA' UNION ALL

SELECT '2012-12-16', 40.1, 'LA' UNION ALL

SELECT '2012-12-16', 30.1, 'NY' UNION ALL

SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

SELECT '2012-12-16', 50.1, 'LA' UNION ALL

SELECT '2012-12-16', 30.1, 'LA' UNION ALL

SELECT '2012-12-16', 30.1, 'LA' UNION ALL

SELECT '2012-12-16', 30.1, 'LA' UNION ALL

SELECT '2012-12-16', 30.1, 'LA' UNION ALL

SELECT '2012-12-16', 50.1, 'NY' UNION ALL

SELECT '2012-12-16', 30.1, 'LA' UNION ALL

SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

SELECT '2012-12-16', 50.1, 'Indy' UNION ALL

SELECT '2012-12-16', 40.1, 'NY' UNION ALL

SELECT '2012-12-16', 40.1, 'Indy' UNION ALL

SELECT '2012-12-16', 50.1, 'LA' UNION ALL

SELECT '2012-12-16', 30.1, 'NY' UNION ALL

SELECT '2012-12-16', 50.1, 'LA' UNION ALL

SELECT '2012-12-16', 30.1, 'Indy' UNION ALL

SELECT '2012-12-16', 50.1, 'NY'

;with cte as

(

SELECT

DateDataRepresents

, Location

, CodeNumber

,COUNT(RowID) AS Count

FROM #Diagnosis

GROUP BY DateDataRepresents, CodeNumber,Location

with cube

)

select

DateDataRepresents,

Location,

ISNULL(CONVERT(VARCHAR,CodeNumber),'Total') AS CodeNumber,

Count

from cte where datedatarepresents is not null and location is not null order by Location,CodeNumber,DateDataRepresents

DROP TABLE #Diagnosis;