October 25, 2024
Chicago 12, Melborne City, USA
SQL

Unable to match the results when converting the Excel Trend formula to SQL


We have a requirement to convert Excel Trend logic to SQL using Azure SQL Editor, but the SQL results do not match.
When I used the below logic getting null for Trend, also i have tried the multiple conditions(using slope & intercept,Linear_Regression) but results are not matched.
If anyone has an idea, please respond to this.

Data
[enter image description here]
(https://i.sstatic.net/xtepxhiI.png)

Quantiy_in_KG
76.1,11.4,3.6,1.1

Customer_Achieved_avg_price_in_kg
651.9,652.67,666.94,1050.18

Trend_to_sql
682.91,742.07,749.2,751.48

Example logic1:

WITH RegressionStats AS (
SELECT
CORR(quantity_in_kg, Customer_achieved_price) / VAR(quantity_in_kg) AS slope,
AVG(Customer_achieved_price) - slope \* AVG(quantity_in_kg) AS intercept
FROM
your_table_name
)

— Calculate predicted values

SELECT
quantity_in_kg,
Customer_achieved_price,
slope \* quantity_in_kg + intercept AS predicted_price
FROM
your_table_name
CROSS JOIN RegressionStats;

Example Logic 2nd

Select
quantity_in_kg,
Customer_achieved_price,
(Slope \* quantity_in_kg + INTERCEPT) as Trend
From
(SELECT
quantity_in_kg,
Customer_achieved_price,
REGR_SLOPE(Customer_achieved_price,quantity_in_kg) OVER() AS SLOPE,
REGR_INTERCEPTCustomer_achieved_price,quantity_in_kg) OVER() AS INTERCEPT
From data
)

Expecting assistance from the Stack Community to move forward.



You need to sign in to view this answers

Leave feedback about this

  • Quality
  • Price
  • Service

PROS

+
Add Field

CONS

+
Add Field
Choose Image
Choose Video