Operand type clash: date is incompatible with tinyint

I get the following error.  Any ideas what it could be?

Msg 206, Level 16, State 2, Line 1
Operand type clash: date is incompatible with tinyint

Code below:
select * from tblOrgHours where Agencyid = 74 and Fiscal = 2015 And ActivityDate >= '07/01/2014' And ActivityDate <= '06/30/2015' having min(activitydate) = 7

Open in new window


Basically, within this data range, I want all records that begin in the month specified.  In this case, its July
al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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' 

Open 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

Open 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

Open in new window

0
PortletPaulfreelancerCommented:
>>"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

Open 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"
0
al4629740Author Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

al4629740Author Commented:
Addtionally, I am simply looking for a count of those who started for the first time in each month.
0
PortletPaulfreelancerCommented:
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
;

Open 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.
0
PortletPaulfreelancerCommented:
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;

Open in new window

0
al4629740Author Commented:
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?
0
awking00Commented:
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
;
0
PortletPaulfreelancerCommented:
>>"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)
0
al4629740Author Commented:
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

Open in new window


Here is what I would like to achieve:

NumberofthoseStarted
12
0
al4629740Author Commented:
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
0
PortletPaulfreelancerCommented:
Try this
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
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
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?
0
PortletPaulfreelancerCommented:
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
0
al4629740Author Commented:
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
;

Open in new window

0
al4629740Author Commented:
Found it

Agency should be AGENCYID
0
PortletPaulfreelancerCommented:
<grin> and I probably wouldn't have got that anyway (that detail is a long way up from here) :: well done
0
al4629740Author Commented:
Thank you for your patience
0
PortletPaulfreelancerCommented:
No problem, the important part is you got your solution. Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.