question

This is actually not too difficult to get the information about the final balance total for each BrojDoK partition. You already have a column named BalanceTotal which calculates the ***cumulative sum*** of Balance for each BrojDoK. The reason that the sum is cumulative is due to the order by clause in the line reading SUM(S1.Balance) OVER (PARTITION BY S1.BrojDok ORDER BY S1.FinID) AS BalanceTotal If there is a need to also include the column storing the final balance total value for each partition and make it available for for all records in each partition then removing the order by will do just that. The BalanceTotal is needed as is, but lets add the column with final balance total per each BrojDoK: sum(S1.Balance) over (partition by S1.BrojDok) FinalBalanceTotal Now that this value is available for each row, it can be used to make a determination about the BalanceDays as needed. I would like to point out that unfortunately, the original calculation of the BalanceDays includes the line of code which does not make any sense whatsoever. I am talking about the part currently reading IIF(S2.BalanceTotal=0,0,0) The only way for the script to branch to this part is if the BalanceTotal > 0 evaluated to false meaning that the BalanceTotal is either zero or it is negative. In this case, there is no reason to wheel out yet another IIF which does not really do anything except trashing the already somewhat complicated logic. In other words, this part should just read ***0***, that is all. Here is the script which includes modifications to the BalanceDays calculation without making changes to the original logic (even though it does include a questionable part): SELECT S2.FinID, S2.Firma,S2.Konto,S2.Partner,S2.BrojDok,S2.DatumVal,S2.pot,S2.dug, S2.Balance,S2.BalanceTotal, case when S2.FinalBalanceTotal = 0 then 0 else -- original calculation, please consider tidying it up IIF(S2.BalanceTotal>0, IIF(S2.BalanceTotal<1,DATEDIFF(day,S2.MinDate,S2.MaxDate), DATEDIFF(day,S2.DatumVal,GETDATE())), -- please note that the line below does not make sense, i.e. the only way the code is branched -- here is when BalanceTotal is zero or negative. Now, there is no reason to add yet another -- IIF if it is not doing anything, it just picks zero regardless of whether BalanceTotal is -- negative or it is equal to zero. The line of code below should just read 0, not IIF (???) IIF(S2.BalanceTotal=0,0,0) ) end AS BalanceDays FROM ( SELECT S1.FinID, S1.Firma,S1.NazFirme,S1.Konto,S1.NazivKonta,S1.Partner, S1.NazivPartnera,S1.BrojDok,S1.DatumVal,S1.pot,S1.dug,S1.Balance, S1.MaxDate,S1.MinDate, SUM(S1.Balance) OVER (PARTITION BY S1.BrojDok ORDER BY S1.FinID) AS BalanceTotal, SUM(S1.Balance) OVER (PARTITION BY S1.BrojDok) AS FinalBalanceTotal FROM ( SELECT t1.FinID, t1.Firma,t1.NazFirme,t1.Konto,t1.NazivKonta,t1.Partner, t1.NazivPartnera, t1.BrojDok,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 Balance,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 = 1102 AND t2.Firma = 1 AND t2.Konto = 2040 AND t2.Partner = 1102 GROUP BY t1.FinID, t1.Firma,t1.NazFirme,t1.Konto,t1.NazivKonta, t1.Partner, t1.NazivPartnera, t1.BrojDok,t1.DatumVal ) AS S1 ) AS S2 ORDER BY BrojDok Hope this helps. Oleg