返回select语句中14个可能列中的日期

Question

我有一个名为F0008 Schema的表。它有14列，我可以在其中找到开始日期(JDE日期格式(，它可以从列名中告诉我财政年度的月份号。

以下是一个将为我返回4的示例，测试日期为120314，解析为2020年11月9日：

DECLARE @TestDate INT
SET @TestDate = 120314
SELECT  CASE
WHEN F0008.CDD01J >= @TestDate THEN 1
WHEN F0008.CDD02J >= @TestDate THEN 2
WHEN F0008.CDD03J >= @TestDate THEN 3
WHEN F0008.CDD04J >= @TestDate THEN 4
WHEN F0008.CDD05J >= @TestDate THEN 5
WHEN F0008.CDD06J >= @TestDate THEN 6
WHEN F0008.CDD07J >= @TestDate THEN 7
WHEN F0008.CDD08J >= @TestDate THEN 8
WHEN F0008.CDD09J >= @TestDate THEN 9
WHEN F0008.CDD10J >= @TestDate THEN 10
WHEN F0008.CDD11J >= @TestDate THEN 11
WHEN F0008.CDD12J >= @TestDate THEN 12
WHEN F0008.CDD13J >= @TestDate THEN 13
WHEN F0008.CDD14J >= @TestDate THEN 14
END AS int_num
FROM [ODS].[PRODDTA].[F0008] F0008
where F0008.CDDTPN = 'B'
and   F0008.CDFY = 20

然后我有一个名为F42119模式的订单表。该表中有一个名为SDIVD的列，它是JDE格式的更新日期。我可以从这个表中得到一系列日期，如下所示：

SELECT  distinct F42119.SDIVD
FROM    [ODS].[PRODDTA].[F42119] F42119
WHERE   F42119.SDIVD BETWEEN 120314 AND 120334
order by 1

我需要做的是将这两个查询的结果组合起来，这样当我从F42119中提取日期列表时，我也可以从F0008中提取case语句的corosconding结果。

简而言之，我需要知道F42119上任何交易的财务月份号。

Answer 1

除了看起来很可怕之外，还有什么可以阻止你把它扔到子查询中吗？

SELECT  distinct F42119.SDIVD, 
(
SELECT  CASE
WHEN F0008.CDD01J >= F42119.SDIVD THEN 1
WHEN F0008.CDD02J >= F42119.SDIVD THEN 2
WHEN F0008.CDD03J >= F42119.SDIVD THEN 3
WHEN F0008.CDD04J >= F42119.SDIVD THEN 4
WHEN F0008.CDD05J >= F42119.SDIVD THEN 5
WHEN F0008.CDD06J >= F42119.SDIVD THEN 6
WHEN F0008.CDD07J >= F42119.SDIVD THEN 7
WHEN F0008.CDD08J >= F42119.SDIVD THEN 8
WHEN F0008.CDD09J >= F42119.SDIVD THEN 9
WHEN F0008.CDD10J >= F42119.SDIVD THEN 10
WHEN F0008.CDD11J >= F42119.SDIVD THEN 11
WHEN F0008.CDD12J >= F42119.SDIVD THEN 12
WHEN F0008.CDD13J >= F42119.SDIVD THEN 13
WHEN F0008.CDD14J >= F42119.SDIVD THEN 14
END AS int_num
FROM [ODS].[PRODDTA].[F0008] F0008
where F0008.CDDTPN = 'B'
and   F0008.CDFY = 20
-- NOTE: you could also hide this in a function to make your life simpler
) as mySubqueryResult
FROM    [ODS].[PRODDTA].[F42119] F42119
WHERE   F42119.SDIVD BETWEEN 120314 AND 120334
order by 1