指定计算中迄今的整数键

Question

尝试在整数键到季度的SSAS中执行命名计算。 IntegerDateKey下面：

SalesDateKey 
    20100101 
    20100101 
    20100101 
    20100102 
    20100102 
    20100102 
    20100102 

这不工作，无论我怎么投它，我不能让它开始工作。

CONVERT(char(4), YEAR(SalesDateKey))+ 
    CASE 
     WHEN MONTH(SalesDateKey) BETWEEN 1 AND 3 THEN 'Q1' 
     WHEN MONTH(SalesDateKey) BETWEEN 4 AND 6 THEN 'Q2' 
     WHEN MONTH(SalesDateKey) BETWEEN 7 AND 9 THEN 'Q3' 
     ELSE 'Q4' 
    END 

任何帮助将不胜感激。请在SSAS中的命名计算中测试您的发现。

Answer 1

想通了！

CAST(DATEPART(YEAR, CAST(CAST(SalesDateKey AS char(8)) AS DateTime)) AS 
VARCHAR(4)) + ' - ' + 
CASE 
    WHEN Month(CAST(CAST(SalesDateKey AS CHAR(8)) AS DATE)) BETWEEN 1 AND 3 
    THEN 'Q1' 
    WHEN Month(CAST(CAST(SalesDateKey AS CHAR(8)) AS DATE)) BETWEEN 4 AND 6 
    THEN 'Q2' 
    WHEN Month(CAST(CAST(SalesDateKey AS CHAR(8)) AS DATE)) BETWEEN 7 AND 9 
    THEN 'Q3' 
    ELSE 'Q4' 
END