Adding a clause into query

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?
Thanks

Nick

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  
LVL 1
nkewneyAsked:
Who is Participating?
 
imran_fastConnect With a Mentor Commented:
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 and UserList_1.User_Deleted=1
                        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  
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
onto this line:
INNER JOIN      UserList AS UserList_1 ON cb.Username = UserList_1.User_Username

add:

AND UserList_1.User_Deleted=0
0
 
imran_fastCommented:
@gbshahaq

sorry didn't see your response.

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
nkewneyAuthor Commented:
Thanks guys.

Could anybody recommend a tool to tidily format sql code?

THanks

NIck
0
 
imran_fastCommented:
paste it in enterprise manager and then execute it will format it.
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
Red Gate do something called "SQL Refactor" - or alternatively use EM as imran suggests.
I once used SQuirreL - a multi-db GUI written in Java (open source too!) and that included some plug-ins for "refactoring" SQL Server code....

personally, i tidy up by hand - makes sure that generally, I type tidily in the first place!!

0
 
nkewneyAuthor Commented:
Spot on Imran, THanks. NIck
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.