Inner join on the same table and based on the sum column subtract the dates

Question

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 Saldo that is based on a SUM of two columns (dug and pot).

Here is an example of that query:

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

The result is:

I need to join the table on BrojDok.

For an example, if BrojDok is 1728, column Saldo Should be 0. The same is with 1379.

After that I need a column that represents number of days based on this: if Saldo is less then 0,it should be 0,

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,

Or if Saldo is greater than 1,Saldo should be the difference between todays date and the date in the table.

I have tried with CTE and subqueries,but my SUM columns get all messed up.This is what I have in CTE:

; 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

and this is the result that I get:

I really don't know where to go from here..Can someone help?

this is something what I would want to get:

also if saldo is <0, then I would subtract the dates with the same BrojDok in the table

Answer 1

After some time I have managed to find to answer.It was actually quite simple,I just made it complicated. When you do the inner join correctly,and use the right columns for the max and min dates,you get what you need.

This is my query that works:

SELECT S2.FinID,S2.Firma,S2.Konto,S2.Partner,S2.NazFirme,S2.NazivKonta,S2.BrojDok,
S2.Valuta,S2.DatumVal,S2.dug,S2.pot,S2.Saldo,S2.MaxDate,S2.MinDate,S2.Saldo,S2.SaldoTotal,
IIF(S2.SaldoTotal>1,1,0) AS KonacnoProba,
IIF(S2.SaldoTotal>1,1,IIF(S2.SaldoTotal>0,DATEDIFF(day,S2.MinDate,S2.MaxDate),
DATEDIFF(day,S2.DatumVal,GETDATE()))) AS KP1,IIF(S2.SaldoTotal<=0,0,
IIF(S2.SaldoTotal<1,DATEDIFF(day,S2.MinDate,S2.MaxDate),
DATEDIFF(day,S2.DatumVal,GETDATE()))) AS Konacno
FROM
   (
     SELECT S1.FinID,S1.Firma,S1.Konto,S1.Partner,S1.NazFirme,S1.NazivKonta,S1.BrojDok,
     S1.Valuta,S1.DatumVal,S1.dug,S1.pot,S1.Saldo,S1.MaxDate,S1.MinDate,
     SUM(S1.Saldo) OVER (PARTITION BY S1.BrojDok ORDER BY S1.FinID) AS SaldoTotal
     FROM
         (
          SELECT t1.FinID, t1.Firma,t1.Konto,t1.Partner,t1.NazFirme,t1.NazivKonta,
          t1.BrojDok,t1.Valuta, t1.DatumVal,Sum(t1.Duguje) AS dug,
          Sum(t1.Potrazuje) AS pot,
          Sum(IIf(t1.[Konto] Like '2%',t1.[Duguje] -t1.[Potrazuje],t1.[Potrazuje]
          -t1.[Duguje])) AS Saldo,
          MAX(t2.DatumVal) as MaxDate,MIN(t2.DatumVal) as MinDate
          FROM tblFinansijskiPodaci t1
          inner join tblFinansijskiPodaci t2 ON t1.BrojDok = t2.BrojDok
          WHERE t1.Firma = 1 
          AND t1.Konto = 2040 AND t1.Partner = 1137 AND t2.Firma = 1 AND t2.Konto = 2040 
          AND t2.Partner = 1137
          GROUP BY t1.FinID, t1.Firma,t1.Konto,t1.Partner,t1.NazivKonta,t1.NazFirme,
          t1.BrojDok,t1.Valuta,t1.DatumVal
         ) AS S1
  ) AS S2