Strange SQL Query Issue

something like this?

...WHERE     (ASSIGNEE = 'tristanboughton') AND 
(PROJECT in  ('10180', '10160', '10182', '10100', '10230')) AND 
(issuetype <> '12') AND 
(issuestatus IN (@StatusParameter)) ...

Select all Open in new window

You have to many AND's and OR's without explicit parens to designate the order of operations.  You can also simplify your list of projects.

WHERE  issuetype <> '12'
AND ASSIGNEE = 'tristanboughton'
AND PROJECT IN ( '10180', '10160', '10182', '10100', '10230')
AND issuestatus = @StatusParameter

Finally, I don't know what you are trying to accomplish with @StatusParameter, but I do not think your IN operator works as you think.  The construct I have shown is equivalent to yours (and I suspect, also not what you intended).

You just need some extra brackets in your where clause I think

SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications'
                        WHEN '10160' THEN 'Corporate Reporting' 
                        WHEN '10182' THEN 'CMMI' 
                        WHEN '10100' THEN 'Information Technology Office' 
                        WHEN '10230' THEN 'Domain Controller/Migration' 
           END AS ProjectName,
           REPORTER,
           ASSIGNEE, 
           CASE issuetype WHEN '1' THEN 'Product Defect'
                          WHEN '3' THEN 'Task' 
                          WHEN '4' THEN 'Risk'
                          WHEN '20' THEN 'Report'
                          WHEN '21' THEN 'Change' 
                          WHEN '22' THEN 'Customization' 
                          WHEN '23' THEN 'Updated' 
                          WHEN '24' THEN 'Research' 
                          WHEN '25' THEN 'Applications Issue Type' 
                          WHEN '26' THEN 'Enhancement'
           END AS IssueTypeDef, 
           SUMMARY, 
           DESCRIPTION, 
           CASE PRIORITY WHEN '1' THEN 'Very High' 
                         WHEN '2' THEN 'High' 
                         WHEN '3' THEN 'Moderate' 
                         WHEN '4' THEN 'Low' 
                         WHEN '5' THEN 'Very Low' 
           END AS IssuePriority, 
           PRIORITY, 
           CASE issuestatus WHEN '1' THEN 'Open' 
                            WHEN '3' THEN 'In Progress' 
                            WHEN '4' THEN 'Reopened' 
                            WHEN '5' THEN 'Resolved' 
                            WHEN '6' THEN 'Closed'
           END AS IssueStatusName,
           CREATED, 
           UPDATED, 
           DUEDATE, 
           ID, 
           PROJECT, 
           TIMESPENT, 
           issuetype, 
           issuestatus
FROM         jiraissue
WHERE     ( (PROJECT = '10180') AND (ASSIGNEE = 'tristanboughton') ) OR
          ( (PROJECT = '10160') AND (ASSIGNEE = 'tristanboughton') ) OR
          ( (PROJECT = '10182') AND (ASSIGNEE = 'tristanboughton') ) OR
          ( (PROJECT = '10100') AND (ASSIGNEE = 'tristanboughton') ) OR
          ( (PROJECT = '10230') AND (issuetype <> '12')
            AND (issuestatus IN (@StatusParameter)) AND (ASSIGNEE = 'tristanboughton') 
          )
ORDER BY ProjectName, PRIORITY

Select all Open in new window

Also change issuestatus IN (@StatusParameter) to
 issuestatus = @Statusparameter

Improved where clause - I think this is slightly different to the other suggestions.

SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications'
                        WHEN '10160' THEN 'Corporate Reporting' 
                        WHEN '10182' THEN 'CMMI' 
                        WHEN '10100' THEN 'Information Technology Office' 
                        WHEN '10230' THEN 'Domain Controller/Migration' 
           END AS ProjectName,
           REPORTER,
           ASSIGNEE, 
           CASE issuetype WHEN '1' THEN 'Product Defect'
                          WHEN '3' THEN 'Task' 
                          WHEN '4' THEN 'Risk'
                          WHEN '20' THEN 'Report'
                          WHEN '21' THEN 'Change' 
                          WHEN '22' THEN 'Customization' 
                          WHEN '23' THEN 'Updated' 
                          WHEN '24' THEN 'Research' 
                          WHEN '25' THEN 'Applications Issue Type' 
                          WHEN '26' THEN 'Enhancement'
           END AS IssueTypeDef, 
           SUMMARY, 
           DESCRIPTION, 
           CASE PRIORITY WHEN '1' THEN 'Very High' 
                         WHEN '2' THEN 'High' 
                         WHEN '3' THEN 'Moderate' 
                         WHEN '4' THEN 'Low' 
                         WHEN '5' THEN 'Very Low' 
           END AS IssuePriority, 
           PRIORITY, 
           CASE issuestatus WHEN '1' THEN 'Open' 
                            WHEN '3' THEN 'In Progress' 
                            WHEN '4' THEN 'Reopened' 
                            WHEN '5' THEN 'Resolved' 
                            WHEN '6' THEN 'Closed'
           END AS IssueStatusName,
           CREATED, 
           UPDATED, 
           DUEDATE, 
           ID, 
           PROJECT, 
           TIMESPENT, 
           issuetype, 
           issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = 'tristanboughton')
          AND ( (PROJECT in ('10180', '10160', '10182', '10100') OR
                (PROJECT = '10230') AND (issuetype <> '12')
                AND (issuestatus IN (@StatusParameter)) 
                AND (ASSIGNEE = 'tristanboughton') 
              )
ORDER BY ProjectName, PRIORITY

Select all Open in new window

I am using the query in a SQL Server Reporting Services report, and with the @StatusParamter I am trying to allow the report user to display issues in different statuses (e.g. open, in progress, closed, etc.) or select combinations thereof.

ralmada's modification brings back zero results--null data set, in other words.

dgmg's modification same as above.

TerryAtOpus's shows all as assigned to "tristanboughton" as opposed to filtering those assigned to "tristanboughton," which is one of the original problems I had.

Second post by TerryAtOpus: "Error in IN Clause" error when I try to run it.

In response to ralmada's second post: there are multiple values that could be passed to the @StatusParameter, so doesn't it need the "IN" as opposed to "="?

My last suggestion has a mistake actually, the where clause should be:

WHERE     ASSIGNEE = 'tristanboughton'
          AND ( PROJECT in ('10180', '10160', '10182', '10100') OR
                ( PROJECT = '10230' AND issuetype <> '12'
                  AND issuestatus IN (@StatusParameter) 
                  AND ASSIGNEE = 'tristanboughton'
                )
              )

Select all Open in new window

you will have to declare a variable like this:

declare @strSQL varchar(3000)
 
set @strSQL = 'SELECT     
	CASE PROJECT WHEN ''10180'' THEN ''Corporate Applications'' WHEN ''10160'' THEN ''Corporate Reporting'' WHEN ''10182'' THEN ''CMMI'' WHEN ''10100'' THEN
                       ''Information Technology Office'' WHEN ''10230'' THEN ''Domain Controller/Migration'' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN ''1'' THEN ''Product Defect'' WHEN ''3'' THEN ''Task'' WHEN ''4'' THEN ''Risk'' WHEN ''20'' THEN ''Report'' WHEN ''21'' THEN ''Change'' WHEN
                       ''22'' THEN ''Customization'' WHEN ''23'' THEN ''Updated'' WHEN ''24'' THEN ''Research'' WHEN ''25'' THEN ''Applications Issue Type'' WHEN ''26'' THEN ''Enhancement''
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN ''1'' THEN ''Very High'' WHEN ''2'' THEN ''High'' WHEN ''3'' THEN ''Moderate'' WHEN ''4'' THEN ''Low'' WHEN ''5'' THEN ''Very Low'' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN ''1'' THEN ''Open'' WHEN ''3'' THEN ''In Progress'' WHEN ''4'' THEN ''Reopened'' WHEN ''5'' THEN ''Resolved'' WHEN ''6'' THEN ''Closed''
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = ''tristanboughton'') AND 
(PROJECT in  (''10180'', ''10160'', ''10182'', ''10100'', ''10230'')) AND 
(issuetype <> ''12'') AND 
(issuestatus IN (' + @StatusParameter + ')) 
ORDER BY ProjectName, PRIORITY'
 
