Mark,

I see two problems with the query though they would not likely cause your counts to be low:

1) you are limiting the comment search to a specific version of the incident in the third query, but not the first two.

2) your comment search in the third query could potentially overlap with the comment search in the first query.

Would it be safe to assume that if an incident was not created by the WinUser form or by email, then it would be a manually created incident?

Brad -

We created a few clean tickets on our DEV site and the below report works BUT when we put on DEV, we are about 400 tickets off for the day.  Looks like we are missing them from Manual. ?huh?  Any advice will be greatly appreciated.

Use AltDEVHELPDESK001_Incidents
--BY WINUSER/FORM        
select
COUNT(distinct W1.number) AS 'Incident count', 'BY SPOC/Forms' AS 'COUNTS'        
from workitem W1        
WHERE W1.[total_minutes_spent] = '0'        
AND W1.comment not like 'Received in mailbox:%'        
and W1.[created_on] >= %STARTDATE%    --'1/11/2011'        
and W1.[created_on] <= %ENDDATE%        --'1/12/2011'        
--AND W1.[created_on] >= '1/11/2011'   --%STARTDATE%       
--AND W1.[created_on] <= '1/12/2011'   --%ENDDATE%       
       
UNION        
       
--BY EMAIL        
select        
COUNT(DISTINCT number) AS 'Incident count', 'BY EMAIL' AS 'COUNT BY EMAIL'        
from workitem W2        
WHERE W2.comment like 'Received in mailbox:%'        
and W2.[created_on] >= %STARTDATE%    --'1/11/2011'       
and W2.[created_on] <= %ENDDATE%        --'1/12/2011'       
--and W2.[created_on] >= '1/11/2011'   --%STARTDATE%       
--and W2.[created_on] <= '1/12/2011'   --%ENDDATE%       
       
UNION        
        
--MANUALLY        
select        
COUNT(DISTINCT W3.number) AS 'Incident count', 'MANUALLY' AS 'COUNTS'        
from workitem W3        
WHERE W3.[created_by_worker_id] = '1348'  --Guest    (MANUAL)       
AND W3.comment NOT LIKE 'Received in mailbox:%'        
and W3.[created_on] >= %STARTDATE%    --'1/11/2011'       
and W3.[created_on] <= %ENDDATE%        --'1/12/2011'       
--and W3.[created_on] >= '1/11/2011'     --%STARTDATE%       
--and W3.[created_on] <= '1/12/2011'     --%ENDDATE%       
      
UNION      
     
select        
--distinct W4.number, W4.title       
COUNT(distinct W4.number) AS 'Incident count',   
'TOTAL' AS 'COUNTS'     
from workitem W4        
WHERE (W4.[created_by_worker_id] = '1348'  --Guest    (MANUAL)       
OR W4.comment NOT LIKE 'Received in mailbox:%'       
OR W4.comment like 'Received in mailbox:%'       
OR W4.comment not like 'Received in mailbox:%')
and W4.[created_on] >= %STARTDATE%    --'1/11/2011'
and W4.[created_on] <= %ENDDATE%        --'1/12/2011' 

Try this for your totals section and see if the numbers line up:

select        
COUNT(distinct W4.number) AS 'Incident count',   
'TOTAL' AS 'COUNTS'     
from workitem W4        
WHERE  W4.[created_on] >= %STARTDATE%    --'1/11/2011'
and W4.[created_on] <= %ENDDATE%        --'1/12/2011'