Distinct sums of case statements

Try adding a group by. Without a dataset in front of me I would try a group by month(hist_date)

This is a two-step problem that you can solve with ROW_NUMBER.

Here's a sample solution:

declare @table table
(
 empid int,
 hist_date date
);

insert into @table(empid, hist_date) values
(132, '7/1/2012'),
(143, '7/1/2012'),
(154, '7/2/2012'),
(154, '7/3/2012'),
(123, '8/1/2012'),
(197, '8/1/2012'),
(197, '8/2/2012'),
(187, '8/3/2012'),
(132, '8/5/2012');

with rownums as
(
 select
 empid,
 hist_date,
 row_number() over (partition by empid, month(hist_date), year(hist_date) order by hist_date) as rownum
 from
 @table
)
select
 sum(case when month(hist_date)='07' and year(hist_date) = '2012' then 1 else 0 end) as Jul12, 
 sum(case when month(hist_date)='08' and year(hist_date) = '2012' then 1 else 0 end) as Aug12
from
 rownums
where
 rownum = 1;

The first part of the query is a common table expression which gets the row number for each employee ID based on the month and year in question. This is what you can use to filter out "duplicate" rows in the bottom expression: only get the ones where rownum = 1. The end results are what you are expecting, and it results in one scan of the table.

One thing you might think about doing is flipping the query around, though, especially if you're presenting it in a presentation layer like an SSRS matrix or Excel table. The query as you have it now works, but you would need to update each month (or write a somewhat-nasty dynamic SQL statement to display each month). Instead, you could use the natural solution:

with rownums as
(
 select
 empid,
 hist_date,
 month(hist_date) as base_month,
 year(hist_date) as base_year,
 row_number() over (partition by empid, month(hist_date), year(hist_date) order by hist_date) as rownum
 from
 @table
)
select
 base_month,
 base_year,
 count(*) as results
from
 rownums
where
 rownum = 1
group by
 base_month,
 base_year;

Then, put it in an SSRS matrix and you'll automatically get the month and year as columns. Or if it's going out to Excel, you can do the same in a pivot table.

EDIT - Fixed a bug in the partitioning. Now a person can show up in multiple months.

If you don't need the separate dates in columns, you could write the query as:

DECLARE @Emp TABLE
(
 EmpID INT,
 hist_date DATETIME
)

INSERT @Emp(EmpID, hist_date)
VALUES (132, '7/1/2012'),
 (143, '7/1/2012'),
 (154, '7/2/2012'),
 (154, '7/3/2012'),
 (123, '8/1/2012'),
 (197, '8/1/2012'),
 (197, '8/2/2012'),
 (187, '8/3/2012'),
 (123, '9/1/2012'),
 (197, '9/1/2012'),
 (197, '9/2/2012'),
 (187, '9/3/2012');


SELECT *
FROM
(SELECT DISTINCT DATENAME(MONTH, hist_date) + CONVERT(VARCHAR(10), DATEPART(YEAR, hist_date)) AS Dt, EmpID
FROM @Emp) AS Src
PIVOT
(
COUNT(EmpID)
FOR Dt IN ([July2012], [August2012], [September2012])
) AS pvt