exec (@strSQL)

Select all Open in new window

I'm assuming @Statusparameter is passing a string like this:   'open', 'in progress', 'closed'

or numbers like this: 1, 2, 3, 4. In order to use the IN clause you need to use it like this
issuestatus IN (1, 2, 3, 4)
or this way
issuestatus in ('open', 'string1', 'string2)
depending on the type of field

- Declaring variables is not supported in SQL Server 2005 Reporting Services.
- Even with the correction by TerryAtOpus to the WHERE clause, their query still showsall trouble tickets as having been assigned to "tristanboughton" as opposed to filtering those that are actually assigned to "tristanboughton."

Can you please copy and paste the query in as you currently have it?

Here is the current query which shows all tickets as though they have been assigned to "tristanboughton" even if they aren't in our JIRA system.

SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications' WHEN '10160' THEN 'Corporate Reporting' WHEN '10182' THEN 'CMMI' WHEN '10100' THEN
                       'Information Technology Office' WHEN '10230' THEN 'Domain Controller/Migration' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN '1' THEN 'Product Defect' WHEN '3' THEN 'Task' WHEN '4' THEN 'Risk' WHEN '20' THEN 'Report' WHEN '21' THEN 'Change' WHEN
                       '22' THEN 'Customization' WHEN '23' THEN 'Updated' WHEN '24' THEN 'Research' WHEN '25' THEN 'Applications Issue Type' WHEN '26' THEN 'Enhancement'
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN '1' THEN 'Very High' WHEN '2' THEN 'High' WHEN '3' THEN 'Moderate' WHEN '4' THEN 'Low' WHEN '5' THEN 'Very Low' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN '1' THEN 'Open' WHEN '3' THEN 'In Progress' WHEN '4' THEN 'Reopened' WHEN '5' THEN 'Resolved' WHEN '6' THEN 'Closed'
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = 'tristanboughton') AND (PROJECT IN ('10180', '10160', '10182', '10100')) OR
                      (ASSIGNEE = 'tristanboughton') AND (PROJECT = '10230') AND (issuetype <> '12') AND (issuestatus IN (@StatusParameter)) AND 
                      (ASSIGNEE = 'tristanboughton')
ORDER BY ProjectName, PRIORITY

Select all Open in new window

Maybe you can create a stored procedure and execute it like this
exec yourprocedure @StatusParameter = .... whatever...

create procedure yourprocedure
@StatusParameter 
as
declare @strSQL varchar(3000)
 
set @strSQL = 'SELECT     
	CASE PROJECT WHEN ''10180'' THEN ''Corporate Applications'' WHEN ''10160'' THEN ''Corporate Reporting'' WHEN ''10182'' THEN ''CMMI'' WHEN ''10100'' THEN
                       ''Information Technology Office'' WHEN ''10230'' THEN ''Domain Controller/Migration'' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN ''1'' THEN ''Product Defect'' WHEN ''3'' THEN ''Task'' WHEN ''4'' THEN ''Risk'' WHEN ''20'' THEN ''Report'' WHEN ''21'' THEN ''Change'' WHEN
                       ''22'' THEN ''Customization'' WHEN ''23'' THEN ''Updated'' WHEN ''24'' THEN ''Research'' WHEN ''25'' THEN ''Applications Issue Type'' WHEN ''26'' THEN ''Enhancement''
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN ''1'' THEN ''Very High'' WHEN ''2'' THEN ''High'' WHEN ''3'' THEN ''Moderate'' WHEN ''4'' THEN ''Low'' WHEN ''5'' THEN ''Very Low'' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN ''1'' THEN ''Open'' WHEN ''3'' THEN ''In Progress'' WHEN ''4'' THEN ''Reopened'' WHEN ''5'' THEN ''Resolved'' WHEN ''6'' THEN ''Closed''
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = ''tristanboughton'') AND 
(PROJECT in  (''10180'', ''10160'', ''10182'', ''10100'', ''10230'')) AND 
(issuetype <> ''12'') AND 
(issuestatus IN (' + @StatusParameter + ')) 
ORDER BY ProjectName, PRIORITY'
 
