DECLARE
@startTime datetime,
@endTime datetime,
@campaign int,
@wave int,
@agent varchar(50),
@wrapupdate datetime

SET @startTime = '2011-11-11'
SET @endTime = '2011-11-18'
SET @campaign = 36
set @wave = 0
set @agent = null


select 
CM.WRAPUP_DATE ,
Record_Used =  (SELECT COUNT(DISTINCT(CW.SYS_ID)) FROM CH_HISTORY CH JOIN CH_WRAPUP CW ON CH.ID = CW.CH_ID JOIN CM_OPPORTUNITY ON CM_OPPORTUNITY.ID = CW.SYS_ID WHERE 
((CW.WRAPUP_DATE BETWEEN(convert(datetime, 
cast(year(DATEadd(day,0,CM.WRAPUP_DATE)) as varchar(4)) + '-' +
cast(month(DATEadd(day,0, CM.WRAPUP_DATE))as varchar(2)) + '-' + 
cast(day(DATEadd(day,0, CM.WRAPUP_DATE)) as varchar(2))))
AND
 (convert(datetime, 
cast(year(DATEadd(day,1,CM.WRAPUP_DATE)) as varchar(4)) + '-' +
cast(month(DATEadd(day,1, CM.WRAPUP_DATE))as varchar(2)) + '-' + 
cast(day(DATEadd(day,1, CM.WRAPUP_DATE)) as varchar(2)))))) 
AND 
(@campaign is NULL or @campaign = '0' or @campaign =  CM_OPPORTUNITY.CAMPAIGN_ID) AND  (@wave is NULL or @wave = '0' or @wave = CM_OPPORTUNITY.WAVE_ID) AND (@agent is NULL or @agent = '0' or @agent = CH.USER_ID))
FROM CM_OPPORTUNITY AS CM
WHERE
(@startTime is NULL or CM.WRAPUP_DATE >=  @startTime) 
AND (@endTime is NULL or CM.WRAPUP_DATE  < @endTime) 
AND (@campaign is NULL or @campaign = '0' or @campaign =  CM.CAMPAIGN_ID)
AND (@wave is NULL or @wave = '0' or @wave = CM.WAVE_ID)
AND (@agent is NULL or @agent = '0' or @agent = WRAPUP_USER)
GROUP BY
CM.WRAPUP_DATE
order by CM.WRAPUP_DATE asc

quote:

---

Originally posted by lavenzo


@khtan: yeah it contains time

---