user3691566 user3691566 - 1 month ago 15
SQL Question

Sql Trend line by departments

I'm using the example of how to create a sql trend line on a report using the below link.

https://www.mssqltips.com/sqlservertip/3432/add-a-linear-trendline-to-a-graph-in-sql-server-reporting-services/

I've got it all up and running but I want to work out the trend by departments also. However its just merging all the data into one final value, I think its the below section of code that needs altering to calculate the sum by each of the departments I add in, but how best do I do this?

-- calculate sample size and the different sums
SELECT
@sample_size = COUNT(*)
,@sumX = SUM(ID)
,@sumY = SUM([OrderQuantity])
,@sumXX = SUM(ID*ID)
,@sumYY = SUM([OrderQuantity]*[OrderQuantity])
,@sumXY = SUM(ID*[OrderQuantity])
FROM #Temp_Regression;

-- output results
SELECT
SampleSize = @sample_size
,SumRID = @sumX
,SumOrderQty =@sumY
,SumXX = @sumXX
,SumYY = @sumYY
,SumXY = @sumXY;


These variables are then used to work out the trend line:

-- calculate the slope and intercept
SET @slope = CASE WHEN @sample_size = 1
THEN 0 -- avoid divide by zero error
ELSE (@sample_size * @sumXY - @sumX * @sumY) / (@sample_size * @sumXX - POWER(@sumX,2))
END;
SET @intercept = (@sumY - (@slope*@sumX)) / @sample_size;

Answer

You need to add departments column in SELECT & GROUP BY

SELECT departments,
       SampleSize = Count(*),
       SumRID = Sum(ID),
       SumOrderQty = Sum([OrderQuantity]),
       SumXX = Sum(ID * ID),
       SumYY = Sum([OrderQuantity] * [OrderQuantity]),
       SumXY = Sum(ID * [OrderQuantity])
FROM   #Temp_Regression
GROUP  BY departments 

Here is the easier way to calculate slope & intercept for all departments

;WITH cte
     AS (SELECT departments,
                sample_size = Count(*),
                sumX = Sum(ID),
                sumY = Sum([OrderQuantity]),
                sumXX = Sum(ID * ID),
                sumYY = Sum([OrderQuantity] * [OrderQuantity]),
                sumXY = Sum(ID * [OrderQuantity])
         FROM   #Temp_Regression
         GROUP  BY departments),
     slope
     AS (SELECT departments,
                Sample_Size,
                sumX,
                sumY,
                slope = CASE
                          WHEN sample_size = 1 THEN 0 -- avoid divide by zero error
                          ELSE ( sample_size * sumXY - sumX * sumY ) / ( sample_size * sumXX - Power(sumX, 2) )
                        END
         FROM   cte)
SELECT departments,
       slope,
       intercept = ( sumY - ( slope * sumX ) ) / sample_size
FROM   slope