CODEWITHSUNDEEP
sqlsql-servert-sql
San
San

Reputation: 359

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

Upvotes: 0

Views: 1154

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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;

Upvotes: 1

lptr
lptr

Reputation: 6808

for the "hard-coded" approach, you could define Quarterly as 9-quarterdiff(between purchaseDate&current date)
[or as 9+quarterdiff(between current date&purchaseDate]

declare @sales table (purchaseDate date, total int);

insert into @sales(purchaseDate, total)
values
('20181016', 4), ('20181220', 5), 
('20190219', 6), 
('20190524', 11), ('20190620', 7), 
('20190708', 12),
('20200210', 20),
('20200923', 19), ('20200926', 11),
('20201111', 2) --this is q9
;


--9&quarterdiff (when/if checking !!previous 8 quarters only!!)
select pvt.*
from
(
select total, 
  concat('Q', 9-datediff(quarter, purchaseDate, getdate())) as Quarterly
  --concat('Q', 9+datediff(quarter, getdate(), purchaseDate)) as Quarterly
from @sales
where purchaseDate >= '20181001' 
and purchaseDate <= '20200930' -- or maybe.. < '20201001', even if it's a date datatype
) as s
pivot
(
sum(total) for Quarterly in (Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8)
) as pvt;

Upvotes: 0

Related Questions