Adding time conditions to query - help!
Posted on 2007-03-23
This query is proving to be a real problem - this is my third question so far! Please bear with me - I am learning from your answers.
I am trying to filter the following query down to all responses received within the last month (i.e. from 1st of the month to the current date).
I tried adding this onto the end of my query but it's skewing up my results set... It's meant to be grouped by username but is messing up when I add this in...
AND cbr.Date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND GETDATE()
Entire query so far...
SELECT TOP (10) Username, Name, Person_ID, Company_ID, NoCompleted, NoAllocated, NoCompleted * 100 / NoAllocated AS OutputFigure
FROM (SELECT TOP (10) cb.Username, SUM(CASE WHEN cbr.call_id IS NULL THEN 0 ELSE 1 END) AS NoCompleted, COUNT(*) AS NoAllocated, People.Person_nameFirst + ' ' + People.Person_nameLast AS Name, People.Person_ID, ClientAccounts.Company_ID
FROM CallBank AS cb
INNER JOIN UserList ON cb.Username = UserList.User_Username
INNER JOIN People ON UserList.Person_ID = People.Person_ID
INNER JOIN ActivityOutlets ON cb.ActivityOutlet_ID = ActivityOutlets.ActivityOutlet_ID
INNER JOIN OutletList ON ActivityOutlets.Outlet_ID = OutletList.Outlet_ID
INNER JOIN AccountOutlets ON OutletList.Outlet_ID = AccountOutlets.Outlet_ID
INNER JOIN ClientAccounts ON AccountOutlets.Account_ID = ClientAccounts.Account_ID
LEFT OUTER JOIN CallBankResponse AS cbr ON cbr.Call_ID = cb.Call_ID
GROUP BY cb.Username, People.Person_nameFirst, People.Person_nameLast, People.Person_ID, ClientAccounts.Company_ID
HAVING(ClientAccounts.Company_ID = 31)
ORDER BY NoAllocated DESC) AS derivedtbl_1
ORDER BY OutputFigure DESC