Joining a table based on date range - Case Statement?

  • Okay - Two tables one transnational table with a standard document date

    Second table is financial fiscal period table that is 'not standard calendar months'.

    CREATE TABLE [dbo].[Sales](

    [SOPTYPE] [smallint] NOT NULL,

    [SOPNUMBE] [char](21) NOT NULL,

    [DOCAMNT] [numeric](19, 5) NOT NULL,

    [DOCDATE] [Date] NOT NULL

    )

    INSERT [Sales]

    Values

    ('6', 'SOP105','0.00','2020-08-30'),

    ('6', 'SOP101','1250.00','2020-01-16'),

    ('6', 'SOP102','200.00','2020-09-10'),

    ('6', 'SOP103','100.00','2020-02-14'),

    ('6', 'SOP104','1805.00','2020-08-31')

    CREATE TABLE [dbo].[FiscalPeriods]

    ([PERIOD] [smallint] NOT NULL,

    [YEAR] [smallint] NOT NULL,

    [FIRSTDATE] [date] NOT NULL,

    [LASTDATE] [date] NOT NULL)

    INSERT FiscalPeriods

    VALUES

    ('1','2020','2020-01-05','2020-02-03'),

    ('2','2020','2020-02-04','2020-03-07'),

    ('3','2020','2020-03-08','2020-04-04'),

    ('4','2020','2020-04-05','2020-05-07'),

    ('5','2020','2020-05-08','2020-06-08'),

    ('6','2020','2020-06-09','2020-07-03'),

    ('7','2020','2020-07-04','2020-08-01'),

    ('8','2020','2020-08-02','2020-08-31'),

    ('9','2020','2020-09-01','2020-09-28'),

    ('10','2020','2020-09-29','2020-10-30'),

    ('11','2020','2020-10-31','2020-11-30'),

    ('12','2020','2020-12-01','2021-01-04')

    Somehow I need a simple select statement that looks at fiscal table and returns the Period and Year for the corresponding date range:

    Select SOPNUMBE, DOCDATE, DOCAMNT, PERIOD (fiscal Table), YEAR(Fiscal Table)

    How do you join a table without a direct relationship? Some kind of joining based upon a case type statement?

    Desired Results:

    SOP105, 2020-08-30, 8,2020

    SOP101, 2020-01-16,1,2020

    SOP102, 2020-09-10,9,2020

    SOP103, 2020-02-14,2,2020

    SOP104, 2020-08-31,8,2020

    Any guidance is appreciated Thanks in advance

  • Try this:

    DROP TABLE IF EXISTS #Sales;
    DROP TABLE IF EXISTS #FiscalPeriods;

    CREATE TABLE #Sales
    (
    SOPTYPE SMALLINT NOT NULL
    ,SOPNUMBE CHAR(21) NOT NULL
    ,DOCAMNT NUMERIC(19, 5) NOT NULL
    ,DOCDATE DATE NOT NULL
    );

    INSERT #Sales
    (
    SOPTYPE
    ,SOPNUMBE
    ,DOCAMNT
    ,DOCDATE
    )
    VALUES
    (6, 'SOP105', 0, '20200830')
    ,(6, 'SOP101', 1250, '20200116')
    ,(6, 'SOP102', 200, '20200910')
    ,(6, 'SOP103', 100, '20200214')
    ,(6, 'SOP104', 1805, '20200831');

    CREATE TABLE #FiscalPeriods
    (
    PERIOD smallint NOT NULL
    ,YEAR SMALLINT NOT NULL
    ,FIRSTDATE DATE NOT NULL
    ,LASTDATE DATE NOT NULL
    );

    INSERT #FiscalPeriods
    (
    PERIOD
    ,YEAR
    ,FIRSTDATE
    ,LASTDATE
    )
    VALUES
    (1, 2020, '20200105', '20200203')
    ,(2, 2020, '20200204', '20200307')
    ,(3, 2020, '20200308', '20200404')
    ,(4, 2020, '20200405', '20200507')
    ,(5, 2020, '20200508', '20200608')
    ,(6, 2020, '20200609', '20200703')
    ,(7, 2020, '20200704', '20200801')
    ,(8, 2020, '20200802', '20200831')
    ,(9, 2020, '20200901', '20200928')
    ,(10, 2020, '20200929', '20201030')
    ,(11, 2020, '20201031', '20201130')
    ,(12, 2020, '20201201', '20210104');

    SELECT s.SOPTYPE
    ,s.SOPNUMBE
    ,s.DOCAMNT
    ,s.DOCDATE
    ,fp.Period
    ,fp.YEAR
    FROM #Sales s
    CROSS JOIN #FiscalPeriods fp
    WHERE s.DOCDATE
    BETWEEN fp.FIRSTDATE AND fp.LASTDATE;

    I do have a few suggestions/guidance items for you:

    1. Put your code in a code block. It makes it much easier to read.
    2. Use temp tables for sample data.
    3. Do not enclose numeric values in quotes
    4. Use the ISO standard YYYYMMDD format for literal dates
    5. Avoid using reserved words as column names (PERIOD)

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thanks for the data setup... this matches your expected output...

    SELECT *
    FROM dbo.Sales s
    INNER JOIN dbo.FiscalPeriods fp
    ON s.DOCDATE>=fp.FirstDate AND s.DocDate<=fp.LastDate;
  • Thank you so much for the prompt reply and for the assistance in properly posting data. It works beautifully.

    I will need to study up on the CROSS JOIN. I had been trying to use a case statement and inner join nested query work with no luck.

  • It is a theta-join!

    SELECT *

    FROM dbo.Sales INNER JOIN dbo.FiscalPeriods

    ON Sales.DOCDATE BETWEEN FiscalPeriods.FIRSTDATE AND FiscalPeriods.LASTDATE;

  • 5. Avoid using reserved words as column names (PERIOD)

    PERIOD is not a reserved word in SQL Server. (Nor is it reserved in ODBC, also not on the list of future reserved words.)

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher wrote:

    5. Avoid using reserved words as column names (PERIOD)

    PERIOD is not a reserved word in SQL Server. (Nor is it reserved in ODBC, also not on the list of future reserved words.)

    Should always double-check these things, shouldn't I 🙂

    The reason I thought it must be was because of Temporal Tables, where PERIOD appears to be a defined term (link).

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply