title: Query not returning results
body: <p>I have written the following query which returns some results which i originally required:</p>
  
  <pre><code>DECLARE @lastMonthDate VARCHAR(10)
  DECLARE @currentMonthDate VARCHAR(10)
  DECLARE @Year VARCHAR(10)
  
  SET @lastMonthDate = CONVERT(VARCHAR(4), DATEPART(YEAR, DATEADD(MONTH, -1, GETDATE())))+'-'+CONVERT(VARCHAR(2), DATEPART(MONTH, DATEADD(MONTH, -1, GETDATE())))+'-21'
  SET @currentMonthDate = CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE()))+'-'+CONVERT(VARCHAR(2), DATEPART(MONTH, GETDATE()))+'-20'
  SET @Year = DATEPART(YEAR, GETDATE())
  
  SELECT 
       DISTINCT 
       Comp_Name
       , comp_idnr 
       , oppo_date17_1sent
       ,datename(month, oppo_date17_1sent)
       , budg_tempDODate1
       , CONVERT(DECIMAL(10,2), budg_do1_total) AS budg_do1_total
       , budg_paymentMethodM_R
       , Commissions.dbo.Simplicity_Paid.RestructureFeeActualDate
       , CONVERT(DECIMAL(10,2),
                  Commissions.dbo.Simplicity_Paid.RestructureFees) as RestructureFees
       , Commissions.dbo.Simplicity_Paid.RestructureFeeStatus
       , Channel.Chan_Description 
  
   FROM 
       Company
  JOIN Commissions.dbo.Simplicity_Paid 
       on company.Comp_CompanyId = Commissions.dbo.Simplicity_Paid.CompanyID 
  JOIN Opportunity 
       on Company.Comp_CompanyId = Opportunity.Oppo_PrimaryCompanyId
  JOIN Budget 
       on  Opportunity.Oppo_OpportunityId = Budget.budg_OpportunityId
  JOIN Users 
       on company.Comp_PrimaryUserId = User_UserId
  Join Channel 
       on User_PrimaryChannelId = Channel.Chan_ChannelId
  
  WHERE 
       CONVERT(DATE, RestructureFeeActualDate) BETWEEN CONVERT(DATE, @lastMonthDate) 
                                             AND CONVERT(DATE, @currentMonthDate) 
  AND 
       User_FirstName = 'Joshua' 
  AND 
       User_LastName = 'Niss' 
  And 
       RestructureFeeStatus = 'Paid' 
  AND 
       RestructureFees &gt;= 900 
  AND 
       Oppo_Deleted IS NULL 
  </code></pre>
  
  <p>As projects go, requirements have now changed and I needed to add a little more SQL to my code to get my desired results. Problem is... I'm not getting what I need.</p>
  
  <p>I am trying to add the following:</p>
  
  <p><code>AND comp_idnr NOT IN (SELECT ClientID FROM Commissions.dbo.AuditHistory)</code></p>
  
  <p>But for some or other reason, my query is not returning any results anymore. <em>You may be thinking, well that's because the ID is in the query.</em> BUT I can assure you it is <strong>not</strong>. I have manually checked the data and only find 1 record.</p>
  
  <p>I have been trawling the internet for things like "Why would a <em>NOT IN</em> statement not be working", how else to do it etc.</p>
  
  <p><code>My attempts include: Joins (LEFT &amp; RIGHT), Exists and Not exists, IN vs NOT IN</code></p>
  
  <p>I Remember someone once told me, the way you structure your <code>WHERE</code> clause would output different results. So this is my question I guess, <em>"How would I structure my where clause" ? Or do you guys see nothing wrong with that either and I should just carry on trying different things?</em></p>
  
  <p>Sorry if this is a crappy post, I just really have no idea what else to try. Is there any more information I can provide you with which would assist you in helping me?</p>
  
  <p><strong>Just a quick EDIT:</strong> The query does find the 1 record that is <code>IN</code> that subquery.</p>