Code:
ALTER PROCEDURE[dbo].[IM_ChartReport]
@app_id int
, @subject_id int
,@issue_id int
,@date_from datetime,
@date_to datetime
,@agent int
AS
BEGIN
if @subject_id=0
set @subject_id=null
if @app_id=0
set @app_id=null
if @issue_id=0
set @issue_id=null
if @agent=0
set @agent=null
select
count(r.subject) as 'total'
,s.subject as 'Value'
,convert(nvarchar(10),[Date_Reported],103) as date_reported
,users.FirstName +' '+ LastName as DisplayName
from
dbo.IM_Msg_Issue_Reported r
left join dbo.IM_Bugs b on b.bug_id=r.issue
left join dbo.IM_Application a on a.App_id=r.application
left join dbo.IM_Subject s on s.Subject_id=r.subject
left outer join users on users.id=r.agent_id
where (r.date_reported BETWEEN CONVERT(varchar(10), @date_from, 103)
AND CONVERT(varchar(10), @date_to, 103))
AND
(R.SUBJECT=@subject_id or @subject_id is null)
and (r.application=@app_id or @app_id is null)
and (r.issue=@issue_id or @issue_id is null)
and (r.agent_id=@agent or @agent is null)
group by
s.subject
,r.date_reported
,users.FirstName
,LastName
union all
select
count (r.application) as 'total'
,a.application as 'Value'
,convert(nvarchar(10),[Date_Reported],103) as date_reported
,users.FirstName +' '+ LastName as DisplayName
from
dbo.IM_Msg_Issue_Reported r
left join dbo.IM_Bugs b on b.bug_id=r.issue
left join dbo.IM_Application a on a.App_id=r.application
left join dbo.IM_Subject s on s.Subject_id=r.subject
left outer join users on users.id=r.agent_id
where (r.date_reported BETWEEN CONVERT(varchar(10), @date_from, 103)
AND CONVERT(varchar(10), @date_to, 103))
AND
(R.SUBJECT=@subject_id or @subject_id is null)
and (r.application=@app_id or @app_id is null)
and (r.issue=@issue_id or @issue_id is null)
and (r.agent_id=@agent or @agent is null)
group by
a.application
,r.date_reported
,users.FirstName
,LastName
union all
select
count(r.issue) as 'total'
,b.bug as 'Value'
,convert(nvarchar(10),r.[Date_Reported],103) as date_reported
,users.FirstName +' '+ LastName as DisplayName
from
dbo.IM_Msg_Issue_Reported r
left join dbo.IM_Bugs b on b.bug_id=r.issue
left join dbo.IM_Application a on a.App_id=r.application
left join dbo.IM_Subject s on s.Subject_id=r.subject
left outer join users on users.id=r.agent_id
where (r.date_reported BETWEEN CONVERT(varchar(10), @date_from, 103)
AND CONVERT(varchar(10), @date_to, 103))
AND
(R.SUBJECT=@subject_id or @subject_id is null)
and (r.application=@app_id or @app_id is null)
and (r.issue=@issue_id or @issue_id is null)
and (r.agent_id=@agent or @agent is null)
group by
b.bug
,r.date_reported
,users.FirstName
,LastName
order by Date_Reported
end
The result of this if the values are null and date_from=’02/02/2009’ and date_to=’03/03/2009’ 
Reply With Quote