• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

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  
0
nkewney
Asked:
nkewney
  • 3
  • 2
  • 2
1 Solution
 
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:
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
 
imran_fastCommented:
@gbshahaq

sorry didn't see your response.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now