Help with SQL

Question

Hi Experts,

Need help in creating calculated columns as below.

My Data:

Expected Result of two calculated columns:

Calculated Column 1 = Previous calculated value in Calculated Column 1 * Rate

Calculated Column 2 = RecentMinRent * current Value/Min(Value)

Appreciate your help.

Answer 1

Hi @Shyam Sreeramula

Try this:

SELECT *
	  ,ROUND(RecentMinRent * EXP(SUM(LOG(rate)) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)),0) AS Column1
	  ,ROUND(RecentMinRent * VALUE/MIN(VALUE)OVER(ORDER BY ID),0) AS Column2 
FROM YourTable

Also, you could use recursive CTE to calculate column1, like this:

;WITH CTE AS
(
 SELECT ID, VALUE, RecentMinRent, Rate, rate * RecentMinRent AS Column1 
 FROM YourTable
 WHERE id = 1
 UNION ALL
 SELECT y.ID, y.VALUE, y.RecentMinRent, y.Rate, x.Column1 * y.Rate
 FROM CTE x INNER JOIN YourTable y ON y.id = x.id + 1
)
SELECT ID, VALUE, RecentMinRent, Rate, Column1,ROUND(Column1,0)
FROM CTE
ORDER BY ID
OPTION (MAXRECURSION MAX);

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

So your first calculation column is a running product. Here is an example on you can achieve that:

CREATE TABLE #rates (year   int   NOT NULL PRIMARY KEY,
                     rate   float NOT NULL)
INSERT #rates(year,  rate)
   VALUES(2020, 1.01),
         (2021, 1.02),
         (2022, 1.03),
         (2023, 1.04),
         (2024, 1.05)
SELECT year, rate,
       EXP(SUM(LOG(rate)) OVER (ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
FROM   #rates
ORDER  BY year
SELECT 1.01*1.02*1.03*1.04*1.05
go
DROP TABLE #rates

The SUM with the OVER clause is a regular running sum, There is no product aggregate in SQL Server, but we work around this by using the mathematical formula:

A*B = exp(log(A) + log(B))

I'm afraid that I don't understand your second calculated column.

Also, I like to point that it is better to post data as CREATE TABLE + INSERT statements, as this permits to copy and paste into a query window to work with the data. We can't copy and paste from images.