sql-server - SQL 查询以获取最后 4 个季度和年份的最后 2 位数字

Question

我需要从一个表中获取最后 4 条记录，它包含列值为“19Q1”、“18Q4”的记录。

使用下面的查询我可以获得过去 4 个季度的数据，但是这个查询是否有任何最简单的形式。

SELECT CAST(DATEPART(QUARTER,  CONVERT(DATE, DATEADD(q, DATEDIFF(q,0,DATEADD(q,0,GETDATE())), 0) )) as VARCHAR) + 'Q' + CAST(Right(Cast(Year(DATEADD(q, DATEDIFF(q,0,DATEADD(q,0,GETDATE())), 0) ) As varchar(4)),2) as VARCHAR) as Period
SELECT CAST(DATEPART(QUARTER,  CONVERT(DATE, DATEADD(q, DATEDIFF(q,0,DATEADD(q,-1,GETDATE())), 0) )) as VARCHAR) + 'Q' + CAST(Right(Cast(Year(DATEADD(q, DATEDIFF(q,0,DATEADD(q,-1,GETDATE())), 0) ) As varchar(4)),2) as VARCHAR) as Period
SELECT CAST(DATEPART(QUARTER,  CONVERT(DATE, DATEADD(q, DATEDIFF(q,0,DATEADD(q,-2,GETDATE())), 0) )) as VARCHAR) + 'Q' + CAST(Right(Cast(Year(DATEADD(q, DATEDIFF(q,0,DATEADD(q,-2,GETDATE())), 0) ) As varchar(4)),2) as VARCHAR) as Period
SELECT CAST(DATEPART(QUARTER,  CONVERT(DATE, DATEADD(q, DATEDIFF(q,0,DATEADD(q,-3,GETDATE())), 0) )) as VARCHAR) + 'Q' + CAST(Right(Cast(Year(DATEADD(q, DATEDIFF(q,0,DATEADD(q,-3,GETDATE())), 0) ) As varchar(4)),2) as VARCHAR) as Period

O/P:

1Q19
4Q18
3Q18
2Q18

Answer 1

假设您使用的是 2012(因为您标记了 3 个不同版本的 SQL Server...)怎么样:

SELECT CONCAT(DATEPART(QUARTER,Q.D),'Q',RIGHT(DATEPART(YEAR,Q.D),2))
FROM (VALUES(0),(-1),(-2),(-3)) V(i)
     CROSS APPLY (VALUES(DATEADD(QUARTER,V.i,GETDATE()))) Q(D);