exec (@strSQL)

Select all Open in new window

The where clause nesting still isn't right. Try this:

WHERE    (ASSIGNEE = 'tristanboughton')
         AND (  (PROJECT IN ('10180', '10160', '10182', '10100'))
                OR (  (PROJECT = '10230') 
                      AND (issuetype <> '12')
                      AND (issuestatus IN (@StatusParameter))
                   )
             )

Select all Open in new window

(Create the procedure in Management Studio, not reporting services)

Also, can you please review the where clause and check to make sure it's doing what you expect -

My latest suggestion:
* Returns every record with PROJECT in '10180', '10160', '10182', '10100'  for tristanboughton
* Plus also return every record for tristanboughton with PROJECT '10230' that has issuetype <> '12' and issuestatus IN (@StatusParameter)

This where clause should work as expected.

WHERE  issuetype <> '12'
AND ASSIGNEE = 'tristanboughton'
AND PROJECT IN ( '10180', '10160', '10182', '10100', '10230')
--AND issuestatus IN (@StatusParameter)


As for the last line, you cannot  use an IN operator like that.  Let's get the query working without the issuestatus criteria and then deal with that as a separate question.

Because you cannot use the IN clause like that, is that you need to build the query during run-time. That's why you could use a stored procedure as I pointed out. So if your issuestatus column is an integer, @statusparameter should look like this:
exec yourprocedure @StatusParameter = '1, 2, 3, 4, 5'
if your issuestatus column is alphanumeric, it should look like this (please note that I'm using two single quotes for each value and then the whole string enclosed in single quotes again.
exec yourprocedure @StatusParameter = ' ''in progress'', ''open'', ''closed'' '
As a second alternative, but honestly I prefer the stored procedure is passing each status to filter as a separate parameter with a value of 0 or 1 depending if you want to filter it show that status or not. See code below (Second alternative) :
Finally, although it has nothing to do with the query you should consider defining another table and joining it to jiraissue instead of using so many CASE statements.

create procedure yourprocedure
@StatusParameter varchar(1000)
as
declare @strSQL varchar(3000)
 
set @strSQL = 'SELECT     
	CASE PROJECT WHEN ''10180'' THEN ''Corporate Applications'' WHEN ''10160'' THEN ''Corporate Reporting'' WHEN ''10182'' THEN ''CMMI'' WHEN ''10100'' THEN
                       ''Information Technology Office'' WHEN ''10230'' THEN ''Domain Controller/Migration'' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN ''1'' THEN ''Product Defect'' WHEN ''3'' THEN ''Task'' WHEN ''4'' THEN ''Risk'' WHEN ''20'' THEN ''Report'' WHEN ''21'' THEN ''Change'' WHEN
                       ''22'' THEN ''Customization'' WHEN ''23'' THEN ''Updated'' WHEN ''24'' THEN ''Research'' WHEN ''25'' THEN ''Applications Issue Type'' WHEN ''26'' THEN ''Enhancement''
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN ''1'' THEN ''Very High'' WHEN ''2'' THEN ''High'' WHEN ''3'' THEN ''Moderate'' WHEN ''4'' THEN ''Low'' WHEN ''5'' THEN ''Very Low'' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN ''1'' THEN ''Open'' WHEN ''3'' THEN ''In Progress'' WHEN ''4'' THEN ''Reopened'' WHEN ''5'' THEN ''Resolved'' WHEN ''6'' THEN ''Closed''
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = ''tristanboughton'') AND 
(PROJECT in  (''10180'', ''10160'', ''10182'', ''10100'', ''10230'')) AND 
(issuetype <> ''12'') AND 
(issuestatus IN (' + @StatusParameter + ')) 
ORDER BY ProjectName, PRIORITY'
 
exec (@strSQL)
 
--------------------------------------------------------------
Second alternative:
 
SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications' WHEN '10160' THEN 'Corporate Reporting' WHEN '10182' THEN 'CMMI' WHEN '10100' THEN
                       'Information Technology Office' WHEN '10230' THEN 'Domain Controller/Migration' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN '1' THEN 'Product Defect' WHEN '3' THEN 'Task' WHEN '4' THEN 'Risk' WHEN '20' THEN 'Report' WHEN '21' THEN 'Change' WHEN
                       '22' THEN 'Customization' WHEN '23' THEN 'Updated' WHEN '24' THEN 'Research' WHEN '25' THEN 'Applications Issue Type' WHEN '26' THEN 'Enhancement'
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN '1' THEN 'Very High' WHEN '2' THEN 'High' WHEN '3' THEN 'Moderate' WHEN '4' THEN 'Low' WHEN '5' THEN 'Very Low' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN '1' THEN 'Open' WHEN '3' THEN 'In Progress' WHEN '4' THEN 'Reopened' WHEN '5' THEN 'Resolved' WHEN '6' THEN 'Closed'
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = 'tristanboughton') AND 
(PROJECT in  ('10180', '10160', '10182', '10100', '10230')) AND 
(issuetype <> '12') AND 
(
(@StatusParameter_inprogress = 1 and issuestatus = 'In Progress' ) -- 0 means "In progress" not filtered
OR (@StatusParameter_open = 1 and issuestatus = 'Open') -- 0 means "Open" not filtered
OR (@StatusParameter_close = 1 and issuestatus = 'Close') -- 0 means "Close not filtered
)

Select all Open in new window

I would prefer not having to do the stored procedure. It would seem relatively straightforward to pull the trouble tickets in projects '10180', '10160', '10182', '10100', and '10230' where the "Assignee" is equal to "tristanboughton," however, I am still having difficulty.

In TerryAtOpus's and dgmg's latest suggestions, I am still having the problem where it is pulling back ALL of the tickets in the project (regardless of whether or not the assignee is "tristanboughton") and overwriting all assignees with "tristanboughton" as opposed to only displaying those tickets assigned to "tristanboughton."

Any ideas why it is doing this? I have attached my most recent version of the query.

SELECT     CASE PROJECT WHEN '10180' THEN 'Corporate Applications' WHEN '10160' THEN 'Corporate Reporting' WHEN '10182' THEN 'CMMI' WHEN '10100' THEN
                       'Information Technology Office' WHEN '10230' THEN 'Domain Controller/Migration' END AS ProjectName, REPORTER, ASSIGNEE, 
                      CASE issuetype WHEN '1' THEN 'Product Defect' WHEN '3' THEN 'Task' WHEN '4' THEN 'Risk' WHEN '20' THEN 'Report' WHEN '21' THEN 'Change' WHEN
                       '22' THEN 'Customization' WHEN '23' THEN 'Updated' WHEN '24' THEN 'Research' WHEN '25' THEN 'Applications Issue Type' WHEN '26' THEN 'Enhancement'
                       END AS IssueTypeDef, SUMMARY, DESCRIPTION, 
                      CASE PRIORITY WHEN '1' THEN 'Very High' WHEN '2' THEN 'High' WHEN '3' THEN 'Moderate' WHEN '4' THEN 'Low' WHEN '5' THEN 'Very Low' END AS
                       IssuePriority, PRIORITY, 
                      CASE issuestatus WHEN '1' THEN 'Open' WHEN '3' THEN 'In Progress' WHEN '4' THEN 'Reopened' WHEN '5' THEN 'Resolved' WHEN '6' THEN 'Closed'
                       END AS IssueStatusName, CREATED, UPDATED, DUEDATE, ID, PROJECT, TIMESPENT, issuetype, issuestatus
FROM         jiraissue
WHERE     (ASSIGNEE = 'tristanboughton') AND (PROJECT IN ('10180', '10160', '10182', '10100', '10230')) AND (issuetype <> '12')
ORDER BY ProjectName, PRIORITY

Select all Open in new window

Can you try your query in Management Studio first?

Tried the query in management studio--same result as my prior comment.

