This is hard without DDL but assuming you cannot have this:

Case | Hours | Type

xxx | 3 | 1

xxx | 3 | 1

Or this:

Case | Hours | Type

xxx | 3 | 2

xxx | 3 | 2

e.g. two non-distinct types for any case/hours combination, then this will work:

Note that I used the ddl that Scott was nice enough to put together but added a constraint.

IF object_id('tempdb..#SomeTable') IS NOT NULL DROP TABLE #SomeTable;

CREATE TABLE #SomeTable

(CaseNum char(3),

MyHours int not null,

MyType int not null,

constraint case_hrs unique(CaseNum,MyHours,MyType));

INSERT INTO #SomeTable

SELECT 'xxx',3,1 UNION ALL

SELECT 'xxx',3,2 UNION ALL

SELECT 'xxx',4,1 UNION ALL

SELECT 'xxx',1,1;

WITH billed_credited AS

(

SELECT CaseNum, MyHours, COUNT(MyHours) AS bc

FROM #SomeTable

GROUP by CaseNum, MyHours

)

SELECT SUM(MyHours) AS ttl_hrs

FROM billed_credited

WHERE bc=1;

Edit: noticed an error with my code :ermm:, will have updated code momentarily.