title: Inner join on the same table and based on the sum column subtract the dates
body: <p>I am developing a report in C# with SQL Server. I have a table that has some documents numbers (BrojDok), I have dates of that rows and a column <code>Saldo</code> that is based on a SUM of two columns (dug and pot). </p>
  
  <p>Here is an example of that query:</p>
  
  <pre><code>SELECT 
      tblFinansijskiPodaci.Firma, 
      tblFinansijskiPodaci.Konto, tblFinansijskiPodaci.NazivKonta,
      tblFinansijskiPodaci.NazFirme, tblFinansijskiPodaci.BrojDok,
      tblFinansijskiPodaci.DatumVal, tblFinansijskiPodaci.Valuta,
      SUM(tblFinansijskiPodaci.Duguje) AS dug,
      SUM(tblFinansijskiPodaci.Potrazuje)AS pot,
      SUM(IIf([tblFinansijskiPodaci].[Konto] Like '2%',
      [tblFinansijskiPodaci].[Duguje] -[tblFinansijskiPodaci].[Potrazuje],
      [tblFinansijskiPodaci].[Potrazuje] -[tblFinansijskiPodaci].[Duguje])) AS Saldo
  </code></pre>
  
  <p>The result is:</p>
  
  <p><a href="https://i.stack.imgur.com/eyCNb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/eyCNb.png" alt="enter image description here"></a></p>
  
  <p>I need to join the table on BrojDok.</p>
  
  <p>For an example, if BrojDok is 1728, column Saldo Should be 0. The same is with 1379.</p>
  
  <p>After that I need a column that represents number of days based on this: if Saldo is less then 0,it should be 0,</p>
  
  <p>If Saldo is between 0 and 1,Saldo should be the difference between dates in days of that BrojDok - for BrojDok 1728 it should be 2016-05-05 - 2016-05-04,</p>
  
  <p>Or if Saldo is greater than 1,Saldo should be the difference between todays date and the date in the table.</p>
  
  <p>I have tried with CTE and subqueries,but my SUM columns get all messed up.This is what I have in CTE:</p>
  
  <pre><code>; WITH CTE AS 
  (
      SELECT 
          a.Firma, a.Konto, a.NazivKonta, a.BrojDok,
          SUM(a.Duguje) AS dugA,
          SUM(a.Potrazuje) AS potA,
          SUM(IIF(a.[Konto] LIKE '2%', a.[Duguje] - a.[Potrazuje], a.[Potrazuje] - a.[Duguje])) AS SaldoA,
          SUM(b.Duguje) AS dugB,
          SUM(b.Potrazuje)AS potB,
          SUM(IIf(b.[Konto] Like '2%',b.[Potrazuje] - b.[Duguje], b.[Potrazuje] - b.[Duguje])) AS SaldoB
      FROM 
          tblFinansijskiPodaci a 
      JOIN 
          tblFinansijskiPodaci b ON b.BrojDok = a.BrojDok
      WHERE 
          a.Firma = 1 AND a.Konto = 2040 
          AND a.Partner = 1137 AND b.Firma = 1
          AND b.Konto = 2040 AND b.Partner = 1137
      GROUP BY 
          a.Firma, a.NazFirme, a.Konto, a.NazivKonta, a.BrojDok, a.DatumVal, a.Valuta
  )
  SELECT 
      Firma, Konto, NazivKonta,
      BrojDok, potA, dugA, potB, dugB,
      SaldoA, SaldoB, SaldoB - SaldoA AS SaldoTotal 
  FROM 
      CTE 
  ORDER BY 
      Firma
  </code></pre>
  
  <p>and this is the result that I get:</p>
  
  <p><a href="https://i.stack.imgur.com/cWL3L.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cWL3L.png" alt="enter image description here"></a></p>
  
  <p>I really don't know where to go from here..Can someone help?</p>
  
  <p>this is something what I would want to get:</p>
  
  <p><a href="https://i.stack.imgur.com/NJM6z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/NJM6z.png" alt="enter image description here"></a></p>
  
  <p>also if saldo is &lt;0, then I would subtract the dates with the same BrojDok in the table</p>