How to query previous 8 quarters for quarterly data report using sql server?

I want to query the previous 8 quarters from today’s date.

Example: last quarter from today’s date = ‘2020-09-30’ and last 8 quarter from today’s date is ‘2018-10-01’.

I want the last 8 quarter previous (‘2018-10-01’) mark as Q1 in my query result instead of Q4 since it’s the 4th quarter of the year 2018. Q2 would be the next quarter which is from January-March 2019 and so on, so forth.

Is there a way to count it from the starting date to current date?

My current query:

    SELECT
       name, 
       Q1, 
       Q2, 
       Q3, 
       Q4, 
       Q5, 
       Q6, 
       Q7, 
       Q8, 
FROM   (SELECT distinct
               s.custId,
               sum(s.total) as total,
               CAST('Q'+ Cast(DATEPART(QUARTER, s.purchaseDate)AS VARCHAR(1)) AS VARCHAR(2)) AS Quarterly,
               c.name, 
        FROM   sales s 
               LEFT OUTER JOIN customers c 
                            ON c.id = s.custId
        WHERE  AND purchaseDate >= '2018-10-01' 
               AND purchaseDate <= '2020-09-30' 
        GROUP BY 
                s.custId) AS data
       PIVOT ( SUM(total) 
             FOR quarterly IN ([Q1], 
                               [Q2], 
                               [Q3], 
                               [Q4], 
                               [Q5], 
                               [Q6], 
                               [Q7], 
                               [Q8]) )AS pvt

       ORDER by name

Answer

I would just use conditional aggregation:

SELECT s.custId, c.name,
       SUM(CASE WHEN DATEDIFF(quarter, s.purchasedate, GETDATE()) = 8
                THEN s.total
           END) as q1,
       SUM(CASE WHEN DATEDIFF(quarter, s.purchasedate, GETDATE()) = 7
                THEN s.total
           END) as q2,
       . . .
FROM sales s LEFT OUTER JOIN
     customers c 
     ON c.id = s.custId AND
WHERE purchaseDate >= '2018-10-01' AND
      purchaseDate <= '2020-09-30' 
GROUP BY s.custId, c.name;