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

Comment

how to define other column values based on one column on subquery inner join?

I do not know how to write this query.

How to: sum timestamp from same column using another column as a reference

invalid buffer size for BCP column property

How to aggregate among the values of different tables in a single query?

Copyright 2019 Redgate Software.
Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges