Operand type clash: date is incompatible with tinyint

You have a few issues. First of all, you are incorrectly using your HAVING clause. This clause is used after the GROUP BY clause to filter the aggregate function. Your query does not have the GROUP BY clause, so you will not be able to use the HAVING clause.
Second, to filter by month you need to extract month from the date. In SQL Server you need to use DATEPART function.
As I understand you correctly, you want to retrieve the records for the whole year, but at the same time you want to see which one of those originated in July. For this exercise the best approach is to use a WINDOW Function.

select *,
min(activitydate) OVER(PARTITION BY Fiscal) minActivity
from tblOrgHours 
where Agencyid = 74 and Fiscal = 2015 And 
      ActivityDate >= '07/01/2014' And ActivityDate <= '06/30/2015' 

Select allOpen in new window

The minActivity column will contain the date you are after. However, you will not be able to use it in the WHERE clause straight away. You will have to use a sub-query:

SELECT * FROM (
select *,
min(activitydate) OVER(PARTITION BY Fiscal) minActivity
from tblOrgHours 
where Agencyid = 74 and Fiscal = 2015 And 
      ActivityDate >= '07/01/2014' And ActivityDate <= '06/30/2015') as s
WHERE DATEPART(month, minActivity) = 7

Select allOpen in new window

Alternatively, you can use a CTE:

;with s as (
select *,
min(activitydate) OVER(PARTITION BY Fiscal) minActivity
from tblOrgHours 
where Agencyid = 74 and Fiscal = 2015 And 
      ActivityDate >= '07/01/2014' And ActivityDate <= '06/30/2015')
SELECT * FROM s
WHERE DATEPART(month, minActivity) = 7

Select allOpen in new window

>>"I want all records that begin in the month specified.  In this case, its July"
Don't try to extract the month from dates. This requires running a function on hundreds, or thousands, or hundreds of thousands, or millions of rows, just so you can use 7 in your query.

Instead, alter your way of thinking to suit the way the data is stored. All you need is a simple date range. This way there is no need to run a function on each row (this is called using "sargable predicates").

SELECT
      *
FROM tblOrgHours
WHERE Agencyid = 74
      AND Fiscal = 2015
      AND ActivityDate >= '20140701' -- equal or greater than 1st July 2014
      AND ActivityDate < '20140801' -- less than 1st August 2014, do NOT use equal here

Select allOpen in new window

Please use date ranges the right way:

the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201'
This form works with all types and all precisions, regardless of whether the time part is applicable.

Itzik Ben-Gan

for more on this topic please see: "Beware of Between"

I am not sure I was clear enough on my initial question.  My fault.

Over the fiscal year of 20140701 to 20120630, I am looking for start dates of each person in the table tblOrgHours
If a 15 people started their hours for the year in July then I would like to know how many.  If I change the number to 8-August, then I would like to know how many started for the first time in August during the specified fiscal range that I indicated.

Addtionally, I am simply looking for a count of those who started for the first time in each month.

That is quite a difference!

I think this is what you are seeking - but I cannot test with the tables and data.

declare @st as date, @fin as date

set @st = '20140701'

set @fin = dateadd(month,1,@st) -- with @st 1st July 2014, then @fin is 1st August 2014

SELECT
      COUNT(CASE
            WHEN ActivityDate >= @st 
             AND ActivityDate  < @fin -- less than 1st August 2014, do NOT use equal here
            THEN ActivityDate 
             END) AS count_of
FROM tblOrgHours
WHERE Agencyid = 74
      AND ActivityDate  < @fin -- less than 1st August 2014, do NOT use equal here
HAVING
      COUNT(CASE
            WHEN ActivityDate < @st THEN ActivityDate END) = 0
;

Select allOpen in new window

The best way for this type of need to for you to provide "sample data" and an "expected result", then we do have a way to test.

actually I think that probably isn't going to work, sigh. Try this instead

DECLARE @st AS date
      , @fin AS date

SET @st = '20140701'

SET @fin = DATEADD(MONTH, 1, @st) -- with @st 1st July 2014, then @fin is 1st August 2014

SELECT
      COUNT(*) AS count_of
FROM (
            SELECT
                  employee_id
            FROM tblOrgHours
            WHERE Agencyid = 74
                  AND ActivityDate < @fin -- less than 1st August 2014, do NOT use equal here
            GROUP BY
                  employee_id
            HAVING COUNT(CASE
                  WHEN ActivityDate < @st THEN ActivityDate END) = 0
      ) sq;

Select allOpen in new window

Paul, that is getting very close.  However I return 0 results from this query.  There is definitely data that should be returned.  Is it something in the HAVING clause?

Is the min(ActivityDate) what determines when the employee started? If so, something like the following might work -
select startdate, employee_id, datepart('mm',startdate) startmonth,
count(empidloyee_id) over (partition by datepart('mm',startdate) order by startdate) as monthscount
from
(select employee_id, min(activitydate) startdate
 from tblOrgHours
 where activitydate >= to_date('20140701','yyyymmdd') and activitydate < to_date('20150701','yyyymmdd')
 group by employee_id
 order by employee_id) as x
;

>>"There is definitely data that should be returned. "
it's time for you to prove that statement by providing some data, and the expected result

"sample data" does not have to be huge and it doesn't need anything private
"expected result" should be based on the sample data

This is a time proven method for getting you the results you want, quickly
(ps: had this been done in the question you would probably have awarded points already)

Here is the table.  I hope this comes appears clean enough

HourID	Agency	AgencyID	Program	ActivityID	RegID	ActivityDate	Hours	HourTimeFrom	HourTimeTo	SubGroup	ParticipantHour	VolunteerHour	Fiscal	EntryTime
180	Administrator	74	CSW	3	35	7/3/2014	1	00:00.0	00:00.0		1	0	2015	7/3/2014
181	Administrator	74	CSW	3	9	7/3/2014	1	00:00.0	00:00.0		1	0	2015	7/3/2014
183	Administrator	74	CSW	3	6	7/3/2014	1	00:00.0	00:00.0		1	0	2015	7/3/2014
185	Administrator	74	CSW	3	3	7/3/2014	1	00:00.0	00:00.0		1	0	2015	7/3/2014

Select allOpen in new window

Here is what I would like to achieve:

NumberofthoseStarted
12

The Number of those Started would be in reference to how many began their very first month of activity hours

Here is a better view of tblOrgHours
tblOrgHours.xlsx

I got this error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Administrator' to data type int.


Why would that be happening?

gosh, with the query used for that error message being on your screen only I would have to say I don't know exactly.

somewhere in that query a string is being converted to integer, which won't work.

hint: although you may think it is redundant, you need to provide the query that you are using so we can look for the problem

SELECT
      COUNT(DISTINCT RegID)
FROM (
            SELECT
                  RegID
                , MIN(ActivityDate) OVER (PARTITION BY RegID) AS RegStartMonth
            FROM tblOrgHours
            WHERE fiscal = 2015 AND Agency = 74
      ) sq
WHERE MONTH(RegStartMonth) = 7
;

Select allOpen in new window

Found it

Agency should be AGENCYID

<grin> and I probably wouldn't have got that anyway (that detail is a long way up from here) :: well done

Thank you for your patience

No problem, the important part is you got your solution. Cheers, Paul