>>and overwriting all assignees with "tristanboughton" <<
What do you mean by that?
The crteria below will give you ASSIGNEE 'tristanboughton' only. There is no way it could give you tickets with a different value in ASSIGNEE.:
...
WHERE    
(ASSIGNEE = 'tristanboughton') AND
(PROJECT IN ('10180', '10160', '10182', '10100', '10230'))
AND (issuetype <> '12')
....
Can you try something like this first and post the result here? RUN THIS IN MANAGEMENT STUDIO
SELECT ASSIGNEE, count(*)
from jiraissue
WHERE    
(ASSIGNEE = 'tristanboughton')
GROUP BY ASSIGNEE
And finally can you post the definition of table "jiraissue" (column names and column type?
Thanks,

>I am still having the problem where it is pulling back ALL of the tickets in the project (regardless of whether or not the assignee is "tristanboughton") and overwriting all assignees with "tristanboughton" as opposed to only displaying those tickets assigned to "tristanboughton."

Huh?
You request a list of tickets assigned to "tristanboughton".
You get back a list showing "tristanboughton" for all of them.
Then you claim, "tristanboughton" is not really assigned to some of them.

Feels like a Gracie Allen skit.




 

Yeah, it does feel like a Gracie Allen skit, but seriously if (1) I login to our JIRA system and/or (2) just do a Select * from the jiraissue table, those tickets are assigned to other users, which is obviously the crux of my problem right now. Could there be any issues, potentially outside of the query itself, causing this?

In response to ralmada: the result of

SELECT ASSIGNEE, count(*)
from jiraissue
WHERE    
(ASSIGNEE = 'tristanboughton')
GROUP BY ASSIGNEE

is "tristanboughton" and 276.

The definition of the "jiraissue" table is in the attached screen shot.
Table-Definition.png

>>(1) I login to our JIRA system and/or (2) just do a Select * from the jiraissue table, those tickets are assigned to other users, which is obviously the crux of my problem right nowIf <<
 
Then your application is querying a different table than jiraissue. It sounds illogical, but at this point you cannot discard any possibility. Can you check if you have any view defined in your Server?

I have altered your latest query to just give a count of the results - if everything is working correctly, the count from the query should be less than 276, as the criteria is tighter than the above count you ran.

Secondly, can you please copy and paste your latest query that you posted and run it to double check that some id's from the latest query actually belong to a different assignee - this sort of issue is usually just a user misunderstanding (caused by something like running the wrong version of a query by mistake). The "OR" criteria is gone from your latest query, so every record returned should be for tristanboughton.

SELECT count(*)
FROM jiraissue
WHERE (ASSIGNEE = 'tristanboughton') AND (PROJECT IN ('10180', '10160', '10182', '10100', '10230')) AND (issuetype <> '12');

Select all Open in new window

Minor correction: "less than 276" should be "equal to or less than 276"

Just another thought. Could it be that your application is accessing another database?
try using the full schema like this
select * from yourdatabase.dbo.jiraissue

Or different server...
select * from yourserver.yourdatabase.jiraissue

Great idea ralmada - I have seen that happen a few times before!

Nope. Both app and report are accessing the correct database.

And what about the views? can you check the views that you have created in the database?

I checked the views in the database. There are a number of system created views, none of them seem to have to do with trouble tickets/issues.

Again if your application is displaying a different resultset. The only reason has to be because it's querying a different table.
Can you run this query? It will tell you all the tables that contains a column name starting with 'Assign'. It will give you an idea of potential tables you should be looking at.

SELECT COLUMN_NAME, TABLE_NAME, TABLE_CATALOG, TABLE_SCHEMA
FROM yourdatabase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like  'ASSIGN%'  
order by 1, 2
 

I ran the query and the result set is in the attached screen shot. It appears that "jiraissue" is indeed the right table.

I have a suspicion this doesn't have to do with the query. Is there anything else I should be looking into/investigating?
Result-Set.png

What about the date the project was created?

select project, assignee, created
from jiraissue
where PROJECT IN ('10180', '10160', '10182', '10100', '10230')
For instance, per your table definition you could have more than one 10180 project created on different dates...

Nope, just five distinct projects, no projects with the same id but created on different dates.