question

I have subquery that calculates some sums.And based on the value in column Saldo, I wanna manipulate and subtract some date columns. My problem is that I get duplicate rows. I want to have one row of each FinID,and if the BrojDok is the same,I want to subtract the dates based on a value from Saldo column. My datediff is working. My isare duplicate rows. How can I manage that? I tried with DISTINCT,but it just gives me the first row unique. And if in a inner join I add to join on a b.FinId = P2.Fin my Number of Days will always be zero,since every row will join on itself. FinID is the PK of my table This is my query: SELECT P2.FinID, P2.Firma, P2.BrojDok, P2.DatumVal,P2.Saldo ,P2.SaldoTotal2, IIF(P2.SaldoTotal2<0,0,IIF(P2.SaldoTotal2<1,(DATEDIFF(DAY,P2.DatumVal, b.DatumVal)),0)) AS NumberOfDays FROM ( SELECT P1.FinID, P1.Firma,P1.BrojDok,P1.DatumVal,P1.Saldo,P1.SaldoTotal, IIF(P1.SaldoTotal<0,0,IIF(P1.SaldoTotal>1,1,0)) AS SaldoTotal1, IIF(P1.SaldoTotal<0,0,IIF(P1.SaldoTotal<1,0,1)) AS SaldoTotal2 FROM ( SELECT P.FinID,P.Firma,P.BrojDok,P.DatumVal,P.Saldo , SUM(Saldo) OVER (PARTITION BY BrojDok ORDER BY FinID) AS SaldoTotal FROM ( SELECT a.FinID, a.Firma, a.Konto,a.NazivKonta, a.NazFirme, a.BrojDok, a.DatumVal,a.Valuta, Sum(IIf(a.[Konto] Like '2%',a.[Duguje] -a.[Potrazuje],a.[Potrazuje] -a.[Duguje])) AS Saldo FROM tblFinansijskiPodaci a WHERE a.Firma = 1 AND a.Konto = 2040 AND a.Partner = 1137 GROUP BY a.FinID,a.Firma,a.NazFirme,a.Konto,a.NazivKonta,a.BrojDok, a.DatumVal,a.Valuta,a.Duguje,a.Potrazuje ) AS P GROUP BY FinID,Firma,BrojDok,Saldo,DatumVal ) AS P1 GROUP BY P1.FinID,P1.Firma,P1.BrojDok,P1.DatumVal,P1.Saldo,P1.SaldoTotal ) AS P2 INNER JOIN tblFinansijskiPodaci b ON b.BrojDok=P2.BrojDok GROUP BY P2.FinID,P2.Firma,P2.BrojDok,P2.Saldo,P2.SaldoTotal,P2.SaldoTotal1,P2.SaldoTotal2,P2.DatumVal ORDER BY BrojDok This is my result: [![enter image description here][1]][1] This is what I would like to get for the BrojDok 1379. But this are results without inner join,so my Number of Days are not working [![enter image description here][2]][2] [1]: https://i.stack.imgur.com/Hzlke.png [2]: https://i.stack.imgur.com/YyS1c.png