Duplicate rows after join the same table in a subquery

,
1

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 problem is duplicate rows. How can I manage that?

I tried with DISTINCT,but it just gives me the first row unique.

FinID is the PK of my table. BrojDok is the field that I am doining my JOIN.

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

This is what I would like to get for the BrojDok 1379.
image link is here : https://i.stack.imgur.com/YyS1c.png
But this are results without inner join,so my Number of Days are not working.

I need to subtract dates based on value from Saldo column.

2

usually duplicate rows after a join means that you don't have enough join columns. First thing I'd check

3

I tried to join on another column,but there is none.Only DokBroj is the same,and is the connection

4

To debug this I would get the IDs for the JOIN into a temporary table:

SELECT DISTINCT P2.BrojDok
INTO #TEMP
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

and then see what the actual JOIN data is, and which rows I get multiple Duplicate Values for the ID on the join:

SELECT *
FROM
(
    SELECT P2.BrojDok
    FROM #TEMP AS P2
    INNER JOIN tblFinansijskiPodaci b ON b.BrojDok=P2.BrojDok
    GROUP BY P2.BrojDok
    HAVING COUNT(*) > 1
) AS P2
INNER JOIN tblFinansijskiPodaci b ON b.BrojDok=P2.BrojDok
1 Like