troubleshooting Question

Adding a clause into query

Avatar of nkewney
nkewneyFlag for United Kingdom of Great Britain and Northern Ireland asked on
DatabasesMicrosoft SQL Server 2005
7 Comments1 Solution181 ViewsLast Modified:
Dear Experts,

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  
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros