Returning a date from 14 possible columns within a select statement

Question

I have a table called F0008 Schema. It has 14 columns where I could find a start date (in JDE Date format) which could tell me from the column name what month number it is in our financial year.

Here is an example of what will return 4 for me for a Testdate of 120314 which resolves to 9th Nov 2020:

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

I then have an orders table called F42119 Schema. This table has a column in it called SDIVD which is the update date in JDE format. I can get a range of dates from this table like so:

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

What I need to do is combine the results of these two queries so when I pull a list of dates from F42119 I can also pull the corosponding result of the case statement from F0008.

In short I need to know the financial month number for any transaction on F42119.

Answer 1

Is there anything preventing you from throwing it into a subquery, other than it looks terrifying?

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