question

tricman10 avatar image
tricman10 asked

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

I have a query that uses inner join on the same table and calculates some balance.I am using partition over for this. And depending on the balance value,it is subtracting some dates. My query works fine, but I have a problem. If I have balance days at the join to be zero at the end, I need all columns with the same BrojDoK(column that the inner join is based on) to be zero. This is the result what I have so far: ![alt text][1] What I need is that in this case,for BrojDok 648,since the BalanceTotal at the end is 0, I need the BalanceDays for FinID 5856 also to be zero. I would like to get something like this: ![alt text][2] Can I somehow calculate another column that would do this? I was looking the update method, but I couldn't manage to do anything with it. This is my query so far: SELECT S2.FinID, S2.Firma,S2.Konto,S2.Partner,S2.BrojDok,S2.DatumVal,S2.pot,S2.dug, S2.Balance,S2.BalanceTotal,IIF(S2.BalanceTotal>0, IIF(S2.BalanceTotal<1,DATEDIFF(day,S2.MinDate,S2.MaxDate), DATEDIFF(day,S2.DatumVal,GETDATE())),IIF(S2.BalanceTotal=0,0,0)) 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 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 [1]: /storage/temp/4416-balance1.png [2]: /storage/temp/4417-balance2.png
sql serverjoinsubqueryinner join
balance1.png (6.3 KiB)
balance2.png (6.4 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you for you reply! I used something similiar. I used this: MIN(S3.BalanceDays) OVER (PARTITION BY S3.BrojDok) AS BalanceDays1 but this works also! And you are right about the not need iif with the zero! Thank you so much!
0 Likes 0 ·
@tricman10 Sure, using the minimum of balance days works as well, but it requires yet another sub-select and you already had the ***select*** from ***select*** from ***select*** in the original query. Heavy nesting of selects is always doable but should be avoided for performance reasons (in most cases). Thank you for accepting my answer.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.