使用Q119、Q318到Q418的SQL脚本对度量的引用。任何建议的代码改进

Question

问题是手动更新脚本引用、Q119等以更改到下一季度。

我使用脚本使用Q418、Q119作为季度度量的引用，但这意味着每一个新季度我都必须手动将Q119更改为Q219、Q418更改为Q119、Q318更改为Q418，等等。任何关于更好的过程的建议。

我试过循环变量，但没有成功。

SELECT S.REV, S.QUARTER
FROM SALES S
WHERE S.QUARTER IN('Q119','Q418')

我希望看到以下情况：

REV   QUARTER
100   Q119
75    Q418

• 我正在处理的真实数据要复杂得多，但为了这个问题，我想保持它的基础。
• 理想情况下，我希望看到脚本将当前季度作为参数，并在此基础上导出前8个季度，并将它们包含在结果中。

Answer 1

您可以使用递归的CTE生成季度。例如：

with qs as (
      select concat('Q', datename(quarter, getdate()), year(getdate()) % 100) as qy,1 as lev
      union all
      select concat('Q', datename(quarter, dateadd(quarter, - lev, getdate())), year(dateadd(quarter, - lev, getdate())) % 100) as qy, lev + 1
      from qs
      where lev < 8
     )
select s.rev, s.quarter
from sales s
where s.quarter in (select q from qs);

Answer 2

您可以使用数字列表生成一个季度列表：

第一步是建立一个开始日期(这是第一个季度)，为此，我假设了一个年份和季度参数，但实际上，这可以是任何输入：

例如：

DECLARE @Quarter INT = 2,
        @Year INT = 2019

SELECT DATEFROMPARTS(@year, (@Quarter * 3) - 2, 1);

回港

2019-04-01

然后，使用编号列表(从0到7)，您可以从这个开始日期一次减去一个季度，并按您的意愿格式化日期：

DECLARE @Quarter INT = 2,
        @Year INT = 2019

SELECT Code = CONCAT('Q', DATEPART(QUARTER, QuarterStart), FORMAT(QuarterStart, 'yy')),
        QuarterStart
FROM (  SELECT QuarterStart = DATEADD(QUARTER, - n.Number, DATEFROMPARTS(@year, (@Quarter * 3) - 2, 1))
        FROM (VALUES (0),(1),(2),(2),(4),(5),(6),(7)) n (Number)
    ) q;

会回来

Code    QuarterStart
--------------------------
Q219    2019-04-01
Q119    2019-01-01
Q418    2018-10-01
Q418    2018-10-01
Q218    2018-04-01
Q118    2018-01-01
Q417    2017-10-01
Q317    2017-07-01

如果需要将其扩展到8个季度以上，可以向表值构造函数中添加更多行，也可以创建数字表。这里有更多的信息：生成不带循环的集合或序列.第1部分

如果您需要将它合并到您的主查询中，您可以简单地嵌入它：

DECLARE @Quarter INT = 2,
        @Year INT = 2019;

SELECT  S.REV, S.QUARTER
FROM    SALES S
WHERE   EXISTS 
        (   SELECT  1
            FROM (SELECT QuarterStart = DATEADD(QUARTER, - n.Number, DATEFROMPARTS(@year, (@Quarter * 3) - 2, 1))
                    FROM (VALUES (0),(1),(2),(2),(4),(5),(6),(7)) n (Number)) q
            WHERE   s.QUARTER = CONCAT('Q', DATEPART(QUARTER, QuarterStart), FORMAT(QuarterStart, 'yy'))
        )

如果需要在整个查询过程中重用季度，也可以使用表变量：

DECLARE @Quarter INT = 2,
        @Year INT = 2019;

DECLARE @Quarter TABLE (QuarterCode CHAR(4) NOT NULL PRIMARY KEY);
INSERT @Quarter (QuarterCode)
SELECT  CONCAT('Q', DATEPART(QUARTER, QuarterStart), FORMAT(QuarterStart, 'yy'))
FROM (  SELECT QuarterStart = DATEADD(QUARTER, - n.Number, DATEFROMPARTS(@year, (@Quarter * 3) - 2, 1))
        FROM (VALUES (0),(1),(2),(2),(4),(5),(6),(7)) n (Number)
    ) q;

SELECT S.REV, S.QUARTER
FROM SALES S
WHERE S.QUARTER IN (SELECT QuarterCode FROM @Quarter);