I have created a query which displays different data when being executed in Microsoft SQL Server Management Studio express than it does when outputting in a browser either using cfdump or cfoutput.
Here is the query:
select count(stat_id) as val, month(status_date) as mnth, year(status_date) as yr
from task_status ts
join appraisal.dbo.employee e on e.userID = ts.user_ID
where e.comp = 1
and e.dept = 2
and e.archive != 1
and ts.status_date between '2016-10-01 00:00:00' AND '2017-10-01 00:00:00'
group by month(status_date), year(status_date)
order by year(status_date), month(status_date)
The expected results, and results seen in Management Studio are:
YR MNTH YR
1 10 2016
1 11 2016
9 2 2017
4 3 2017
3 4 2017
18 5 2017
6 6 2017
1 7 2017
However, results seen from the browser are:
YR MNTH VAL
2016 1 7
2016 2 13
2016 3 5
2016 4 5
2016 5 1
2016 6 4
2016 7 2
2016 10 1
2016 11 1
Any suggestions as to what may be causing this would be most welcome, as I have no idea why there is a difference.
EDIT:
Try changing the dates in your query to
select count(stat_id) as val, month(status_date) as mnth, year(status_date) as yr
from task_status ts
INNER JOIN appraisal.dbo.employee e on e.userID = ts.user_ID
AND e.comp = 1
AND e.dept = 2
AND e.archive != 1
WHERE ts.status_date between '20161001' AND '20171001'
group by year(status_date), month(status_date)
order by year(status_date), month(status_date)
See ISO 8601. You could also change the dates to '2016-10-01T00:00:00' AND '2017-10-01T00:00:00'.
I believe your date may be getting interpreted as a string that is read as YYYY-DD-MM and giving the wrong range when passed to SQL through ColdFusion or the JVM.
=========================================================================
ORIGINAL:
This is more of a personal preference comment:
Change your JOIN syntax to move the conditions out of the WHERE and into the JOIN.
select count(stat_id) as val, month(status_date) as mnth, year(status_date) as yr
from task_status ts
INNER JOIN appraisal.dbo.employee e on e.userID = ts.user_ID
AND e.comp = 1
AND e.dept = 2
AND e.archive != 1
WHERE ts.status_date between '2016-10-01 00:00:00' AND '2017-10-01 00:00:00'
group by year(status_date), month(status_date)
order by year(status_date), month(status_date)
When JOINing tables, it helps to imagine the sets of data that you're working with. When you specify the conditions in the WHERE, you will be creating a big JOIN and then filtering out those results with the WHERE clause. I think newer versions of SQL are a smarter with their optimizer, but I know that 2005 can return different results when conditions are in a LEFT OUTER JOIN vs a WHERE. INNER JOIN won't make a difference, but OUTER can.
I also changed the order in your GROUP BY. It shouldn't change the results, but it's cleaner and more consistent with the grouping of the way the data is likely being used (group by years, then the months of those years).
And a personal preference: rather than just using JOIN, I like adding INNER JOIN, just to make it clearer what specifically I'm doing.