SQL query task

Interesting query... Some aspects seem to be a little redundant / repetitive, and so, I have to assume that it has been simplified to hide (obfuscate) the real query... And in saying that, I mean no disrespect whatsoever... Maybe some dummy data would help as well.

Also, you haven't mentioned what version of SQL Server you are running. The samples below will work from SQL2008 onwards.

For your needs, you should look at a few new functions to add into your query. They are :
1) datename
2) group by ... with rollup
3) grouping_id
4) avg
5) common table expression ( CTE )

Explanations for AVG and GROUPING_ID can be found at : https://docs.microsoft.com/en-us/sql/t-sql/functions/grouping-id-transact-sql and you can also check/search the other functions there as well

And as an example, I will over simplify your query by way of an example query

But first, let us create and populate a sample table

if object_id('tempdb..#table','U') is not null drop table #table
go

create table #table
(
  PK_Name varchar(10),
  SetupDate datetime,
  CancelDate datetime
)

go

insert into #table values
('Jack',  '20160101','20160103'),
('Jill',  '20160201','20160205'),
('Fred',  '20160301','20160303'),
('Wilma', '20160101','20160103'),
('Barney','20160201','20160207'),
('Betty', '20160301','20160309'),
('Fred',  '20160401','20160402'),
('Wilma', '20160401','20160403'),
('Jack',  '20170101','20170102'),
('Jill',  '20170101','20170103'),
('Barney','20170201','20170207'),
('Betty', '20170301','20170309')

go

Select all Open in new window

Now we have some sample data, we can look at those new functions :

;with mth_cte as
(
  select PK_Name,SetupDate, CancelDate
       , datediff(day,SetupDate,CancelDate) as DaysDiff
       , datename(month,SetupDate) as Month_Name
       , datename(year,SetupDate) as Year_Name
       , datename(quarter,SetupDate) as Quarter_Name
  From #table

) select Case when grouping_id(Year_Name, Quarter_Name, Month_Name) = 1 then 'Qtr '+ Quarter_Name
              when grouping_id(Year_Name, Quarter_Name, Month_Name) = 3 then Year_Name
              when grouping_id(Year_Name, Quarter_Name, Month_Name) = 7 then 'Grand Total'
         else Month_Name end as 'Period'
       , avg(DaysDiff) as AVG_DaysDiff
  From mth_cte
  Group by Year_Name, Quarter_Name, Month_Name with rollup
 -- Having grouping_id(Year_Name, Quarter_Name, Month_Name) < 7  -- to filter out the grand total if needed
  Order by Year_Name, Quarter_Name, Month_Name

Select all Open in new window

See how you go with the above example and then we can discuss in more detail. For example, do you need to accommodate missing periods ? You might need to fabricate months first (or use a calendar table). Also, the AVG function shows as INT because datediff() returns INT and might want to convert to a decimal  basis...