Creating a trend line from data set SQL

linear-regression, math, sql, visual-studio

Solution

I figured it out. I divided the data into multiple derived tables and sub queries, essentially dividing the data in half. These are my formulas to get each value:

*(each row is a week)*
y1 = average of data first half
y2 = average of data second half
x1 = 1/4 of number of weeks
x2 = 3/4 of number of weeks
m = (y1-y2)/(x1-x2)
b = y2 - (m * x2)
trend = (m * row_number) + b 

And here is my (very dirty) SQL code:

SELECT  resolved_half1,resolved_half2,opened_half1,opened_half2, c.period,
((SUM (resolved_half1) OVER () + SUM(opened_half1) OVER ()) - (SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ())) / ((COUNT(resolved_half1) OVER () + COUNT(opened_half1) OVER ()) / 2) as y1, 
((SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ()) / (COUNT(resolved_half2) OVER () + COUNT (opened_half2) OVER ())) as y2,
((COUNT(c.period) OVER ()) / 4) as x1,
(((COUNT(c.period) OVER ()) / 4) * 3) as x2,
((CAST(((SUM (resolved_half1) OVER () + SUM(opened_half1) OVER ()) - (SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ())) / ((COUNT(resolved_half1) OVER () + COUNT(opened_half1) OVER ()) / 2) as float) - CAST(((SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ()) / (COUNT(resolved_half2) OVER () + COUNT (opened_half2) OVER ())) as float)) / (CAST(((COUNT(c.period) OVER ()) / 4) as float) - CAST( (((COUNT(c.period) OVER ()) / 4) * 3) as float))) as m,
(CAST(((SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ()) / (COUNT(resolved_half2) OVER () + COUNT (opened_half2) OVER ())) as float) - (((CAST(((SUM (resolved_half1) OVER () + SUM(opened_half1) OVER ()) - (SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ())) / ((COUNT(resolved_half1) OVER () + COUNT(opened_half1) OVER ()) / 2) as float) - CAST(((SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ()) / (COUNT(resolved_half2) OVER () + COUNT (opened_half2) OVER ())) as float)) / (CAST(((COUNT(c.period) OVER ()) / 4) as float) - CAST( (((COUNT(c.period) OVER ()) / 4) * 3) as float))) * (((COUNT(c.period) OVER ()) / 4) * 3))) as b,
((((CAST(((SUM (resolved_half1) OVER () + SUM(opened_half1) OVER ()) - (SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ())) / ((COUNT(resolved_half1) OVER () + COUNT(opened_half1) OVER ()) / 2) as float) - CAST(((SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ()) / (COUNT(resolved_half2) OVER () + COUNT (opened_half2) OVER ())) as float)) / (CAST(((COUNT(c.period) OVER ()) / 4) as float) - CAST( (((COUNT(c.period) OVER ()) / 4) * 3) as float))) * (ROW_NUMBER() OVER(ORDER BY c.yearClosed,c.weekClosed))) + (CAST(((SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ()) / (COUNT(resolved_half2) OVER () + COUNT (opened_half2) OVER ())) as float) - (((CAST(((SUM (resolved_half1) OVER () + SUM(opened_half1) OVER ()) - (SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ())) / ((COUNT(resolved_half1) OVER () + COUNT(opened_half1) OVER ()) / 2) as float) - CAST(((SUM(resolved_half2) OVER () + SUM(opened_half2) OVER ()) / (COUNT(resolved_half2) OVER () + COUNT (opened_half2) OVER ())) as float)) / (CAST(((COUNT(c.period) OVER ()) / 4) as float) - CAST( (((COUNT(c.period) OVER ()) / 4) * 3) as float))) * (((COUNT(c.period) OVER ()) / 4) * 3)))) as trend,
ROW_NUMBER() OVER(ORDER BY c.yearClosed,c.weekClosed) as row

FROM
    (SELECT *, CAST(yearClosed as varchar(5)) + ', ' + CAST(weekClosed as varchar(5)) AS period
     FROM  (SELECT        TOP (100) PERCENT COUNT(DISTINCT TicketNbr) AS resolved_half1, { fn WEEK(date_closed) } AS weekClosed, { fn YEAR(date_closed) } AS yearClosed
                          FROM            v_rpt_Service
      WHERE (date_closed >= DateAdd(Day, DateDiff(Day, 0, GetDate()) - (180), 0))

      GROUP BY { fn WEEK(date_closed) }, { fn YEAR(date_closed) }) AS a 
      LEFT OUTER JOIN
      (SELECT TOP (100) PERCENT COUNT(DISTINCT TicketNbr) AS opened_half1, { fn WEEK(date_entered) } AS weekEntered, { fn YEAR(date_entered) 
       FROM v_rpt_Service AS v_rpt_Service_1
       WHERE (date_entered > = DateAdd(Day, DateDiff(Day, 0, GetDate()) - (180), 0))
       GROUP BY { fn WEEK(date_entered) }, { fn YEAR(date_entered) }) AS b ON a.weekClosed = b.weekEntered AND a.yearClosed = b.yearEntered) as c 
       LEFT OUTER JOIN
       (SELECT *, CAST(yearClosed as varchar(5)) + ', ' + CAST(weekClosed as varchar(5)) AS period 
       FROM  (SELECT TOP (100) PERCENT COUNT(DISTINCT TicketNbr) AS resolved_half2, { fn WEEK(date_closed) } AS weekClosed, { fn YEAR(date_closed) } AS yearClosed
       FROM v_rpt_Service
       WHERE (date_closed >= DateAdd(Day, DateDiff(Day, 0, GetDate()) - (180 / 2), 0))
       GROUP BY { fn WEEK(date_closed) }, { fn YEAR(date_closed) }) AS d 
       LEFT OUTER JOIN
       (SELECT TOP (100) PERCENT COUNT(DISTINCT TicketNbr) AS opened_half2, { fn WEEK(date_entered) } AS weekEntered, { fn YEAR(date_entered)} AS yearEntered
       FROM v_rpt_Service AS v_rpt_Service_1
       WHERE (date_entered > = DateAdd(Day, DateDiff(Day, 0, GetDate()) - (180 / 2), 0))
       GROUP BY { fn WEEK(date_entered) }, { fn YEAR(date_entered) }) AS e ON d.weekClosed = e.weekEntered AND d.yearClosed = e.yearEntered
) as f ON c.yearClosed = f.yearClosed AND c.weekClosed = f.weekClosed AND c.weekEntered = f.weekEntered AND c.yearEntered = f.yearEntered AND c.period = f.period
GROUP BY c.period, resolved_half1,resolved_half2,opened_half1,opened_half2,c.yearClosed,c.weekClosed
ORDER BY row

This code uses a hard coded value of 180 days. I still need to be able to use a varibale to select the number of days (without getting a divide by 0 error), and the code really needs to be cleaned up. If someone can do those two things for me (I'm not the best at SQL), the bounty is theirs.

Image:

Problem

Original source