I was interested in the problem, and I found that the best way to get a complex request is to reformat it using my own style and conventions. I applied them to your decision, and the result is lower. I do not know if this will have any value for you ...
- There were a few bits of code that I believe are not part of the MS T-SQL syntax, for example
({fn xxx } and WEEK(xxx) . - This code compiles, but I cannot run it, since I do not have a properly configured data table.
- I made a lot of changes to the encoding that will require serious explanation, and I will skip most of this. Add a comment if you want to explain something.
- I threw a lot of blanks. The difference between legible and illegible codes is just the perception and sensitivity of the observer, and you can hate my conventions.
- Not sure what the end result should be (i.e. which columns are returned)
Some additional notes:
- This request will not receive items entered for the week if no items were closed this week.
- Weeks may be partial, for example. not all seven days may be present (adjust @Interval to always include full weeks, but what about odd intervals?)
- Multiply count (*) by 1.0 to convert them to earlier floats (avoids reducing the number of lines and integers)
- This was done by cte to allow earlier formulas to be replaced by characters in later formulas (at this point everything became much clearer).
So here is what I came up with:
;WITH cte as ( select c.period ,resolved_half1 ,resolved_half2 ,opened_half1 ,opened_half2 ,row = row_number() over(order by c.yearClosed, c.weekClosed) ,y1 = ((SUM(resolved_half1) + SUM(opened_half1)) - (SUM(resolved_half2) + SUM(opened_half2))) / ((count(resolved_half1) + count(opened_half1)) / 2) ,y2 = ((SUM(resolved_half2) + SUM(opened_half2)) / (count(resolved_half2) + COUNT (opened_half2))) ,x1 = ((count(c.period)) / 4) ,x2 = (((count(c.period)) / 4) * 3) from (select a.yearclosed ,a.weekClosed ,a.resolved_half1 ,b.yearEntered ,b.weekEntered ,b.opened_half1 ,cast(a.yearClosed as varchar(5)) + ', ' + cast(a.weekClosed as varchar(5)) period from (-- Number of items per week that closed within @Interval select count(distinct TicketNbr) * 1.0 resolved_half1 ,datepart(wk, date_closed) weekClosed ,year(date_closed) yearClosed from v_rpt_Service where date_closed >= @FullInterval group by datepart(wk, date_closed) ,year(date_closed) ) a left outer join (-- Number of items per week that were entered within @Interval select count(distinct TicketNbr) * 1.0 opened_half1 ,datepart(wk, date_entered) weekEntered ,year(date_entered) yearEntered from v_rpt_Service where date_entered >= @FullInterval group by datepart(wk, date_entered) ,year(date_entered) ) b on a.weekClosed = b.weekEntered and a.yearClosed = b.yearEntered) c left outer join (select d.yearclosed ,d.weekClosed ,d.resolved_half2 ,e.yearEntered ,e.weekEntered ,e.opened_half2 ,cast(yearClosed as varchar(5)) + ', ' + cast(weekClosed as varchar(5)) period from (select count(distinct TicketNbr) * 1.0 resolved_half2 ,datepart(wk, date_closed) weekClosed ,year(date_closed) yearClosed from v_rpt_Service where date_closed >= @HalfInterval group by datepart(wk, date_closed) ,year(date_closed) ) d left outer join (select count(distinct TicketNbr) * 1.0 opened_half2 ,datepart(wk, date_entered) weekEntered ,year(date_entered) yearEntered from v_rpt_Service where date_entered >= @HalfInterval group by datepart(wk, date_entered) ,year(date_entered) ) e on d.weekClosed = e.weekEntered and d.yearClosed = e.yearEntered ) f on c.period = f.period group by c.period ,resolved_half1 ,resolved_half2 ,opened_half1 ,opened_half2 ,c.yearClosed ,c.weekClosed ) SELECT row ,Period ,x1 ,y1 ,x2 ,y2 ,m = ((y1 - y2) / (x1 - x2)) ,b = (y2 - (((y1 - y2) / (x1 - x2)) * x2)) ,trend = ((((y1 - y2) / (x1 - x2)) * (row)) + (y2 - (((y1 - y2) / (x1 - x2)) * x2))) from cte order by row
As an addition, all the subqueries “c” can be replaced with something like the following, and “f” with a slightly modified version. Better or worse performance depends on table size, indexing and other weightless.
select datepart(wk, date_closed) weekClosed ,year(date_closed) yearClosed ,count (distinct case when date_closed >= @FullInterval then TicketNbr else null end) resolved_half1 ,count (distinct case when date_entered >= @FullInterval then TicketNbr else null end) opened_half1 from v_rpt_Service where date_closed >= @FullInterval or date_entered >= @FullInterval group by datepart(wk, date_closed) ,year(date_closed)