title: T-SQL Query returning different number rows when run immediately after each other
body: <p>Afternoon all</p>
  
  <p>Strange one this. I have written a query which is fairly simple and I had assumed it was working ok. However, when I run the query, one after another, multiple times, I get a different number of rows every time. It is a live database, but apart from me, nobody is using it today. The date range I have set in my parameters is way in the past, so no new\deleted records should affect it. So I'm a little confused as to why sometimes I get more rows, sometimes less rows when I should get back the same rows every time. Copy of the code below</p>
  
  <pre><code>DECLARE 
          @From date,
          @To date
  
  SET @From = '01/07/2015'
  SET @To = '31/12/2015'
  
  --AS
  
  
  SELECT DISTINCT
      BR.Branch, 
      DIV.Division,
      BCM.ClientRef@ AS 'Client Reference',
      BPY.PolicyRef@ AS 'Policy Reference',
      AE.Name AS 'Account Executive',
      DIV2.#Name AS 'Account Handler',
      BTX.Dt_raised AS 'Effective Date',
      BTX.Ledger_dt AS 'Ledger Date',
      INS.VTDescription AS 'Insurer',
      BPY.Ptype AS 'Policy Type',
      BTX.Orig_Debt AS 'GWP',
      COALESCE(BTX.Comm_amt, 0) AS 'Commission',
      CASE
          WHEN BTX.Ccode = '7' THEN 0
          ELSE COALESCE(CHG.Orig_Debt, 0)
      END AS 'Fee',
      COALESCE(BTX.Comm_amt, 0) + CASE WHEN BTX.Ccode = '7' THEN 0 ELSE COALESCE(CHG.Orig_Debt, 0) END AS 'Income',
      CASE DATENAME(MONTH, BTX.Dt_Raised)
          WHEN 'July' THEN YEAR(BTX.Dt_Raised)
          WHEN 'August' THEN YEAR(BTX.Dt_Raised)
          WHEN 'September' THEN YEAR(BTX.Dt_Raised)
          WHEN 'October' THEN YEAR(BTX.Dt_Raised)
          WHEN 'November' THEN YEAR(BTX.Dt_Raised)
          WHEN 'December' THEN YEAR(BTX.Dt_Raised)
          WHEN 'January' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
          WHEN 'February' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
          WHEN 'March' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
          WHEN 'April' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
          WHEN 'May' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
          WHEN 'June' THEN YEAR(DATEADD(YEAR, -1, BTX.Dt_Raised))
      END AS 'FinYear',
      CASE DATENAME(MONTH, BTX.Dt_Raised)
          WHEN 'January' THEN 'Ja'
          WHEN 'February' THEN 'Fe'
          WHEN 'March' THEN 'Ma'
          WHEN 'April' THEN 'Ap'
          WHEN 'May' THEN 'My'
          WHEN 'June' THEN 'Ju'
          WHEN 'July' THEN 'Jy'
          WHEN 'August' THEN 'Au'
          WHEN 'September' THEN 'Se'
          WHEN 'October' THEN 'Oc'
          WHEN 'November' THEN 'No'
          WHEN 'December' THEN 'De'
      END AS 'Month Letter',
      DATENAME(MONTH, BTX.Dt_Raised) AS 'MonthName',
      CASE DATENAME(MONTH, BTX.Dt_Raised)
          WHEN 'January' THEN 7
          WHEN 'February' THEN 8
          WHEN 'March' THEN 9
          WHEN 'April' THEN 10
          WHEN 'May' THEN 11
          WHEN 'June' THEN 12
          WHEN 'July' THEN 1
          WHEN 'August' THEN 2
          WHEN 'September' THEN 3
          WHEN 'October' THEN 4
          WHEN 'November' THEN 5
          WHEN 'December' THEN 6
      END AS 'Order',
      CASE ROW_NUMBER() OVER (PARTITION BY BCM.ClientRef@ ORDER BY BCM.ClientRef@)
          WHEN 1 THEN 1 ELSE 0
      END AS 'Client Count', 
      CASE ROW_NUMBER() OVER (PARTITION BY BPY.PolicyRef@ ORDER BY BPY.PolicyRef@)
          WHEN 1 THEN 1 ELSE 0
      END AS 'Policy Count'
  FROM icp_yyclient AS BCM 
  INNER JOIN icp_brpolicy AS BPY ON BCM.ClientRef@ = BPY.ClientRef@ AND BCM.Branch@ = BPY.Branch@
  INNER JOIN icp_brcledger AS BTX ON BPY.PolicyRef@ = BTX.PolicyRef@ AND BPY.Branch@ = BTX.Branch@
  LEFT JOIN icp_brcledger AS CHG ON BTX.Chg_ptr = CHG.Suffix AND BTX.PolicyRef@ = CHG.PolicyRef@ AND BTX.Branch@ = CHG.Branch@
  LEFT JOIN AccountExecutives AS AE ON BCM.Job1 = AE.Code
  LEFT JOIN icp_bredetail AS DIV2 ON BPY.#Exec = DIV2.Code AND BPY.Branch@ = DIV2.Branch@
  LEFT JOIN Division_VT AS DIV ON DIV2.Ext_department = DIV.Division
  LEFT JOIN Branch_VT AS BR ON DIV.Branch = BR.VTId
  LEFT JOIN icp_INSC_VT AS INS ON BPY.Insco_INSC_VTId = INS.VTId
  
  WHERE
      BPY.Branch@ = 0
      AND BTX.Dt_Raised BETWEEN @From AND @To
      AND (BTX.Trantype IN ('New Business') AND BTX.Trantype &lt;&gt; 'Charge')
      AND (BPY.Term_code IS NULL AND BPY.Term_date IS NULL)
  ORDER BY BR.Branch, DIV.Division, BCM.ClientRef@
  </code></pre>