Adding a clause into query
Posted on 2007-07-25
I was hoping somebody would be able to assist me in adding a clause into this query. Within table 'UserList', I have added a column called 'User_Deleted' as a bit field
I want to omit all x.Username from this query where User_Deleted=True
What's the most efficiant was to do it in the following query without a major rewrite?
SELECT x.Username, People.Person_nameFirst + ' ' + People.Person_nameLast AS Name, x.Person_ID, " 31 AS Company_ID, x.NoCompleted AS OutputFigure, x.NoAllocated, x.Previous
FROM (SELECT TOP (10) cb.Username, (SELECT COUNT(*) AS PreviousNumberCalls FROM (SELECT cbr.CallBankResponse_ID FROM CallBankResponse AS cbr INNER JOIN CallBank ON cbr.Call_ID = CallBank.Call_ID " & PreviousPeriod & " WHERE (CallBank.Username = cb.Username)) AS derivedtbl_1) AS Previous, SUM(CASE WHEN cbr.call_id IS NULL THEN 0 ELSE 1 END) AS NoCompleted, COUNT(*) AS NoAllocated, UserList_1.Person_ID FROM CallBank AS cb INNER JOIN UserList AS UserList_1 ON cb.Username = UserList_1.User_Username INNER JOIN ActivityOutlets AS ActivityOutlets_1 ON cb.ActivityOutlet_ID = ActivityOutlets_1.ActivityOutlet_ID INNER JOIN OutletList AS OutletList_1 ON ActivityOutlets_1.Outlet_ID = OutletList_1.Outlet_ID INNER JOIN AccountOutlets AS AccountOutlets_1 ON OutletList_1.Outlet_ID = AccountOutlets_1.Outlet_ID INNER JOIN ClientAccounts AS ClientAccounts_1 ON AccountOutlets_1.Account_ID = ClientAccounts_1.Account_ID LEFT OUTER JOIN CallBankResponse AS cbr ON cbr.Call_ID = cb.Call_ID " & Period & " WHERE(ClientAccounts_1.Company_ID = 31) GROUP BY cb.Username, UserList_1.Person_ID ORDER BY NoAllocated DESC) AS x INNER JOIN People ON x.Person_ID = People.Person_ID ORDER BY OutputFigure DESC